Using Unique Keys stops data from filling

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings,

I have two tables. Table A is student records with a unique student id.
Table B is each students weekly lunch selections, again with a unique student
id.

When I join the two tables with a query, utilizing the student id from table
B, the fields in the query from table A do not refresh. If I allow duplicates
on table B, then refresh is instant.

Am I missing something? Both tables must have no duplicates, but I do not
see how to accomplish my task with the query and an associated form if I
cannot get that instant refresh.

Thank you in advance for your support,

DKB
 
Is the StudentID the primary key in both tables? Why don't you put an
autonumber key in the meals table as the primary key and then use a one to
many relationship to link the two table together for updating.

example:
tblStudents
StudentID (Primary Key)
Student Last name
Student First Name
etc.... any other fields that are unique to the student

tblLunchMenu
MenuID (Autonumber) (Primary Key)
StudentID (Foreign Key to be used with relationship to StudentID in
tblStudents)
Date
MenuItems
etc... any other field relevant to the weekly menu

Build your query with both tables listed and you should be good to go.

HTH
Wally Steadman
US Army in Iraq
 
Walter,

First of all, be careful over there. We want all of you to come home soon,
safe and sound.

Now, back to Access... I thought about doing what you suggested also, but it
still leaves me with the possiblility of accidentally creating more than one
'meal' record per student, which is my primary objective here. Any other
suggestions?

Thanks again, and come back soon,

DKB
 
What is the structure of your meal selection table? Is it your intent to
make sure Johnny doesn't get Lasagna on Monday and then again on Friday? I
take it, the students are not choosing their meals?

and thanks for your thoughts
 
Walter,

Well, in this case, the parents are pre-selecting and paying for their kid's
daily lunches for the semester. There are specific selections each day, and
each student's choices are to be entered when the appropriate form is filled
in and payment is made.

So, each unique student record StudentID/LName/FName/Class/Grade/Instructor,
has a unique weekly menu choices file StudentID/MonChoice/TuesChoice/Wed...
that will hold for the entire semester, yielding a one-to-one file
relationship.

I tried to combine them into a single larger file, linked to the 'Menu'
file which contains Day/Item#/Description/Price, but then I still get no
automatic field population in either the query nor the associated form.

Oh, and I really meant what I said. I have friends in the military, and
additional friends with children stationed overseas. Keep up the great work,
keep your head down, and come back soon.

DKB
 
Back
Top