Report Combining Three Tables

[onwiththeshow11]

I have three tables that contain item data from three stores. The tables have some items that are in all three stores, some in two of the stores and some that are just in one store.

I want to create a report to show all three stores combined so the prices can be compared between each store. I have tried to set this up but all I seem to get are items that are in all three tables. Even if an item is in only one table I would like it to show up on the report anyway.

Thanks in advance for the help.

 

[PWise]

What is your data structure? It dose not seem normalized

 

[onwiththeshow11]

The tables are:

tblStore1
Store1ItemNumber
Store1ItemDesc
Store1Dept
Store1Category
Store1Price

tblStore2
Store2ItemNumber
Store2ItemDesc
Store2Dept
Store2Category
Store2Price

tblStore3
Store3ItemNumber
Store3ItemDesc
Store3Dept
Store3Category
Store3Price

The report needs to include all the item numbers but for the item numbers that are in common I would like to layout the report to show pricing side by side.

 

[dhookom]

I would start by putting all of this in a single table with a field that identifies the store. I think this is what pwise was hinting at with his response.

If you can't or won't normalize, you can use a union query to normalize the tables into a single recordset. I would then create a report of every unique item with a multi-column subreport to display the pricing. You could also create a crosstab from the normalized data to display prices for all stores.

Duane
Hook'D on Access
MS Access MVP

 

[PWise]

Try this

select items.*,
tblStore1.Store1Price ,
tblStore2.Store2Price ,
tblStore3.Store3Price
(
Select Store1ItemNumber item,Store1ItemDesc
From tblStore1
Union
Select Store2ItemNumber,Store2ItemDesc
From tblStore2
union
Select Store3ItemNumber,Store3ItemDesc
From tblStore3)items

left join tblStore3
on tblStore3.Store3ItemNumber = items.item

left join tblStore2
on tblStore2.Store2ItemNumber = items.item

left join tblStore1
on tblStore1.Store1ItemNumber = items.item

 

[PWise]

sorry sb

Try this

select items.*,
tblStore1.Store1Price ,
tblStore2.Store2Price ,
tblStore3.Store3Price
from
(
Select Store1ItemNumber item,Store1ItemDesc
From tblStore1
Union
Select Store2ItemNumber,Store2ItemDesc
From tblStore2
union
Select Store3ItemNumber,Store3ItemDesc
From tblStore3)items

left join tblStore3
on tblStore3.Store3ItemNumber = items.item

left join tblStore2
on tblStore2.Store2ItemNumber = items.item

left join tblStore1
on tblStore1.Store1ItemNumber = items.item

 

[onwiththeshow11]

I tried this and I get-

Syntax error (missing operator) in query expression 'tblStore3.Store3ItemNumber = items.item

left join tblStore2
on tblStore2.Store2Itemnumber = items.item

left join tblStore1
on tblStore1.Store1ItemNumber = items.ite'.

 

[PWise]

sorry forgot to use jet sql syntex
use
SELECT items.*, tblStore3.Store3Price, TblStore2.Store2Price, TblStore1.Store1Price
FROM (([Select Store1ItemNumber as item,Store1ItemDesc
From tblStore1
Union
Select Store2ItemNumber As item,Store2ItemDesc
From tblStore2
union
Select Store3ItemNumber as item,Store3ItemDesc
From tblStore3 ]. AS items LEFT JOIN tblStore3 ON items.item = tblStore3.Store3ItemNumber) LEFT JOIN TblStore2 ON items.item = TblStore2.Store2ItemNumber) LEFT JOIN TblStore1 ON items.item = TblStore1.Store1ItemNumber;