I'm usually not bad with queries, but this one is giving me a headache.
It's producing results but takes a while to do it.
It's like a cost comparison query, the following is an example:
I have the following stores:
Store A
Store B
Store C
And each store carries similar products:
Product A
Product B
Product C
And each product comes in different sizes:
Small
Medium
large
I have a table for each store with the following fields:
ID, StoreName, ProductName, SmallPrice, MediumPrice, LargePrice
What I want to do is design a query that tells me the cheapest price for each size of each product and what store is offering that price.
Hope that makes sense!
Oh, and one other thing, the same products have different names.
To get around this I came up with a master table with the fields:
ID, MasterProductName, StoreAProductNames, StoreBProductNames, StoreCProductNames
I then have to link the product in the stores table, to the corresponding field in the master table and use the ID field to compare against the other prodcuts.
It's producing results but takes a while to do it.
It's like a cost comparison query, the following is an example:
I have the following stores:
Store A
Store B
Store C
And each store carries similar products:
Product A
Product B
Product C
And each product comes in different sizes:
Small
Medium
large
I have a table for each store with the following fields:
ID, StoreName, ProductName, SmallPrice, MediumPrice, LargePrice
What I want to do is design a query that tells me the cheapest price for each size of each product and what store is offering that price.
Hope that makes sense!
Oh, and one other thing, the same products have different names.
To get around this I came up with a master table with the fields:
ID, MasterProductName, StoreAProductNames, StoreBProductNames, StoreCProductNames
I then have to link the product in the stores table, to the corresponding field in the master table and use the ID field to compare against the other prodcuts.