New query does not produce results

[ppines]

I am new to MS Access and used the Query Wizard to design my query. However, it is producing no results. What am I missing?

SAMPLE DATA

QUERY DESIGN

SQL

RESULTS

 

[tvanstiphout]

The join line between two tables indicates that the values in the joined fields must be the same.
Looking at your Grade and Student tables for example, you are saying that GradeID and StudentID must match up. But that is wrong. The join line should be between Grade.StudentID and Student.StudentID.

 

[The_Doc_Man]

The usual answer for this is that, since this is a four-way inner join, at least one of the four tables doesn't contain indexes to match the other tables. The solution to finding which one is to build the query again, adding in one table at a time, to see which table isn't matching up.

However, I'm going to take a wild stab here and save you the effort. Your query contains this JOIN specification:

... INNER JOIN Numeracy_Grade ON Numeracy_Student.StudentID = Numeracy_Grade.GradeID ...

But GradeID is numbers like 1, 2, 3, 4, ... whereas StudentID is a list of 7-digit integer numbers. They won't be equal. If they are not equal, then that JOIN will be empty.

 

[ppines]

Thank you. If I rename StudentID to Student and create a new StudentID as per the others, this query should produce results. I will amend the table and try again.

 

[plog]

If I rename StudentID to Student and create a new StudentID

...FROM ((Numeracy_Student INNER JOIN Numeracy_Enrolled ON Numeracy_Student.StudentID=Numeracy_Enrolled.EnrolledID) INNER JOIN Numeracy_Grade ON Numeracy_Student.StudentId = Numeracy_Grade.GradeID) INNER JOIN [Numeracy_UNSW Maths] ON Numeracy_Student.StudentID=[Numeracy_UnSQ Maths].MathsID...

Probably not. You need to fix every single JOIN you have, none seem right. Does StudentID really equal EnrolledID, GradeID and MathsID? That can't be right. You never link the primary key of one table to the primary key of another table. The primary key in one table gets linked to a plain number field in another table.

I am new to MS Access...

Not a problem. But let me give you some general pieces of advice:

1. Don't screenshot SQL for us. Copy and paste it. That way we can manipulate it and fix it easier.

2. Don't use spaces in table and field names. When you do it makes coding and querying more difficult.

3. Simplify, Divide, Conquer and Advance. When this thing didn't work you should have stripped all but 2 tables from your query and got just those 2 tables to give you the correct results. When it did, add the third table. When that worked, add the fourth. Initially, if your feeling lucky, shove everything in your mouth at once and hopefully you don't choke on it. But once you do choke, you need to dislodge everything, cut things up into bite size pieces, chew them one at a time and get them down that way.

 

[DHookom]

Always begin with two tables and get the join correct. Then add another table and get the join correct. Then add another table and get the join correct.

 

[ppines]

Thanks for all your help. It finally worked!