Should I Have Only 1 Table?

[JD McLeod]

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.

 

[Jeff Boyce]

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]

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.

 

[John Vinson]

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]