Hi,

We are attempting to creat a report which will display information stored in several (4) different tables. The database tracks construction projects, both planned and currently underway. Each job is identified with a unique code and that code is part of each of the tables.

Basically what we need is a report to show various fields from each table on the one report. Should be simple enough. But we can't get it to work properly. Here's what we did.

We created a query which pulled data from each of the necessary tables and used the Job Code as the relation. It kind of works. but the problem is that if one of the fields in one of the tables is blank, the query won't pull any info for that job from any other table, thus eliminating that job entirely from the report. For example, the anticipated completion date is in one table and is one of the fields we want to display on the report. But if it's empty, which is often the case, the query won't display any of the other data for that job from any other table. It is possible for a planned construction project to have a project code, name, budget, etc. without a completion date.

So what we need is for all records to show up on the report regardless if one of the fields is empty.

Hope this makes sense, and hopefully someone can help out with an easy answer.


Thanks,
Shane

sounds like you have to look at the "join" condition in your query. If you are in query design mode and you relate 2 tables together and then right click on the joining line you will see 3 choices. Hard to say exactly which one you need to change.

Do you really mean that the field in one of the tables is empty, or is it a case that there isn't a row in one of the tables corresponding to the specific Job Code? The latter is far more likely to be the issue...

Presumably one of your tables contains a row for each Job Code. In the query builder, one by one right-click on the relationship lines joining that table to the other three tables and choose the option that says "Include ALL records from 'table1' and only those records from 'table2' where the joined fields are equal." (where table1 is the table that contains one for for each job code, and table2 is the other table)

Perfect guys! Thanks for you help.

I figured there was a simple solution and there was!

As always, I apprecite the assistance.

Cheers,

Shane