MS Access - Confused about primary/foreign keys and populating tables

[MrYoda1]

I'm sure this is fairly basic, but the concept isn't clear to me. I'm confused about how certain data that is imported into a table is also propagated to the joined tables at the same time.

The situation is, I have three tables: tblStudents, tblBorrowed, tblPaidBack (this is used to keep track of lunch money students borrow from the cafeteria and then pay back throughout the school year).

tblStudents - StudentID (PK), LastName, FirstName, Grade, Teacher

tblBorrowed - BorrowedID (PK-auto number), AmtBorrowed, DateBorrowed

tblPaidBack - PaidID (PK-auto number), AmtPaid, DatePaid

I know I need some foreign keys somewhere. I figure that StudentID should be added to tblBorrowed and tblPaidBack as a foreign key to create the relationships. So, my first question is, is this correct, or should the two primary keys for tblBorrowed and tblPaidBack be added to tblStudents?

I plan on importing all of the student's info from an Excel file to setup the initial student info in tblStudents. How does the StudentID information also end up in the StudentID field in tblBorrowed and tblPaidBack. Do I have to manually add that info in the two tables so all of the joins work?

I created a small, test version with StudentID in each of the other two tables. I tried to enter an amount borrowed and couldn't do it unless there was a corresponding StudentID in tblBorrowed, which I had to enter by hand.

If I reverse the keys and put into tblStudents the two keys from the other tables, the joins don't make sense to me.

I'm not sure if this question really is about primary and foreign keys as it is about how joined tables "talk" to each other through the keys.

I think that once I can conceptually get past this I should be okay.

Thank you for any help you are willing to provide.

[Seth Schrock]

I think of it this way. You are tracking transactions. You need to know who each transaction is for, so you need the student information in the transaction table. Since each student can have multiple transactions, you would have a separate table for students (which you have), but you still need to know for each transaction which student did the transaction. Thus, the foreign key goes in your transactions table. You have this broken into two tables, but it should really just be one. You need a primary key, the student ID foreign key, transaction date, amount, and if it is credit or debit (credit is paid back and debit is borrowed).

As to how it gets populated, there are different ways. If you have a form based on tblStudents with a linked subform based on your transaction table, then the student ID foreign key will be automatically entered as the ID of the student currently being viewed in the main form. If you just have a simple form based on the transaction table, then you would have to enter the student ID in the foreign key field. However, to make this simpler, you can use a combo box (drop down box) that gets its values (row source) from the tblStudents table so that you can just select a name instead of remembering a number. As with most things in databases, there are a thousand ways to do things depending on what you want to do.

You might benefit from reading the following page on Database Normalization and Table Structures.

[NeoPa]

Seth already explained about using a single transaction table (tblTransaction?) with a direction flag of some form rather than the two similar tables you're thinking of.

If you want to import all the data from a spreadsheet then you should consider working on the spreadsheet first to format the data in such a way as to load straight into Access seamlessly. That would include adding a value to each transaction row to indicate which student it's related to.

It wouldn't be a simple import as a single SQL query. Populating multiple tables involves separate imports for each.

[MrYoda1]

When you say "direction flag" you mean whether money is being borrowed or paid back, right?

As to the method of importing, what I planned on doing was massage the student information in Excel so it matches up with the fields in Access. Since this is a onetime thing done at the beginning of the year I'll admit that I would cheat and just copy/paste append the Excel data into tblStudents.

I gather that since my copy/paste append method will only put the StudentID into tblStudents, I will need to find a way to do the same for tblTransactions, even if it means copy/paste append the StudentID into that table, as well, meaning I do need it in both tables?

>If you have a form based on tblStudents
>with a linked subform based on your transaction table...

This suggestion above is what I had planned on doing. I thought about a combo box as the method of selecting a student, but there are over 1,000 students so this might not be a very easy way for the user to find the one student they are interested in.

[NeoPa]

MrYoda:
When you say "direction flag" you mean whether money is being borrowed or paid back, right?

Absolutely. Yes.

MrYoda:
I gather that since my copy/paste append method will only put the StudentID into tblStudents, I will need to find a way to do the same for tblTransactions, even if it means copy/paste append the StudentID into that table, as well, meaning I do need it in both tables?

Once you have the tblStudent data uploaded copy the data (Only the name and the new ID are required.) back into a separate worksheet in your original spreadsheet. Now, using VLookup() against your original transaction data, add in the related student ID into your Excel data.

Once that's completed load it up into Access.

MrYoda:
This suggestion above is what I had planned on doing. I thought about a combo box as the method of selecting a student, but there are over 1,000 students so this might not be a very easy way for the user to find the one student they are interested in.

You may want to consider a completely separate form that lists the students. From here a command button, or even a simple double-click, can trigger code to open your other form filtered to exactly that one student.

[MrYoda1]

Seth and NeoPa--thanks for your help with this. Much appreciated, and...there will a Main Office secretary who will also appreciate your help since she can stop pulling out her hair trying to deal with the crazy Excel file she had been using before asking if I thought there was a better way!

[NeoPa]

You're welcome, and it's been a pleasure working with you.