Reports Using multiple queries

[sean98]

I am running a report using multiple queries. Each query pulls Sales & Margin data by customer. The reason for using multiple queries is the data in broken down by year as there is too much information to consolidate into one table in access. Thus I have data for 2001, 2002 and 2003 all in seperate tables. The report was created to compare the sales and margin results, by customer, from 2001 and 2002 against the 2003 data. The problem I am having is that if data does not exist for a customer in 2001 that customer does not show at all even though data from 2002 and 2003 exists and should be compared.

I am linking store numbers and customer numbers between tables as they are the common fields in each.

In struggling with this one I have changed links so that the 2003 table is linked to 2001 and 2002 but the 2001 and 2002 tables are not linked then I changed so they were linked.

I even thought that the left to right order of the sub queries within my report query might matter. That did not seem to work.

Can anyone provide me with some insight as to how I can get all customers to show even though data might not exist in the other two years?

Please & Thanks
Sean

 

[bat17]

in the query view, right click on the join between the 2003 and 2002 table, Select Join Properties and then select the option that says something like "Select all the records fromm 2003 table and only 2002 table where join is equal"
Repeat for 2002/2001.
This should give you Null fields for customers who did not exist in the earlier years.

Peter

 

[sean98]

I have tried using the joins properties options, thank you for the insight, but it keeps coming up with an ambiguous outer join error reference.

I have tried breaking down the queries, as per the error message, to force the performance of the first join and tried tying it into the second query with the same problems. I find that if I remove the store number join it runs but in order to identify a unique field we need to have both store and customer number as different stores can have the same customer number.

I think I am out of luck but thanks for the feedback.

Cheers,
Sean

 

[SydneyGeek]

Access doesn't like single queries that contain more than one outer join. I get around this problem by chaining queries together, each with one outer join, until I get the result I want.
eg: qry1 is 2001 table outer join 2002 table
qry2 is qry1 outer join 2003 table.

Hope that helps.

 

[sean98]

I am not sure what I was doing wrong but I assume it was how I was creating the joins. Every time I tried to set up outer joins within the query I would get the ambiguous error. I remembered that joins could also be created through relationships (Tools | Relationships).

This worked. I created a relationship between each field in my three queries, ensuring there was only one relationship per field for each table (ie table 2003 to 2002 to 2001 but not 2003 to 2001).

Thanks to everyone for helping out. You assistance pointed me in the right direction and I learned something new.

Cheers,
Sean