Union query joins tables, need to insert zero where data doesn't e

[Guest]

I have a database that tracks problems from vendors. It uses many tables from
different sources for each problem. I need to capture all of these into one
report, graph each problem seperatly by month and provide a 3-month average
for each problem and give an overall score. I join all my tables using a
union query to result in the following:

Date Supplier Problem Demerit

I then group the date by month in the crosstab query.

The issue I have is that not every table includes a score for every supplier
for every month and if they don't exist then their problem graph will look
poor. Thus I need to somehow run a query or code to ensure that every
supplier is listed for every problem for every month and they are given a
score of zero. Any ideas??

 

[Guest]

Sorry everyone, I was able to resolve my issue by running a regular query on
my union query and performing an iif(score=null,0,score) to populate the
fields

 

[John W. Vinson/MVP]

The issue I have is that not every table includes a score for every

supplier
for every month and if they don't exist then their problem graph will look
poor. Thus I need to somehow run a query or code to ensure that every
supplier is listed for every problem for every month and they are given a
score of zero. Any ideas??

Use a "Left Outer Join" to connect the supplier table to the scores.

John W. Vinson/MVP