Reporting problem with multiple tables?

[slenish]

Hello all,

Im having a problem getting a report to work with multiple tables.

First let me give a little detail in to how im doing this. I am using a form to pull the report. I have a report linked to 3 tables by a persons name. If you type in the persons name, along with a date range (from the form) then run the report it pulls the information from all 3 tables together and gives you the output. It actually works fine but my problem is when one of the tables does not have the persons name.

The main table is a list of all the names (along with other info). Now the second and third table contain errors (along with a date) that a person has made from the main table, but not everyone makes errors so both of the tables may not contain errors for the person, which is the problem. If all 3 tables have the persons name the report works fine, if i remove a name from one of the tables the report errors out. How can i make it still pull all of the info even if all the tables do not contain the persons name??

So an example would be if table 2 has errors for John Smith but table 3 does not, how to still pull the info?

Thanks in advance!!

[TheSmileyCoder]

If you join 2 tables in the query window, (by PersonID for example) and one of them does not contain a record with PersonID the result from the other table will not be shown.

Now, If you require that your results from the main table are always shown, the solution is to LEFT join the the 2 other tables. In the query window, you can click the join between the 2 tables to modify the join type (Standard join is both ways) to either a LEFT or a RIGHT join. A one sided join will select all the records from 1 table, and only the matching records from the other table.

[slenish]

Hi TSO,

I tried what you suggested with the left join but im not quite getting the results i would like. It works but only when the main table and the secondary table are in the same date range i get all the results but if only the secondary table is in the date range im trying to check i get nothing.

Is there a way to show the results of the secondary table with out the primary table having a match?

Im attaching an example db to show you what i have been working with.

Thanks for the help on this :D

[TheSmileyCoder]

I have modified the example DB you provided, to show what I meant.

A few unrelated observations:
You should join the table by the ID of the person(The Primary Key), not their name.

Why do you join on the date? Are errors only relevant if they have happened on the same date?

[slenish]

Hi TSO,

Appreciate you taking the time to make some changes. Its close to what i was trying to accomplish.

The reason i was trying to join on the date was because i wasn't sure if that might help when i pull for a date range.

As of right now if I put in the persons name and a date range say Feb 1 - Feb 28 then I want it to pull all the errors for that date range for the person, but instead its pulling everything. If you look at table 3 the date is set for January. So what i want it to do is if i pull Jan 1 - 31 then it shows me only the table 3 records, if i pull Feb 1 - 28 then it will only show me the table 2 records or if both tables have records in the same date range then show me all the records. Is it possible to do this? I keep playing around with the left and right join trying all kinds of combinations but nothing seems to work.

I could set up the ID as the primary key but would that make a difference for what im trying to do? I'm guessing not...

====Update====
Hi TSO,

Ok i have been playing around with this some more and i figured out how to make it work the way i want. I put in a sub report and linked that to table 3 and it works great. Now the problem im having is trying to get the main report Name box to contain the name of the person...Im attaching my update for you to take a look at.

Hope to hear from you soon :D