make 1 table from 2 similar tables

[Guest]

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]

 

[Guest]

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?

 

[Joseph Meehan]

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?


:

You might want to make the year a separate field or not. Making the
year a separate field would make identifying the year easier. You can
combine the two fields in a query form or report to look like a single field
or to provide a primary key field in the table. This would allow you to
restart the first part of the number each year.

BTW the "F#" should not be saved as part of the field but should be
added in the report, form and or query for display only.

With small numbers of records all this does not mean much, but when you
get large files it can make a big difference.

 

[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]