Need query to return empty records (1 Viewer)

snorf3

Registered User.
Local time
Today, 12:11
Joined
May 25, 2001
Messages
45
I have 4 tables set up with one table as the main table and the other 3 in one-to-one relationship to the main table.

The main table contains student info (name, ID, etc.) and each of the other 3 tables contains info about a specific year of their program...tblOne = courses in year 1, tblTwo = courses in year 2, tblThree = courses in year 3.

I want to run a query to print a report of the student's progress. The problem is, if the student is in year 1, no record exists for them in either tblTwo or tblThree, and the query cannot return anything on that student. There are no problems with a student in year 3, since a record exists in each of tblOne, tblTwo, and tblThree under that student's ID number.

Is there some way I can get the query to return all students, regardless of what year they are in?

Many thanks for your help and advice!
 

Abby N

Registered User.
Local time
Today, 12:11
Joined
Aug 22, 2000
Messages
123
I don't know exactly how your query is set up. So this is a bit of a shot in the dark. However, try changing the relationship between your main table and the yearly tables to include all items from the main table and only those records in the yearly table where the joined fields are equal. To do that open your query in design view and right click on the black line linking the tables. Select 'Join Properties' from the pop-up menu. You'll need to do this for each of the relationships.
 

snorf3

Registered User.
Local time
Today, 12:11
Joined
May 25, 2001
Messages
45
Hmmm....not sure what you mean about the relationships. The tables are joined only on the StudentID number. The rest of the fields are different in each table.

Right now the query is simple a select query containing all fields from all tables, and no specified criteria.

So, it will return any student that has a record in each of the 4 tables, but will not return a student that is missing a record in any of the 4 tables.

Is there some kind of "Or IsNull" criteria or something I can set so that it will return students who do not have records in all four tables?
 

Abby N

Registered User.
Local time
Today, 12:11
Joined
Aug 22, 2000
Messages
123
Sorry I wasn’t more explicit in my original post. I’ll try to sort things out.

In your query's design view there should be black lines connecting the [StudentID] field in your main table to each of the yearly tables, yes? This line represents the join relationship of the tables it connects. By default these joins will limit the recordset produced by your query to records where all the joined fields are equal. Which is what’s causing your problem. To fix this you’ll need to change those joins to include all records from your main table without regard for matching records in the yearly tables.

To do that, right click on one of those black lines. A pop-up menu will appear with two menu items, 'Join Properties' and 'Delete'. It can sometimes be a little difficult to click exactly on the line. So, don't be surprised if you have to try a few times. Once you get that pop-up menu select 'Join Properties'.

You will then be presented with 3 options. The second or third option will be something to the effect of "Show all records from table 'Main' and only those records from 'tblOne' where the joined fields are equal." Select that option and press 'OK'. The black line connecting [StudentID] will now have an arrow pointing to the tblOne. Repeat this process for the other two joins and you should be good to go. No criteria are necessary.

If you have any problems you can paste the SQL statement here and I can set the joins for you that way. Good luck!

~Abby
 

snorf3

Registered User.
Local time
Today, 12:11
Joined
May 25, 2001
Messages
45
Got it this time! Thanks Abby... it works perfectly.
 

Users who are viewing this thread

Top Bottom