Using MS Access/sequential numbering in access
QUESTION: Hi Scott,
I read your great examples on sequential numbering but need a little more clarification. I'm not sure where I'm putting the sequence field and code.
I will be importing my data so the user will not be entering anything.
I have one table that holds my Family information (tblFamilyAccount) and another table that holds my Family Member information (tblFamilyMember). The data I'm importing has a Family Account ID but I want to assign a sequential number for each member of that family so I have a unique ID for them. (ie Rose Family is FamilyAccountID 100, Drew Rose is FamilyMemberID 1001, Ali Rose is FamilyMemberID 1002, Sam Rose is FamilyMemberID 1003, etc... I want that FamilyMemberID field to be a number field.
Right now I import my family data first into tblFamilyAccount, then I import the family members into a temporary table with an AutoNumber field to create their unique FamilyMemberID, then I append those names and IDs into tblFamilyMember. The IDs are already getting to be really large numbers so that's why I wanted to use the Sequential numbering option. Any specific instructions you can give me would be very helpful. Thanks so much!!
ANSWER: First, I would try this on a copy of your db first.
After you have imported everything I would add the Sequence field to tblFamilyMenber (if its not already there). I would then run an Update query like so:
UPDATE tblFamilyMember SET Sequence = Nz(DMax("[Sequence]","tblFamilyMember","FamilyAccountID = " & FamilyAccountID);
See if that works correctly assigning the Sequence numbers.
If it doesn't then you will need to Loop through tblFamilyMembers, assigning the Sequence for each record.
---------- FOLLOW-UP ----------
QUESTION: Hi Scott, Thanks for your quick reply. I tried that Update query and it ran and said it was updating all the records but the Sequence field was still blank. I'm not sure I'm doing it correctly. I added a Sequence field, but what field is actually being updated in this update query? I would like the FamilyMemberID updated with the FamilyAccountID + the Sequence field. All those fields are in the table tblFamilyMember. If it doesn't work, how do I loop through tblFamilyMember to assign the Sequence for each record?
Thanks so much for your help - I really appreciate it. If you think there's a better way to assign a unique field to my Family Members, let me know. The only unique field that comes over from my import is the FamilyID. I could use a combo of FamilyID + FirstName + birthyear. Let me know your thoughts.
First, Sequence should be a separate field and kept separate. You should use an Autonumber as the PK for the Member table, but use an expression to display the combination of FamilyID and Sequence. My blog article details this.
It is the SEQUENCE field that should be update. After running the UPDATE query, did you look in the Sequence field? Sort the table by FamilyID and Sequence. You should see soemthing like this: