0

I have 2 tables :

  1. StudentInfo
  2. Attendance

The StudentInfo table has fields about students like: RollNo, FirstName, LastName etc...

The Attendance table has the fields: RollNo, FirstName, LastName, InClass(Yes/No) and Date.

I want to duplicate the common fields from StudentInfo to Attendance and when I add a new entry in the StudentInfo table it will automatically add them to the Attendance table.

Also, when I add attendances in the Attendance table then I want to be able to view the data by months or dates, so that I can see on what day how many students attend the class.

Can you suggest a way for me to store attendance of students like we store on register, your help will be really appreciated!

Christopher Moore
  • 3,071
  • 4
  • 30
  • 46
OfficialHS
  • 3
  • 3
  • Is there a reason you're putting the same values in two tables? The beauty of a relational design is being able to store data only once. – Andrew Brēza Sep 24 '18 at 12:59
  • 1
    Spend 2 hours searching/reading about `Relation database design`. It wil give you a 500% return for sure. – iDevlop Sep 24 '18 at 13:07
  • To repeat what @AndrewBreza and @PatrickHonorez have said: You don't need to store duplicate student info (first name and last name) in your `Attendance` table. As long as it's in your `StudentInfo` table, you can obtain the student's name for a class by using a `JOIN`. – Zack Sep 24 '18 at 13:09
  • forget about the other fields but i really need to put the RollNo filed on both tables so that i can create a relation between them to use query and when i enter rollno on student info table it will also auto add on Attendance table. so is there any suggestion plz i am new to ms access , your help is really mean to me. – OfficialHS Sep 24 '18 at 13:13
  • @Zack i am new to ms access , how does JOIN command work ? can u give some explanation to it ?? – OfficialHS Sep 24 '18 at 13:15
  • A join - as the command implies - joins two tables together. This generally happens based on a key field shared between the two tables - such as `RollNo`. If you join `StudentInfo` and `Attendance` on the `RollNo` field then `Attendance` doesn't need the student name - it will look it up off the `StudentInfo` table on demand. Then, if _StudentA_ changes their name you only have to change it in one place and not for every day they've attended. – Darren Bartrup-Cook Sep 24 '18 at 13:40
  • @DarrenBartrup-Cook Thank u so muchh dareen , i need to know where this JOIN command work? on expression builder ? or can u give me example of this JOIN Command plzz – OfficialHS Sep 24 '18 at 13:49
  • Visually you can see a join when you build a query - it's the line joining two tables. As text a join is in the `FROM` clause of the SQL and will look something like `SELECT * FROM TableA INNER JOIN TableB ON TableA.PrimaryKey = TableB.ForeignKey` – Darren Bartrup-Cook Sep 24 '18 at 14:17
  • @DarrenBartrup-Cook Thank u so muchh i will try this :) – OfficialHS Sep 24 '18 at 14:36
  • 1
    Read [this article](https://support.office.com/en-us/article/create-a-simple-select-query-de8b1c8d-14e9-4b25-8e22-70888d54de59#bm3_2) for information about using JOINs in Access. – Zack Sep 24 '18 at 14:41
  • Good luck - I should point out that asking how a `JOIN` command works is similar to asking how a car works and someone saying "put your foot on the accelerator" - there's so much more than _just_ joining tables. – Darren Bartrup-Cook Sep 24 '18 at 14:43

0 Answers0