Queries Linking

[freddie1437]

Hello everyone, I have a Manager's database that tracks vacations. I have a 4 tables, one with the manager's information, one with Sick Days one with Full Week Vacations and one with Single Vacation Days. What I need is a report that has all those tables linked through each manager. The problem I am having is that I have it linked, but when a manager has a sick day, but no single day vacation, the manager wont show up. My question is how do I get this information to show up if they don't have any info on one of the tables???

Thank you

[Stewart Ross]

Hi. Use an outer join (a left join in Access terms) from your Manager table to each of the other tables in your query to see all manager details. If there are no illnesses etc to show for a manager the outer-joined fields from the illness and vacation tables will be listed as nulls (i.e. value not present).

To change a relationship from an equijoin to an outer join in the Access query editor right-click on the relationship line and select Join Properties. There are three properties listed - the first is the normal equi join, and the other two are left and right joins, direction dependent on the relative position of the tables within the join. Select the one which lets you show all rows from your manager table, and only rows in the other table where the joined fields are equal.

You need to change all relationship lines in your query - mixed equijoins and outer joins will generate an error message if you leave some of the relationships as they were.

-Stewart