I have a database that is growing too big for it's britches.
I started out with a "Contacts" database that also tracks training courses that those contacts have been to. Our org. has been adding new training programs, and each type must be tracked separately. I had been adding these all as separate tables (i.e. applicant info., training info, etc. for each program), but have soon realized that I can't continue to to that!
I need to condense and I decided the best way to do it would be to store all the applicant's information and all the training information in one master table (one for each thing, that is, applicants, and trainings)...trying still to maintain a "normalized" database.
BAsically, the trainings table needs to have a unique ID, but it can't be solely numerical, and I'd like for users not to have to generate it by hand.
I like the autonumber feature of Access, and was wondering if it's possible to, say, prompt users for the alpha portion of the primary key (ID field) and somehow make the numerical portion default to the next available number for that program....
for instance, air quality trainings would be:
AQ1
AQ2
AQ3 and so forth
and water quality trainings would be
WQ1
WQ2
W23 and so forth
...but all stored in the same table and all part of the same primary key.
The kicker is these probably can't be a concatenated primary key because they are used further down the line as a concatenated primary key (in a junction table).
Any suggestions???
I started out with a "Contacts" database that also tracks training courses that those contacts have been to. Our org. has been adding new training programs, and each type must be tracked separately. I had been adding these all as separate tables (i.e. applicant info., training info, etc. for each program), but have soon realized that I can't continue to to that!
I need to condense and I decided the best way to do it would be to store all the applicant's information and all the training information in one master table (one for each thing, that is, applicants, and trainings)...trying still to maintain a "normalized" database.
BAsically, the trainings table needs to have a unique ID, but it can't be solely numerical, and I'd like for users not to have to generate it by hand.
I like the autonumber feature of Access, and was wondering if it's possible to, say, prompt users for the alpha portion of the primary key (ID field) and somehow make the numerical portion default to the next available number for that program....
for instance, air quality trainings would be:
AQ1
AQ2
AQ3 and so forth
and water quality trainings would be
WQ1
WQ2
W23 and so forth
...but all stored in the same table and all part of the same primary key.
The kicker is these probably can't be a concatenated primary key because they are used further down the line as a concatenated primary key (in a junction table).
Any suggestions???
