Lookup Wizard Question (Very new to Access/Database Design)

[navyguy58000]

Good afternoon, I am very new to access/database design and have no formal training and mostly learning by trial and error. I am not sure how to really ask this question sensibly, so ill try to write it out what's in my head.



I have a table of a student, and a student has a last name and classNumber (populated with lookup wizard grabbing from classInformation table) There is more information that a student has, but its not relevant.
I have a table, its called 341s (its a military thing, but its a positive or negative behavior or act sheet) a 341 has last name (lookup from student table referencing lname) and a 341 has a class number. We need to be able to track both for various reasons. Im trying for the life of me to be able to reference lname on student info for lname on 341 and then based on the last name picked, it populates class number appropriately.

If this doesn't make sense I can try and do better explaining my thoughts. Thank you in advance for your help!

[Welshgasman]

So what happens when you get two Smiths in the same class?

[navyguy58000]

I guess you could refence by the studentInfo Primary Key and Last Name then, (Primary key is their DoDID #) so that will never be duplicated.

So then on the 341 table for lName a lookup wizard that references DoDID and lName to choose the person, but then is it possible to populate the classnumber column in the 341 table automatically with the classnumber of the student chosen?

[Welshgasman]

Yes.
If you are using a combo for the student, then bring the class number in as an extra column, but hidden.
Or use a DLookUp() to get it.
Either way, you would not store it again, just look it up when needed.

[navyguy58000]

Thank you! This makes sense, appreciate your time!

[mike60smart]

Hi NavyGuy from an ArmyGuy

I would recommend that you do not use the "DoDID #" as the Primary Key.

You should have a specific Primary Key which would be an Autonumber.

You would then create a separate field "DODIDNumber" - You should not use spaces or Characters in field names.

[ssanfu]

Good afternoon, I am very new to access/database design and have no formal training and mostly learning by trial and error. I am not sure how to really ask this question sensibly, so ill try to write it out what's in my head.
I have a table of a student, and a student has a last name and classNumber (populated with lookup wizard grabbing from classInformation table)

It sounds like you have a Look up FIELD in a table (at the table level). This is considered BAD. See The Evils of Lookup Fields in Tables

You might also see The Ten Commandments of Access

....I have a table, its called 341s (its a military thing, but its a positive or negative behavior or act sheet) a 341 has last name (lookup from student table referencing lname) and a 341 has a class number.....

Some suggestions on naming access objects:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not begin an object name with a number. <-- Sometimes the Access Gnomes get persnickety when an object name begins with a number.
Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.


You could use "tbl" as a prefix... "tbl641" for a table..., "frm341" for a form