make 1 table from 2 similar tables

[Officer A.J.]

I have 2 almost identical tables I need to make into one table; One table is
records from 2006 and has a field called "File #" which the number is put in
but not automatically generated. The table from 2007 has an autonumber field
for the "File#", which started at 1 for records entered in 2007. I need to
keep auto generating numbers for 2007, but keep the file numbers from the
2006 records. And ultimately have a 2008 table which will auto generate
numbers starting at 1 in 2008. I don't want seperate tables for each year,
as I want to easily search all records fron a single form. I can rename the
fields "2006 File#" and 2007 File#" ect. But when I "merge" the two tables,
how do I make sure the autonumber field doesn't generate autonumbers for the
2006 records? This is for Police Reports.

 

[John Vinson]

I have 2 almost identical tables I need to make into one table; One table is
records from 2006 and has a field called "File #" which the number is put in
but not automatically generated. The table from 2007 has an autonumber field
for the "File#", which started at 1 for records entered in 2007. I need to
keep auto generating numbers for 2007, but keep the file numbers from the
2006 records. And ultimately have a 2008 table which will auto generate
numbers starting at 1 in 2008. I don't want seperate tables for each year,
as I want to easily search all records fron a single form. I can rename the
fields "2006 File#" and 2007 File#" ect. But when I "merge" the two tables,
how do I make sure the autonumber field doesn't generate autonumbers for the
2006 records? This is for Police Reports.

If you use an Append query, and append the File # field (which you
should rename, by the way: the # character is a date delimiter and can
cause trouble in fieldnames) from the old table into the new one, it
will copy whatever number is there. This is very likely to be a
problem though! If the file numbers were in the same range as the 2007
file numbers, you'll have two records with File # 1, two with File # 2
and so on. What do you want the file # to contain?

Note that if you use an Autonumber, it WILL have gaps, and it will
emphatically NOT let you start over at 1 on January 1, 2008. You may
want to reconsider using Autonumber for this purpose!

John W. Vinson[MVP]

 

[Officer A.J.]

John, I viewed your site and saw "General: Use Autonumbers properly" Maybe
I'm not using the autonumber for the right purpose. If I loose the auto
number and can gernerate a meaningful number, the File Number, that I can
distinguish by year, ie "F# 002345-07" something that would look like that
and assign one in sequence?

 

[John Vinson]

John, I viewed your site and saw "General: Use Autonumbers properly" Maybe
I'm not using the autonumber for the right purpose. If I loose the auto
number and can gernerate a meaningful number, the File Number, that I can
distinguish by year, ie "F# 002345-07" something that would look like that
and assign one in sequence?

I'm curious what site you viewed - I don't HAVE a website!

Whatever it was, I fully agree with the suggestion. This isn't an
Autonumber example. I do have code that I use to maintain almost
exactly this kind of file number (07-00001 but there'd be no problem
reversing it). However, it's just an implementation of copyrighted
software from the Access Developer's Handbook so I'm not comfortable
posting it.

There are many, many posts on this group about "Custom Counter"
techniques, or check the resources at my friend Jeff's site or the
Access Web:

Jeff Conrad's resources page:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

John W. Vinson[MVP]