Assigning a constant autonumber across 4 tables

[apc0243]

Hi all!

I have a database I'm designing that will aid in the administration of a school program. The DBUsers will be updating this information, regularly adding new students, parents, coaches, and mentors as the program moves along. Another function of this database is to house survey responses.

The way I've designed it (and it's way too late to change this unless this problem is insurmountable) is that survey responses are loaded in from an external source like this:

Member_ID | Survey_ID | Question_ID | Academic_Year | Answer

This way, I can query a single member and pull all their answers (or their answers to a single survey, or a single question over time, etc). This is an absolute necessity.



My problem arises in the Member_ID assignment. Initially, my plan was to just format the autonumber to add an alpha identifier (S502 would be Student 502, while P502 would be Parent 502). However using the format option in the table design is just superficial- the actual ID in a query is just the number (re: 502 and 502, my uniqueness is now gone).

Does anyone have any idea of how to create an ID-builder that works across tables? An idea that was suggested to me is to have the ID be populated in a master list with a member group type, and then the respective tables use a lookup to a query that separates them into groups. This would cause problems, however, because by adding a new student the new ID is created before the information is entered. If, for whatever reason, the DBUser needs to cancel then it's easy to undo the information, but I haven't developed a system to undo the ID creation. I think I can use a SQL command to delete the ID (that I've stored in a local variable), or to set the new ID using a form that is hidden until the overall information is accepted. I'm not sure how to implement this though.

Does anyone have any suggestions? Thanks!

[orange]

You may want to read this regarding autonumbers

[June7]

These sites might be of interest:

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

Use Autonumbers properly
http://access.mvps.org/access/general/gen0025.htm

If you use autonumber ID and allow user to cancel data entry, best to accept that the number is lost and there will be gaps in sequence. Alternatives require complicated code.

Generating sequential custom unique identifier is a common topic in forum.

[apc0243]

I don't care about the sequence integrity, you both missed my point.

The issue is that I don't want an ID generated and not assigned. I know how autonumbers work. The point is that I need to generate a primary key whos generation technique (and sequence) is shared between 4 tables. The reason that it has to be GENERATED is because I don't want to leave ID generation to the DBUser. So I could allow the autonumber to exist, but I need assignment to be automatic so that the user hits "new student" and that new student has a memberID already created. That memberID cannot be unque and unassigned because a regular query of ours is to count the number of members of each group (therefore, if S502 is created but not assigned, our count would be off. Granted, this could be accomodated, but I'd rather not, I want to solve the generation issue, not the reporting issue).

June, the first link says: "Sometimes people justify composite (multi-field) primary keys to avoid duplicates. This is the wrong approach. To prevent duplicates, create a secondary index for those fields and define it as unique. That lets the secondary index handle that job while the single field primary index retains its efficiencies."

I don't see an alternative.

[June7]

Whether or not the autonumber is used, one table has the generated ID as primary key. This value would be foreign key in related tables. Set up form/subform arrangement for synchronization.

What are data relationships - 1-to-1, 1-to-many, many-to-many?