Access Database pulling data from one table to another table

[dina1808]

Hello-

I'm new to Access and started created new Students database with Fees Collecting details.

I had created 3 Tables;

1 - Students


2 - Course
3- Fees

While Collecting Fees, I'm looking to pull Student Name, DOB from Students Table and Course, Course Fee from Course Table and updating Fees paid and paid date in Fees table.

Please advice how to get Student name, DOB,Course, Course Fee in Fees Form.

Also, is there any way to sum fees paid by student multiple times and reflect in Fees due field.

Thank you,
Dina

[andy49]

Your tables aren't set up correctly Dina.

Try
Tblstudent is fine.

Your fees table should contain no info which is in another table such as first name, DOB

When that is correct you'll be ready to join tables in a relationship database


Sent from my iPhone using Tapatalk

[June7]

DO NOT duplicate student and course info into Fees table, only save the StudentID and CourseID.

[dina1808]

I had removed all fields. Please advice how to get Student Name and DOB in while collecting fees

[andy49]

Suggest some renaming
Tblcourse
CourseID_PK
CourseName
courseFees

Tblstudents
StudentID_PK
Rest are fine


TblFees
FeesID_PK
StudentID_FK
CourseID_FK
PaymentReceipt
Feespaid
PaidDate
remarks.







Sent from my iPhone using Tapatalk

[andy49]

Use relationships window to join pk fields to Fk fields.


Sent from my iPhone using Tapatalk

[dina1808]

Thanks Andy.. It looks fine.

but while collecting fees, we should verify the student name & DOB when we type student ID and the same for Course ID should display Course Name but the Fees table only displays Course ID and Student ID...
I'm looking for search option for student name or DOB and same for Course Name

I'm also planning to insert fees due field in Fees table. Is it possible to reflect the total amount in that field.
Ex. If Student A has paid fees twice of 10000 with multiple dates towards total fees of 30000. when he pays fees third time the fees due should reflect 10000.

Sorry to trouble you by dragging into much deeper!! your help will be much appreciated!!!

[andy49]

Can you bring all your tables into a query window then post that here Dina. A database stores only necessary information and what you've planned should be possible with just your three tables.

[dina1808]

Thanks and here you go...

[andy49]

Drag the fields you need first name last name course etc and then run your query

[dina1808]

No, I got that from query but I'm looking details in Fees Form...

In the below Fees Form, if one student is paying fees and if he don't know his ID. how do I pay fees. so in Fees Form, I'm looking for Search option to get students list from students table from there I select student... Please suggest .

I hope you got my query.

[andy49]

Your record source for this should be a query which includes names.

[June7]

Use comboboxes to select student and course. The comboboxes can be set up to display the descriptive student and course names but save the IDs. This is basic Access form design. Have you completed an introductory tutorial book?

Options for form design for a many-to-many relationship are:

1. single form bound to tblFees with comboboxes to select student and course (this is the form you posted, only need comboboxes)

2. main form bound to tblStudents and subform bound to tblFees with a combobox to select course

3. main form bound to tblCourses and subform bound to tblFees with a combobox to select student

It is not necessary for form RecordSource to include all three tables. You don't really need Gender, Mobile, DOB to show for options 1 or 3, however, option 2 will allow you to easily enter a new student record as well their payment info.

There is no reason to display the FeeID_PK but if you do, the textbox should be set as TabStop No.