I have created a database to record information about a number of servers. I have created a separate table to record the server information and a separate table to record the peripheral information. I wish to auto number the peripherals 1 to X grouped the server name on the peripheral table. I.e Server 1, - peripheral 1 , peripheral 2 the next peripheral I created for “Server 1” would automatically be numbered “3” however if it was the first peripheral for server Server 2 it would be created as peripheral 1 (for server 2.). Any help or advice would be great as I am new to access.
How To? Autonumber grouped by a field parameter.
- Thread starter msp
- Start date
- Local time
- Today, 11:17
- Joined
- Feb 28, 2001
- Messages
- 30,495
Autonumber doesn't work this way.
If you are numbering your peripherals in a separate table, the peripheral's private number is NEVER an autonumber if it is going to restart.
The way to do this is with a query. Look up DCount and then think about storing a new peripheral record based on this...
tblPrphrl
fldSrvNum - long, FK - server number
fldPrphrlNum - long - peripheral number for this server
etc.
[fldPrphrlNum] = 1 + DCount( [fldPrphrlNum], "tblPrphrl", "[fldSrvNum]=" & {the current server number in question} )
By the way, you should be aware of this, but you might not be. If it is critical to your design that server numbers be contiguously assigned, that should ALSO not be an autonumber. There, you could use DMax of the server number in your server table. Then add 1 to that. You could have also used DMax in the peripheral number.
See, here's the problem. If you assign a field as an autonumber, the moment you open that record, the number is PERMANENTLY ASSIGNED even if you abort the update or append process. (It's the only way you can share a table with an autonumber prime key.) So you would very soon notice gaps in your numbering scheme no matter what.
In general, if your field has any meaning other than a totally arbitrarily assigned number WITH NO OTHER PROPERTIES, it cannot be an autonumber. People who try to encode autonumbers are cruisin' for a bruisin' somewhere down the line.
If you are numbering your peripherals in a separate table, the peripheral's private number is NEVER an autonumber if it is going to restart.
The way to do this is with a query. Look up DCount and then think about storing a new peripheral record based on this...
tblPrphrl
fldSrvNum - long, FK - server number
fldPrphrlNum - long - peripheral number for this server
etc.
[fldPrphrlNum] = 1 + DCount( [fldPrphrlNum], "tblPrphrl", "[fldSrvNum]=" & {the current server number in question} )
By the way, you should be aware of this, but you might not be. If it is critical to your design that server numbers be contiguously assigned, that should ALSO not be an autonumber. There, you could use DMax of the server number in your server table. Then add 1 to that. You could have also used DMax in the peripheral number.
See, here's the problem. If you assign a field as an autonumber, the moment you open that record, the number is PERMANENTLY ASSIGNED even if you abort the update or append process. (It's the only way you can share a table with an autonumber prime key.) So you would very soon notice gaps in your numbering scheme no matter what.
In general, if your field has any meaning other than a totally arbitrarily assigned number WITH NO OTHER PROPERTIES, it cannot be an autonumber. People who try to encode autonumbers are cruisin' for a bruisin' somewhere down the line.
Thanks Doc,
I have not used autonumber for the server name,
I am looking in to your advise and seeing if I can get it to work. I will not be using autonumber, I guess I want Access to automatically number the peripherals as I add them. However I would like to be able to delete and reuse the number.
I have not used autonumber for the server name,
I am looking in to your advise and seeing if I can get it to work. I will not be using autonumber, I guess I want Access to automatically number the peripherals as I add them. However I would like to be able to delete and reuse the number.
Last edited:
B
bionicsmurf
Guest
im trying to do almost the same thing! but either no one knows or no ones telling. i have 17 tables, all of which contain v. diff data , linked by 1 thing . the need to be linked!
lets get basic here .. lets say i add my first 3 records to table6 , the autonumber ID field will label them 1, 2, and 3. but then i add 2 records to table12.. autonumber starts again 1,2.... what i need it to do is number them 4,5. and when i go back to table6 and add another record to have it number it 6...
the number it makes is really inconsiquential as long as it makes a number that hasnt been used before its just a record indentifier!
lets get basic here .. lets say i add my first 3 records to table6 , the autonumber ID field will label them 1, 2, and 3. but then i add 2 records to table12.. autonumber starts again 1,2.... what i need it to do is number them 4,5. and when i go back to table6 and add another record to have it number it 6...
the number it makes is really inconsiquential as long as it makes a number that hasnt been used before its just a record indentifier!
- Local time
- Today, 11:17
- Joined
- Feb 28, 2001
- Messages
- 30,495
msp - the way you want to get where you want to go is to use either DMax or DCount functions. (I vote for DMax myself, but that's one man's opinion.) Identify the highest number in use for a given combined key and add one to the DMax for the next item with the same key. That is going to require some VBA code somewhere, probably underlying a "New Record" button on your data entry form. Using DMax, you reclaim the number when you delete the highest record (or all of the records). You do NOT release a number when you delete other than the latest record.
bionicsmurf, you can't do that task simply. Conceptually, your tables are ill-defined because their primary key has to depend on data outside of each individual table. Your description depicts a technical normalization error. In fact, it doesn't seem that it denormalized, but it is. Because you have a numeric field in a record and that field's value depends on something outside of the table itself, yet it acts as a primary key. I forget which form that violates, but it violates one of the normal forms.
The way to see that this is a violation is to turn that number around and try to use it in a practical way. Let's say, for the sake of argument, that you succeed in building your DB to do what you wanted, with a single number applying to several tables. I challenge you to take a number and tell me where it is defined or what it is.
You can get CLOSE to doing this by having a UNION query that gets the sequence number from all affected tables and then take the DMax of the item number from the UNION query. BE WARNED that even doing it this way you are still violating one of the normalization rules. Because the issue will still be the reverse query of finding the object given the number. Unless you do some serious gyrations, you can't do that.
bionicsmurf, you can't do that task simply. Conceptually, your tables are ill-defined because their primary key has to depend on data outside of each individual table. Your description depicts a technical normalization error. In fact, it doesn't seem that it denormalized, but it is. Because you have a numeric field in a record and that field's value depends on something outside of the table itself, yet it acts as a primary key. I forget which form that violates, but it violates one of the normal forms.
The way to see that this is a violation is to turn that number around and try to use it in a practical way. Let's say, for the sake of argument, that you succeed in building your DB to do what you wanted, with a single number applying to several tables. I challenge you to take a number and tell me where it is defined or what it is.
You can get CLOSE to doing this by having a UNION query that gets the sequence number from all affected tables and then take the DMax of the item number from the UNION query. BE WARNED that even doing it this way you are still violating one of the normalization rules. Because the issue will still be the reverse query of finding the object given the number. Unless you do some serious gyrations, you can't do that.
