how to join 4 tables in microsoft access with one table as the base?

Question

I've look around and I haven't find anything in the Web pointing me to the right direction, so I'll try the knowledgeable stackoverflow people :)

I have 4 tables in Microsoft Access 2007 (Warehouse, Cars, TVs, Toys).

            __(many) Cars
           /
Warehouse 1---(many) TVs
           \__(many) Toys

The Warehouse table has a 1-to-many realtionship to Cars, TVs, Toys on the WarehouseNumber field I've been trying to make a query that would give me the sum Dollar Value (that is a field in Cars, TVs, and Toys tables). I am doing it with Joins, but maybe is the wrong way to go. I only know the basics about joins, never work with them before though.

First I am trying to get distictive records based on the Warehouse.WarehouseNumber field value like this:

SELECT Warehouse.[WarehouseNumber], Cars.[DollarValue], TVs.[DollarValue]
FROM (Warehouse INNER JOIN Cars
      ON Warehouse.[WarehouseNumber] = Cars.[WareHouseNumber])
INNER JOIN TVs ON TVs.[WarehouseNumber] = TVs.[WarehouseNumber];

But I get repetitive records like this (this is just dummy data):

WarehouseNumber | Cars.DollarValue | TVs.DollarValue
1111-1111       |        $8,000.00 |         $500.00 
1111-1111       |        $8,000.00 |         $800.00 
1111-1111       |                  |         $500.00 
1111-1111       |                  |         $800.00 
1111-3333       |    $1,000,000.00 |                 
1111-3333       |       $21,000.00 |

Is there a better way to do this? Thanks for the help in advance

UPDATE: I gave the answer to Remou for the simplicity in the query and being the first one, but all the examples so far didn't gave me any repetitive records. Thanks for the fast help guys, you are making access more bearable for me.

Answer 1

I suggest you use a union query for toys, cars and TVs. You may not need warehouse if all you need is a value.

SELECT DollarValue FROM Cars
UNION ALL
SELECT DollarValue FROM TVs
UNION ALL
SELECT DollarValue FROM Toys

If you need a total, you can get it like so:

SELECT Sum(DollarValue) FROM (
SELECT DollarValue FROM Cars
UNION ALL
SELECT DollarValue FROM TVs
UNION ALL
SELECT DollarValue FROM Toys ) A

You can also have:

SELECT Product, Sum(DollarValue) FROM (
SELECT "Cars" As Product, DollarValue FROM Cars
UNION ALL
SELECT "TVs" As Product, DollarValue FROM TVs
UNION ALL
SELECT "Toys" As Product, DollarValue FROM Toys ) A
GROUP BY Product

Answer 2

Is a union more appropriate for you

SELECT Warehouse.[WarehouseNumber], Cars.[DollarValue] AS DollarValue, 'Car' as Type
FROM Warehouse INNER JOIN Cars
      ON Warehouse.[WarehouseNumber] = Cars.[WareHouseNumber]
UNION ALL
SELECT Warehouse.[WarehouseNumber], TVs.[DollarValue] AS DollarValue, 'TV' as Type
FROM Warehouse INNER JOIN TVs
      ON Warehouse.[WarehouseNumber] = TVs.[WareHouseNumber]
--etc....

This will create a record set containing

WarehouseNumber, DollarValue, Type

Answer 3

You should use LEFT JOINS and SUM/GROUP BY

Something like

SELECT  Warehouse.[WarehouseNumber], 
     SUM(Cars.[DollarValue]) SumOfCars, 
     SUM(TVs.[DollarValue]) SumOfTvs
FROM    (Warehouse LEFT JOIN Cars      ON Warehouse.[WarehouseNumber] = Cars.[WareHouseNumber])
     LEFT  JOIN TVs ON TVs.[WarehouseNumber] = TVs.[WarehouseNumber]
GROUP BY Warehouse.[WarehouseNumber];

You can then expand this to your third case (Toys).

Answer 4

Try three queries one for cars, one for TVs and one for toys. Make sure they all have the same columns. You can join the three queries with union. You might add a 'static' column to every query so you know if the value is a car, a TV or a toy.

select warehouse[WarehouseNumber], 'Car', Cars.[DollarVallue]
FROM (Warehouse INNER JOIN Cars
      ON Warehouse.[WarehouseNumber] = Cars.[WareHouseNumber])
union 
select warehouse[WarehouseNumber], 'TV', TVs.[DollarVallue]
FROM (Warehouse INNER JOIN Cars
      ON Warehouse.[WarehouseNumber] = TVs.[WareHouseNumber])

Answer 5

This sounds like a bad design to me. There should be a product type table with data values toys, cars and TVs. Then the toys, cars and TVs tables should be merged into one table called products. And now you don't need the union query at all.

After all what happens when you decide to start carrying refrigerators, stereos and radar detectors? More tables? Not a good idea.