Making queries from multiple tables

[amywilson95]

I am trying to create a query using multiple tables.



I have a master data table, which includes the codes of various locations.
I have a few other tables which contain info for rent, rates etc

I want to create a query that shows the sum of the rent, rates etc for each location code.
It works with one table, but then when I add more tables the figures don't correspond with the original data?

I have a relationship between the master data code and the codes in the other tables.

Am I missing something? Can anyone assist?

[ranman256]

You may want a UNION query. You query many different tables, ie:
select clientID, amt from tRent
union
select clientID, rate from tRates

[orange]

Can you show us a jpg of your relationships window? Please expand all tables to show all fields before doing the jpg.

[June7]

Not a UNION query.

Build aggregate (GROUP BY) Totals query for each table that needs to be summed. Then join those queries to the master locations table.

Or build report/subreports arrangement.

[amywilson95]

I have tried doing it this way - seems to be bringing the results I want.

BUT... it is only showing results for less than a quarter of the locations in my master data, any idea why??

Thanks

Not a UNION query.

Build aggregate (GROUP BY) Totals query for each table that needs to be summed. Then join those queries to the master locations table.

Or build report/subreports arrangement.

[orange]

Post the sql of your query.

[amywilson95]

Here is the SQL...

SELECT [Master Data].Code, Sum([Rent Q].[SumOfRent Amount]) AS [SumOfSumOfRent Amount], Sum([Rates Q].[SumOfRates Amount]) AS [SumOfSumOfRates Amount], Sum([FM Q].[SumOfFM Amount]) AS [SumOfSumOfFM Amount], Sum([Fixed Occupancy Q].[SumOfFixed Occupancy Amount]) AS [SumOfSumOfFixed Occupancy Amount], Sum([Variable Maintenance Q].[SumOfVariable Maintenance - Net Sales Value]) AS [SumOfSumOfVariable Maintenance - Net Sales Value]
FROM (((([Master Data] INNER JOIN [Fixed Occupancy Q] ON [Master Data].Code = [Fixed Occupancy Q].Code) INNER JOIN [FM Q] ON [Master Data].Code = [FM Q].Code) INNER JOIN [Rates Q] ON [Master Data].Code = [Rates Q].Code) INNER JOIN [Rent Q] ON [Master Data].Code = [Rent Q].Code) INNER JOIN [Variable Maintenance Q] ON [Master Data].Code = [Variable Maintenance Q].Code
GROUP BY [Master Data].Code;

Post the sql of your query.

[June7]

Don't use INNER JOIN, use OUTER - LEFT or RIGHT, probably RIGHT in this case - "All records from [Master Data] and only those from ..."

[amywilson95]

Sorry I'm confused, do I just replace everything that says "INNER JOIN" with "OUTER - RIGHT" ??
SQL view is not something I've used before.

Don't use INNER JOIN, use OUTER - LEFT or RIGHT, probably RIGHT in this case - "All records from [Master Data] and only those from ..."

[June7]

Use the query designer to help you construct the statement. Double click on the line that is linking between tables to open the Join Properties dialog.

[orange]

Try some experimenting.
Using Access, take 2 of your tables that are related.
Create a query using an Inner Join, and see the result.
Change the Inner Join to Left Join and see the query results.

You can see specifics at http://www.w3schools.com/sql/sql_join_inner.asp
.

[amywilson95]

Problem solved, thanks for your help everyone