I want to match those records from tblStorePrice and tblCompPrice that have the same FuelType, RecDate, and then match on the tblStoreComp. There are many Comp_No's to Store_No's. I would like to get those that match and then also those that don't watch. So I want to see all the records in tblCompPrice and tblStorePrice, but see those that match as one record.
Any ideas on how to do in a query in ACCESS, or can also be done then in a data access page. Thanks for any and all help!!!
A Store_No can have many Comp_No's. So a Store_No will have multiple records in this table. The Comp_No's are all unique, so don't duplicate among Store_No's.
In english, a Store can have multiple Competitors, that are each unique (with Comp_No) and in the tblCompPrice.
Originally posted by dynamictiger
A question on your structure. What is the relationship between tblStorePrice(FuelType, RecDate, FuelPrice, Store_No)
tblCompPrice(FuelType, RecDate, FuelPrice, Comp_No)
Looking at the structures they contain the same data so there is a relationship.
select S.Store_No, S.FuelType, S.RecDate, S.FuelPrice
, C.Comp_No, C.FuelType, C.RecDate, C.FuelPrice
from tblStorePrice S
join tblStoreComp SC
on S.Store_No = SC.Store_No
join tblCompPrice C
on SC.Comp_No = C.Comp_No
and S.FuelType = C.FuelType
and S.RecDate = C.RecDate