Results 1 to 10 of 10
  1. #1
    davezachc is offline Novice
    Windows 8 Access 2013
    Join Date
    Aug 2014
    Posts
    5

    Not sure what's the problem (I suspect the issue is with Queries/a combination of Queries and Forms)

    I can't seem to pin down where the problem lies, probably because I'm very inexperienced in Access. But it has to do with some sort of duplication of student data.

    Intro: This is a school database to record student info, grades, enrolments (semesters, scores), and attendance.

    Here's the reproduction:

    This is a form showing a record of one student - DOEK CHHORVIN.
    There are a couple of sub-forms each with only ONE record.

    As you can see, there are 1095 records of students in total.
    Click image for larger version. 

Name:	Capture.jpg 
Views:	22 
Size:	203.4 KB 
ID:	17901
    (Capture.jpg)

    The multiple problems start when I decide to create a new enrolment record(because of a new semester) to the student through the "Enrolment Page" sub-form.
    The number of total student records increases once I refresh the form. It is as though I added a new student entirely.
    Click image for larger version. 

Name:	Capture4.jpg 
Views:	21 
Size:	192.1 KB 
ID:	17894
    (Capture4.jpg)

    It is an intended function that once a new Enrolment is made, NEW(meaning all data is 0 or null) Grading and Absence records are added as well.
    But because of the duplication of the student record itself. Each subform is also duplicated.
    (The two "Enrolment Page" records are different as per New Record,
    but both the "Grading Page" and "Absence Page" duplicated records take on data from previous records.)
    So now, looking at the "StudentDetails" form, there are 2 same students. Each of them have 2 same "Enrolment Page", "Grading Page" and "Absence Page"
    Click image for larger version. 

Name:	Capture9.jpg 
Views:	22 
Size:	179.4 KB 
ID:	17898
    (Capture9.jpg)


    This is the Student table where all the actual data is stored:
    Click image for larger version. 

Name:	Capture15.PNG 
Views:	21 
Size:	49.9 KB 
ID:	17895
    (Capture15.jpg)
    So obviously, no duplication of Student records.
    But, as intended, there are extra records in the Enrolment table.
    This is the list of records populated by query ran by clicking the "Search" button you see on the top right of every "StudentDetails" form:
    Click image for larger version. 

Name:	Capture14.PNG 
Views:	22 
Size:	64.0 KB 
ID:	17900
    You can clearly see the duplication here. So it shows that while the actual data in the Students table is not duplicated, creating a new record in the "Enrolment" page causes another record to appear in the query, making it seem in the form, like there are duplicate students.


    I'm seriously lost.
    Here is the accdb file if you would like extra testing(https://www.dropbox.com/s/1bc9cl6ji6...Ori.accdb?dl=0).


    I appreciate all the help I can get!

    Thanks in advance!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Downloading your db would take over an hour with my slowpoke internet. Suggest you make a copy and delete records and/or objects. Run Compact & Repair then zip the file.

    I suspect the issue is with the RecordSource of each subform. Is it an SQL that joins tables? Post the SQL statement.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    davezachc is offline Novice
    Windows 8 Access 2013
    Join Date
    Aug 2014
    Posts
    5
    Would a 7zip file be fine? It's 2MB:

    https://www.dropbox.com/s/p01kjjl7ll...%20Ori.7z?dl=0

    BY THE WAY, to login to the database, the Username and Password are the same.

    This is the SQL code from one of the queries used in the subforms. I'm not sure if it's what you need:
    SELECT tblStudents.ID, tblStudents.StudentID, tblEnrolment.StudentIDE, tblGrades.StudentIDG, tblStudents.LastName, tblStudents.FirstName, tblStudents.DateOfBirth, tblStudents.Sex, tblStudents.Age, tblStudents.MobilePhone, tblStudents.Address, tblStudents.HomePhone, tblStudents.EmailAddress, tblStudents.Notes, tblStudents.Attachments, tblStudents.Village, tblStudents.Commune, tblStudents.District, tblStudents.Province, tblStudents.Country, tblStudents.[FB account], tblEnrolment.EnrolmentNo, tblEnrolment.EnrolmentDate, tblEnrolment.[Amount Paid], tblEnrolment.Balance, tblEnrolment.Discount, tblEnrolment.Level, tblEnrolment.Time, tblEnrolment.Classroom, tblEnrolment.CostofClass, tblEnrolment.TeacherID, tblEnrolment.TeacherName, tblEnrolment.TLastName, tblEnrolment.TFirstName, tblEnrolment.ReceiptNo, tblEnrolment.DiscountReason, tblGrades.GradesID, tblGrades.StartDate, tblGrades.EndDate, tblGrades.mListen, tblGrades.mGramm, tblGrades.mGramm2, tblGrades.mRdg, tblGrades.mWrtg, tblGrades.mQuiz, tblGrades.mSpking, tblGrades.mSpdRdg, tblGrades.mCS, tblGrades.fListen, tblGrades.fGramm, tblGrades.fGramm2, tblGrades.fRdg, tblGrades.fWrtg, tblGrades.fQuiz, tblGrades.fSpkg, tblGrades.fSpdRdg, tblGrades.fCS, tblGrades.MidGrade, tblGrades.FinGrade, tblGrades.EngCamp, tblGrades.[1MoA], tblGrades.[2MoA], tblGrades.[3MoA], tblGrades.[4MoA], tblGrades.[5MoA], tblGrades.[6MoA], tblGrades.TotalAbs, tblGrades.DropOut, tblGrades.TPer, tblGrades.[1MoP], tblGrades.[2MoP], tblGrades.[3MoP], tblGrades.[4MoP], tblGrades.[5MoP], tblGrades.[6MoP], tblGrades.MScore, tblGrades.FScore, tblGrades.TAbs
    FROM (tblGrades RIGHT JOIN tblStudents ON tblGrades.GradesID = tblStudents.ID) INNER JOIN tblEnrolment ON tblStudents.StudentID = tblEnrolment.[StudentIDE]
    ORDER BY tblStudents.ID;

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    No, I cannot open 7zip files, only Windows Compression.

    A form can enter new record to only one table. I see 3 tables in that query. Why?

    Is tblGrades related to tblEnrolment? I suspect the issue is multiple 1-to-many relationships in the query. That will cause duplicate records.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    davezachc is offline Novice
    Windows 8 Access 2013
    Join Date
    Aug 2014
    Posts
    5
    Okay, Windows Compression (https://www.dropbox.com/s/0ckaray0fr...08%29.zip?dl=0)
    4.6 MB

    Hmm, you mean the "Search" query? Well, it was just to display all info in one line. But I don't know why other table data(Absence and Grades) are being duplicated every time a new semester(Enrolment record) is added.

    Here is the relationships:
    Click image for larger version. 

Name:	rel.PNG 
Views:	13 
Size:	35.2 KB 
ID:	17921

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Will each student have multiple records in tblEnrolment? If yes, then the query has two 1-to-many relationships and this will result in 'duplicate' data.

    Should not have tblEmployees in the relationship twice. Why is there a link between EmploymentID and TeacherName? TeacherName should not even be in tblEnrolment.

    I will try download db tonight.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    davezachc is offline Novice
    Windows 8 Access 2013
    Join Date
    Aug 2014
    Posts
    5
    Yes, they would have multiple records in tblEnrolment. So, the issue here in with relationships, correct? In layman terms, bad design?

    I don't know if the tblEmployees are contributing to the problem, but the main concern is the Enrolment. I believe that the TeacherName was meant to be just a data member in a record for each Enrolment record.

    Thank you!

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Have you looked at the Microsoft Desktop Student database template? I don't think it tracks grades. Student database is fairly common topic in forum. Try search.

    This seems to offer an expanded version of the template to include testing and grades http://www.microsofttemplates.org/mi...-database.html

    tblEmployees is not contributing to this issue. I simply noticed the relationship and added comment.
    Last edited by June7; 08-25-2014 at 06:02 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    davezachc is offline Novice
    Windows 8 Access 2013
    Join Date
    Aug 2014
    Posts
    5
    Are you suggesting that I re-create the forms/queries/tables by using the templates?

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Do what you want. Use the template outright or adapt or reject. Regardless of decision, hopefully you can learn from the examples.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Which forms use which queries.
    By polisasimo in forum Forms
    Replies: 1
    Last Post: 06-22-2014, 12:22 AM
  2. Forms and queries
    By MalvinkaAccess in forum Access
    Replies: 4
    Last Post: 03-15-2013, 12:25 PM
  3. Forms and SQL Queries
    By springboardjg in forum Queries
    Replies: 1
    Last Post: 06-07-2011, 06:18 AM
  4. Issue with Pass-Through Queries
    By MFriend in forum Queries
    Replies: 1
    Last Post: 07-04-2010, 10:54 PM
  5. Replies: 4
    Last Post: 04-01-2009, 08:49 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums