Problem with my query

[Bokazoit]

Ok this is perhaps difficult to explain, but I'll do my best and hope You will ask if something seems hazy.

I have four 5 tables in my access database. 4 of the tables contains this:

Our business revenue monthly:
StoreID
month
Segmentation
revenue
Index

Our business revenue YTD
StoreID
Month
Segment
Revenue
Index

Partner business revenue month
StoreId
Month
Segment
Revenue
Index

Partner business revenue YTD
StoreID
Month
Segment
Revenue
Index

YTD = Year To Date revenue.

In our business we have a different Category-segmentation than our partner, so I have made another table (the fifth table) which has this info:

Our business category:
StoreID
Segment
Our business Sub-Category
Our business Category

The fifth table (Our business category) is linked/joined to the four remaining tables through the 'segment'. So I have made an inner join between 'Our business category' and the 4 remaining tables.

I have created a query in which I gather all data from the five tables. The idea with this query and report in the end is to gather our business revenues compared to our partners revenue in each category, as well as index' and marketshares based on the different tables.

Everything seemed fine until I compared my report with a 'handmade' report. The results were different in some of the categories which puzzled me for some time. I then realized that the reason were rather simple.

I have choosen that the join between my fifth table (see above) and the four remaining tables should be an inner join, but that created a simple problem. Whenever data from either our business or our partners business weren't available, no data is 'entered' into the query.

What I really need is an outer-join were all data from our revenue tables are brought into the query. But when I try to make an outer-join I get an error that says I need to make a seperate query that makes the join and then include that query into my excisting query.

Problem is that I don't know what to do now :-(

If you don't know what help I need, what can I post in here so that you can help?

In general I just need a way to join my fifth table to the other 4 tables without loosing data in my query

 

[PHV]

Have you tried to use the Our business category table as the master table and use 4 LEFT JOIN with the 4 revenue tables ?
SELECT ...
FROM ((([Our business category]
LEFT JOIN [Our business revenue monthly] ON ... )
LEFT JOIN [Our business revenue YTD] ON ... )
LEFT JOIN [Partner business revenue monthly] ON ... )
LEFT JOIN [Partner business revenue YTD] ON ...
WHERE ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Bokazoit]

No I haven't but how do I make it my master table?

If I open a new query and make new joins I get the same error

 

[PHV]

how do I make it my master table
By making the 4 four relation arrows starting from it.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Bokazoit]

I get the same error :-(

 

[PHV]

Can you please go in SQL view to copy the sql instruction and paste it here ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244