drew77
asked on
AutoNumber
Have a old database with 4000 records from last fiscal year, need to start new fiscal year to same database, but want to add a autonumbering field. The autonumbering sequence automatically counts all previous records. I need to start a "0000" new sequence to the new FY without it taking in to affect the previous year... Help!
If I understand you correctly, you are saying you want a field that shows zero or null for records from prior years, but shows an incrementing autonumber starting at the beginning of this year.
There is no way to make an autonumber behave like that on a single table. However, what you can do is create two tables and then use a union query to make them appear like a single table for purposes of reporting, etc.
For testing, I created two tables. One has two string fields, "key" and "value". The other has the same two fields plus an autonumber field called "seq". The following SQL query will return a single result set that gives all data with zero values for "seq" for records that came from the old table:
SELECT key, value, 0 AS seq FROM TABLE1
UNION SELECT key, value, seq FROM TABLE2;
The problem with this approach, of course, is that queries are read-only. However, I assume that your prior year data is closed out and won't be changed at this point, so it might be acceptable to point your data entry screens at the new table and simply ignore last year's data. Reporting is no problem because reports can run off queries.
Hope this helps...
-Graham
There is no way to make an autonumber behave like that on a single table. However, what you can do is create two tables and then use a union query to make them appear like a single table for purposes of reporting, etc.
For testing, I created two tables. One has two string fields, "key" and "value". The other has the same two fields plus an autonumber field called "seq". The following SQL query will return a single result set that gives all data with zero values for "seq" for records that came from the old table:
SELECT key, value, 0 AS seq FROM TABLE1
UNION SELECT key, value, seq FROM TABLE2;
The problem with this approach, of course, is that queries are read-only. However, I assume that your prior year data is closed out and won't be changed at this point, so it might be acceptable to point your data entry screens at the new table and simply ignore last year's data. Reporting is no problem because reports can run off queries.
Hope this helps...
-Graham
If I understand you correctly, you are saying you want a field that shows zero or null for records from prior years, but shows an incrementing autonumber starting at the beginning of this year.
There is no way to make an autonumber behave like that on a single table. However, what you can do is create two tables and then use a union query to make them appear like a single table for purposes of reporting, etc.
For testing, I created two tables. One has two string fields, "key" and "value". The other has the same two fields plus an autonumber field called "seq". The following SQL query will return a single result set that gives all data with zero values for "seq" for records that came from the old table:
SELECT key, value, 0 AS seq FROM TABLE1
UNION SELECT key, value, seq FROM TABLE2;
The problem with this approach, of course, is that queries are read-only. However, I assume that your prior year data is closed out and won't be changed at this point, so it might be acceptable to point your data entry screens at the new table and simply ignore last year's data. Reporting is no problem because reports can run off queries.
Hope this helps...
-Graham
There is no way to make an autonumber behave like that on a single table. However, what you can do is create two tables and then use a union query to make them appear like a single table for purposes of reporting, etc.
For testing, I created two tables. One has two string fields, "key" and "value". The other has the same two fields plus an autonumber field called "seq". The following SQL query will return a single result set that gives all data with zero values for "seq" for records that came from the old table:
SELECT key, value, 0 AS seq FROM TABLE1
UNION SELECT key, value, seq FROM TABLE2;
The problem with this approach, of course, is that queries are read-only. However, I assume that your prior year data is closed out and won't be changed at this point, so it might be acceptable to point your data entry screens at the new table and simply ignore last year's data. Reporting is no problem because reports can run off queries.
Hope this helps...
-Graham
ASKER
Sorry, VMANO... The previously answered question refers only to a start number base. What I need is a autonumbering sequence on the same table. Furthermore, I have received other suggestions to suggest combining two tables and using a union query to act as one. I tried that before, but was just missing it somewhere. I've gotten some suggestions on that same option...I'll be trying that. Thanks again....
Drew...
Drew...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
https://www.experts-exchange.com/topics/comp/lang/vbdatabases/Q.10084353
let me know if this helps
vmano