If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Should I Have Only 1 Table?
I am creating a database which will list various internal control processes
for the various business cycles. There will only be about 7-10 cycles, but each cycle can have multiple controls, some as few as 10 while others can have as many as 75 to 100. All of the forms, reports, etc. will be similar in their structure, the only difference being which cycle. At first, I was going to have a separate table for each cycle, but then I thought it would be much simplier to have one table and a way to identify the various cycles. Our current filing system is to use a letter to represent the cycle (B for the treasury cycle) and a number for each control. B1, B2 etc. Here is where I am stuck... I have a form for the user to enter new control processes into the database. The user selects the cycle name and it stores the cycle letter. The next field is the control number which I currently have set to an autonumber. As long as you are entering controls for the same cycle, the autonumber works fine, but once you select a different cycle letter, the autonumber doesn't start over, but rather keeps going. For example, if you enter the three controls for treasury you have B1, B2, B3 and then you enter your first loan control you get E4 rather than E1. If I have a separate table for each cycle this isn't a problem, but I don't know if that makes sense since the data is similar in every other respect except for which cycle it belongs to. Is there a way to have the autonumber start over when a new cycle is detected? Should I change the structure of my database? I want to be sure I get this project started off right so any advice is much appreciated. |
#2
|
|||
|
|||
The Access Autonumber field is designed to provide a unique row identifier,
to aid in establishing relationships between tables. It is not particularly suitable for human consumption. I'll urge you to consider a "custom autonumber" instead (actually, this is a small routine you write yourself, and has nothing in common with the Access Autonumber). You can find examples at mvps.org or by using Google.com. Good luck Jeff Boyce Access MVP "JD McLeod" wrote in message news I am creating a database which will list various internal control processes for the various business cycles. There will only be about 7-10 cycles, but each cycle can have multiple controls, some as few as 10 while others can have as many as 75 to 100. All of the forms, reports, etc. will be similar in their structure, the only difference being which cycle. At first, I was going to have a separate table for each cycle, but then I thought it would be much simplier to have one table and a way to identify the various cycles. Our current filing system is to use a letter to represent the cycle (B for the treasury cycle) and a number for each control. B1, B2 etc. Here is where I am stuck... I have a form for the user to enter new control processes into the database. The user selects the cycle name and it stores the cycle letter. The next field is the control number which I currently have set to an autonumber. As long as you are entering controls for the same cycle, the autonumber works fine, but once you select a different cycle letter, the autonumber doesn't start over, but rather keeps going. For example, if you enter the three controls for treasury you have B1, B2, B3 and then you enter your first loan control you get E4 rather than E1. If I have a separate table for each cycle this isn't a problem, but I don't know if that makes sense since the data is similar in every other respect except for which cycle it belongs to. Is there a way to have the autonumber start over when a new cycle is detected? Should I change the structure of my database? I want to be sure I get this project started off right so any advice is much appreciated. |
#3
|
|||
|
|||
Hey Jeff,
thanks for the direction. Here is what I did. I can use a dmax function on the form to look at the table and give me the next available number in that cycle, but I don't know how to get that new number on the form to save with the current record in the table. Thanks. "Jeff Boyce" wrote: The Access Autonumber field is designed to provide a unique row identifier, to aid in establishing relationships between tables. It is not particularly suitable for human consumption. I'll urge you to consider a "custom autonumber" instead (actually, this is a small routine you write yourself, and has nothing in common with the Access Autonumber). You can find examples at mvps.org or by using Google.com. Good luck Jeff Boyce Access MVP "JD McLeod" wrote in message news I am creating a database which will list various internal control processes for the various business cycles. There will only be about 7-10 cycles, but each cycle can have multiple controls, some as few as 10 while others can have as many as 75 to 100. All of the forms, reports, etc. will be similar in their structure, the only difference being which cycle. At first, I was going to have a separate table for each cycle, but then I thought it would be much simplier to have one table and a way to identify the various cycles. Our current filing system is to use a letter to represent the cycle (B for the treasury cycle) and a number for each control. B1, B2 etc. Here is where I am stuck... I have a form for the user to enter new control processes into the database. The user selects the cycle name and it stores the cycle letter. The next field is the control number which I currently have set to an autonumber. As long as you are entering controls for the same cycle, the autonumber works fine, but once you select a different cycle letter, the autonumber doesn't start over, but rather keeps going. For example, if you enter the three controls for treasury you have B1, B2, B3 and then you enter your first loan control you get E4 rather than E1. If I have a separate table for each cycle this isn't a problem, but I don't know if that makes sense since the data is similar in every other respect except for which cycle it belongs to. Is there a way to have the autonumber start over when a new cycle is detected? Should I change the structure of my database? I want to be sure I get this project started off right so any advice is much appreciated. |
#4
|
|||
|
|||
On Tue, 28 Jun 2005 07:56:07 -0700, JD McLeod
wrote: Hey Jeff, thanks for the direction. Here is what I did. I can use a dmax function on the form to look at the table and give me the next available number in that cycle, but I don't know how to get that new number on the form to save with the current record in the table. Thanks. Use the Form's BeforeInsert event: Private Sub Form_BeforeInsert(Cancel as Integer) Me!sequencenumber = NZ(DMax("[sequencenumber]", "[tablename]", "criteria")) + 1 End Sub John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Get data from combo box to popluate the next box | Lin Light | Using Forms | 4 | December 30th, 2004 05:01 PM |
transpose | john | Using Forms | 1 | November 24th, 2004 06:16 PM |
Automatic filling of fields in table two from table one | Jim Kelly | Database Design | 1 | September 27th, 2004 10:16 PM |
Complicated Databse w/many relationships | Søren | Database Design | 7 | July 13th, 2004 05:41 AM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |