CODEWITHSUNDEEP
sqldatabasems-accessms-access-2013
BootyBump
BootyBump

Reputation: 59

Access Relationships issue with data not being pulled to correct table

My database does not seem to be working correctly. Amongst other things I am discovering along the way (!), I am unable to get the payment values for exams in the PAYMENT table to show up in the EXAM table as they do for the classes in the CLASS table.

Here is the relationships table in Access.

I would like it to show the payments in the EXAM table but it is not, and only showing the entries from the PAYMENT table for the classes like here.

Here are the values in the PAYMENT table for the exams which I would like

.

I am not able to get a full SQL of the actual database from Access as it doesn't allow it, but I can get some SQL from making a query with all the tables, but be aware that I cannot program any suggestions into the database, only I can change the relationships and join types in the visual Relationships screen: pastebin.com/RS6tT8C3

SELECT 
FROM (Parent INNER JOIN Student ON Parent.[Parent ID] = Student.[Parent ID]) INNER JOIN (([Exam Type] LEFT JOIN (Exam INNER JOIN [Exam Results] ON Exam.[Exam ID] = [Exam Results].[Exam ID]) ON [Exam Type].[Exam Type ID] = Exam.[Exam Type ID]) RIGHT JOIN (([Class Type] LEFT JOIN (Class INNER JOIN Attendance ON Class.[Class ID] = Attendance.[Class ID]) ON [Class Type].[Class Type ID] = Class.[Class Type ID]) RIGHT JOIN Payment ON Class.[Class ID] = Payment.[Class ID]) ON Exam.[Exam ID] = Payment.[Exam ID]) ON (Student.[Student ID] = Payment.[Student ID]) AND (Student.[Student ID] = [Exam Results].[Student ID]) AND (Student.[Student ID] = Attendance.[Student ID]);

An extremely helpful database manager suggested to me yesterday on IRC that I should do: 

payments left join exams on examid = examid left join classes classid = classid

Which I believe I was able to do by changing the join type option in the relationships tab but it didn't seem to work. I have just done 1 module of database with no SQL programming so it's been a steep learning curve!

It's gotten to a stage that everything seems too complicated and I'm losing track of things lol If anyone could give me any advice in the next few hours as I have the assignment to hand in today then it would be very appreciated. Thanks!

**I will include the full database file also.

Upvotes: 2

Views: 1409

Answers (1)

Kefash
Kefash

Reputation: 533

First lets see if we are on the same page. It see as if you want to be able to see the Payments for exams when you select the sub datasheet. if so go to the exam table and switch to Design Mode then select Property Sheet Then change the field under Subdatasheet Name to the table you want. then match the [Exam ID] under Link Child Filds and Link Master Fields

enter image description here

PS. That's the ideal way to present data to users you should consider using forms for better control. Just a side note even though off topic its not a good practice to have spaces in your table names or table column headings.

Upvotes: 2

Related Questions