• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 491
  • Last Modified:

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!
0
drew77
Asked:
drew77
1 Solution
 
vmanoCommented:
refer to the previously asked questions in EE.
http://experts-exchange.com/topics/comp/lang/vbdatabases/Q.10084353

let me know if this helps
vmano
0
 
ghjmCommented:
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
0
 
ghjmCommented:
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
0
 
drew77Author Commented:
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...
0
 
rholmesCommented:
Set both fields to Long Integer fields. That will lock FY1 fields and discontinue the counting. Set the DefaultValue on FY2 to =DMax+1.Since its a Long Integer field it should start the numbering @ 1, etc.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now