Need to create a new unique identifier in sequence to existing IDs

[deanslgr]

I oversee an Access database that was developed before my time by someone who is no longer with the company. The DB holds patient records on inpatient stays. For the past year and a half we only been getting records from a single source, where they assign a patient ID before sending us the patient records. Our database was built around that patient ID. There are several interactive forms and tables in the DB all revolving around that ID (rebuilding the DB is not an option). We have started receiving records from other sources that need to be added to the database, however I need to figure out an automated way to create an ID for those new patients.



The new data are imported from Excel spreadsheets.

After reviewing several threads on this board, I thought maybe I could use the DMAX function to create the new IDs. So I created a master table of all the unique patients and their IDs.

My first attempt was to try an append query appending the data from the spreadsheet into the master table:

New_ID: DMax([PATIENT_IDENTIFIER],"PATIENT_IDENTIFIER_NUMBERS")+1

This worked for the first patient in the spreadsheet, but then it added that person to the master table a thousand times each with a newly created ID.

So then I appended the new patients into the master table leaving the ID blank and attempted an update query:

Update to: DMax([PATIENT_IDENTIFIER],"PATIENT_IDENTIFIER_NUMBERS",[Patient_Identifier_Final] Is Null)+1

I created a second field in the master table, "Patient_Identifier_Final," to fit the 'null' criteria. If I said where PATIENT_IDENTIFIER is null I didn't think that would work because that is the field where the query is getting the max ID from. This, however, did not work.

So now I am at a brick wall. The DMAX might not be the way to go, but I cannot find any other way of doing it.

[June7]

Review recent thread on this same issue https://www.accessforums.net/queries...ers-33632.html

There is always VBA code option.

[deanslgr]

Thanks, but I read those threads and they involve forms. I'm not using a form. The new patients are being imported into the database 30-40 a day via Excel spreadsheets. So I need to know how to create an ID either during the import/append process or via an update query.

What would be the VBA option?

[June7]

That entire thread discusses updating a table with a unique identifier. Form objects are not involved. Only one post references another thread which had no bearing on the solution.

VBA would open a recordset, loop through records, set value of ID field for each record.

[deanslgr]

OK, yes, you are correct. The thread https://www.accessforums.net/queries...ers-33632.html is not about forms. However, I am not seeing a solution to my problem. I have a series of numbers. I am not concatenating any fields. If I adapt your update code to Format(DCount("*","Table")+1,"000") all I get are 193 records all with the Patient ID of "194."

So I guess what you are telling me is that I need to find a VBA, not an Access, forum.