473,373 Members | 1,153 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,373 software developers and data experts.

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

15
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.
Feb 17 '16 #1

✓ answered by 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.

6 1441
Seth Schrock
2,965 Expert 2GB
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.
Feb 17 '16 #2
NeoPa
32,556 Expert Mod 16PB
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.
Feb 18 '16 #3
MrYoda1
15
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.
Feb 18 '16 #4
NeoPa
32,556 Expert Mod 16PB
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.
Feb 18 '16 #5
MrYoda1
15
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!
Feb 19 '16 #6
NeoPa
32,556 Expert Mod 16PB
You're welcome, and it's been a pleasure working with you.
Feb 22 '16 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Jeff Silverman | last post by:
I am working on my first database and I think I want to build a table with a foreign key in it. The O'Reilly book on MySQL says that MySQL does not support foreign keys, but it still talks about...
31
by: Robert Brown | last post by:
Let's say I have a type hierarchy: (just an example) the general entity customer: CREATE TABLE customer(customer_id int, customer_name varchar(250), customer_type int) three specific...
10
by: D. Dante Lorenso | last post by:
I'd like to run a clean up command on my tables to eliminate rows that I'm no longer using in the database. I want to do something like this: DELETE FROM tablename WHERE...
5
by: Ross A. Finlayson | last post by:
Hi, I'm scratching together an Access database. The development box is Office 95, the deployment box Office 2003. So anyways I am griping about forms and global variables. Say for example...
1
by: Thomas T. Thai | last post by:
I'm looking for a better way to make use of foreign keys. Here is a sample setup: -- TESTING Foreign Keys create table mod ( mod_id int not null primary key, name varchar(32) not null...
9
by: sonal | last post by:
Hi all, I hv started with python just recently... and have been assigned to make an utility which would be used for data validations... In short we take up various comma separated data files for...
6
by: ravichoudhari | last post by:
i came accross requirement of multiple foreign keys in a table referencing the same primary key another table. i created the table relations using the relations editor in access. even though i...
9
AMT India
by: AMT India | last post by:
I am using mysql 4.2. I have a big database with lots of data. Now there is no foreign key relationship between the tables. But every one has a primary key. Can I alter these tables to bring foreign...
4
by: Wolfgang Keller | last post by:
Hello, so far it seems to me as if the only ORM module for Python which supports composite primary/foreign keys was SQLAlchemy. Which looks a little bit "overbloated" for my needs: I "just" need...
2
JnrJnr
by: JnrJnr | last post by:
I have two SQL database tables. One contains various products(with unique primary keys) and the other contains information related to the products aswel as the product's foreign keys. What I want...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.