Create autonumber field in make table query

[workerbee777]

So, we've discussed how to make a new table with an autonumber field using only SQL statements. However, I need to make a new table with a new autonumber field using a make table query (and referencing data in multiple linked tables). I have heard of doing this by referencing the record number of the new table, but haven't been able to get it to work.

Any ideas?

Mucho TIA

 

[scriverb]

From your question you infer that there has been previous discussions about using SQL to create a new table with an exisiting autonumber field. I haven't seen that Thread but if you post it I would like to see how they do this function. I don't believe that you can use the make-table query to create an Autonumber field from within the make-table query. The only way that I know is to perform the following:

docmd.setwarnings false
DoCmd.openquery "qryMakeTableQuery"
docmd.setwarnings true
Dim DB as DAO.database
Set DB = CurrentDB
DB.Execute "ALTER TABLE tblNewTableName ADD COLUMN RecCounter AUTOINCREMENT"
MyDB.Execute "CREATE INDEX RecCounter ON tblNewTablename(RecCounter) WITH PRIMARY;"

You now have created your new table, created a new field)RecCounter) in the table which is an Autonumber type, and finally created a primary index for the table as the new autonumber field(RecCounter).

If this is not what you are looking for please get back with more info.
Bob Scriver

 

[workerbee777]

Bob,
Thanks for the idea. I will look for that other thread--I searched long and hard before I posted my question.

The only reason I'm using Autonumber is to assign a sequential order to the records in a table becuase the table is not well normalized. I can have several clients with multiple treatment dates, and need to know the most recent one. The way I've been working around the table setup is to select the most recent treatment date by assinging an autonumber to the table (which is sorted by client, then in descending treatment date). I have no control over the db setup, so I'm doing the best I can... any other thoughts?

Thanks

 

[scriverb]

Well the true normalization would have you setting up two tables. One being the Client table with all pertitient information about the client. The second being a Treatment table which would be in a one to many relationship. One record per client with many treatment records.

But, in the situation that you have right now sorting by Client and then by Treatment Date will in fact give you the ability identify the order of the treatment. The easiest way to identify the most recent treatment record would be to use a query:

Select tblClient.ID, tblClientName, Max(tblClient.TreatmentDate) as LastTreatmentDate
FROM tblClient
Order By tblClient.ID
Group By tblClientID, tblClientName;

This will give you the most recent record for each client. You don't need to use the AutoNumber although it is always good to have it available in case you need it to see the order of transaction entry.

Let me know if this helps your situation. Bob Scriver

 

[workerbee777]

Bob,
You're right, it would be fairly easy to identify the latest treatment. And, in a perfect world, the tables would be normalized. However, I need to manipulate this data in other queries, etc. Since I'm stuck with this setup...

What I have been doing is sorting by client, then treatment date, and pasting all data into a new table. Then I manually insert an autonumber field (call this ID) so that for future uses, I can simply refer to the min ID for any client and know that this record includes the latest treatment info.

I was hoping to be able to create that ID field without manually creating the autonumbering.