Autonumber question again sorry

G1ZmO · Mar 7, 2008

I have read a lot of posts regarding using autonumber and other incremental numbering but I cant seem to get my head round how to impliment it in my situation.

We have a database at work which currently has about 22000 records across several tables. eg. (Printers, Systems, Components, Misc Equipment) The current database has been built and modified as we required more functions and is a bit messy. These tables all have some similar fields so I thought that merging all the data into one table would make queries easier.

eg. currenlty if we're looking for a serial number we need to do 4 searches in the 4 tables to locate it.
also getting a list of what technican has done on a particular date means searching 4 tables for the test date and tech name.

SO.. I have started rebuiling the database using one main table with all the data and an "Item_Type" field to differentiate between the printers, systems etc

Now,, the database is still in use until I get the new one running and I'd like to keep the ID numbers for the systems at least. I dont mind if there are gaps in the number sequence as long as they are all unique.

I was thinking that if I started the new database at ID 30000 then I can get it ready and import the old records at changeover time.

Problem is that, without scripting knowledge. I cant really figure out how to do this DMAX thing that people on the forum refer to.

Can anyone give me a simple, how-to, given the guidelines above?

Many thanks,

Paul

Dennisk · Mar 7, 2008

The Dmax 'thing' is flawed as it does not handle concurrency correctly. I have posted on a number of occasions a function to get the next number to allocate with the correct concurrency locking.

It is possible in access to get your auto number to start from a specific number.

1) Copy the table structure only.
2) Convert the Autonumber in the copied table to a number then add a record with a number of 29,999.
3) append this record to the original table.
4) delete the appended row.
5) the autonumber should now continue from 30,000

G1ZmO · Mar 7, 2008

Thanks Dennisk
Ok tried that but when I append the record into the original table it sets the autonumber field back to 1 (no other records in the original table yet)

The_Doc_Man · Mar 7, 2008

Instead of doing all of that, consider another way to simplify your life.

Instead of searching four tables, write a UNION query of the four tables that contains the common things you might wish to look up, then query the query.

That gives you breathing room to redesign/reimplement what you've got and still retain (even improve) existing functionality in the interim.

Long-term, you are correct to want to merge the tables and do whatever you need in a single-table environment. But since life has to go on at the same time, look for the UNION query as a stop-gap measure.

SELECT SN, LOCATION, DEPT, "WIDGET" AS DEVTYPE FROM tblWIDGETS UNION
SELECT SN, PLACE AS LOCATION, DEPARTMENT AS DEPT, "THINGY" AS DEVTYPE FROM tblTHINGIES UNION
SELECT SERNUM AS SN, WHEREIS AS LOCATION, DEP AS DEPT, "WHATSIS" AS DEVTYPE FROM tblWHATSISES ....

Then you can query via SELECT SN, LOCATION, DEPT, DEVTYPE FROM qryUNION4 WHERE SN LIKE "*DPSZ*";

Read up on UNION queries to understand how the above simultaneously joins the tables end-to-end and remaps the fields that have variant names AND supplies a constant for a "fake" field to identify the table from where said record originated.

G1ZmO · Mar 8, 2008

Thanks Doc_Man,

I'll have a go at this when I get back to work on Monday and I'll let you know how I get on.

Thanks

Paul

G1ZmO · Mar 20, 2008

OK how about this. Can this be done?

I currently have 4 tables and each has been autonumbered

I'd like to import these into the new database but I'd like to conserve their numbering by adding an a digit to the start of their ID numbers. Their ID numbers do not have to go into an autonumbered field in the new database.
eg A00001,2,3,... B00001,2,3,... C00001,2,3,... etc

Now, assuming that can be done, I'd like to set up a new field in the new database that contains the IDs referred to above and also IDs for the new records of the database but I'd like these to start autonumbering at say 30000

Does any of that make sense?
if not, can anyone suggest a better way of importing my existing tables into a single table whilst conserving the IDs?

ps The IDs do not need to be consecutive but they do need to be unique to the record.

Thanks

jdraw · Mar 21, 2008

G1ZmO said:
I have read a lot of posts regarding using autonumber and other incremental numbering but I cant seem to get my head round how to impliment it in my situation.

We have a database at work which currently has about 22000 records across several tables. eg. (Printers, Systems, Components, Misc Equipment) The current database has been built and modified as we required more functions and is a bit messy. These tables all have some similar fields so I thought that merging all the data into one table would make queries easier.

eg. currenlty if we're looking for a serial number we need to do 4 searches in the 4 tables to locate it.
also getting a list of what technican has done on a particular date means searching 4 tables for the test date and tech name.

SO.. I have started rebuiling the database using one main table with all the data and an "Item_Type" field to differentiate between the printers, systems etc

Now,, the database is still in use until I get the new one running and I'd like to keep the ID numbers for the systems at least. I dont mind if there are gaps in the number sequence as long as they are all unique.

I was thinking that if I started the new database at ID 30000 then I can get it ready and import the old records at changeover time.

Problem is that, without scripting knowledge. I cant really figure out how to do this DMAX thing that people on the forum refer to.

Can anyone give me a simple, how-to, given the guidelines above?

Many thanks,

Paul

Here is a link that discusses what auto numbers ARE and what they ARE NOT.
Somethings to consider
http://www.utteraccess.com/forums/showflat.php?Cat=&Board=53&Number=443604

G1ZmO · Mar 25, 2008

Thanks for that.

I accept that using the autonumber is not the way to do the job but need to know how to set up an incremental record numbering system which starts at a specific number.

The numbering doesnt need to be sequential but the record numbers have to be unique.

If I cant get a simple way of doing it then I will HAVE to use autonumber and forget about importing any records which isnt ideal.

Autonumber question again sorry

G1ZmO

Registered User.

Dennisk

AWF VIP

G1ZmO

Registered User.

The_Doc_Man

Immoderate Moderator

G1ZmO

Registered User.

G1ZmO

Registered User.

jdraw

Super Moderator

G1ZmO

Registered User.

Similar threads

Users who are viewing this thread