difficulties creating a complex query

[bau]

Hi everybody,

I am using MS Access 2003 to prepare data for a scientific study. I
have a table which look like that:

"Customer" "Store" "Product" "Price"
"week"
"100" "Supermarket1" "XProduct" 2,20 $ 5
"101" "Supermarket2" "YProduct" 2,05 $ 2
"102" "Supermarket1" "ZProduct" 2,10 $ 1
"103" "Supermarket2" "XProduct" 2,05 $ 3
"104" "Supermarket1" "YProduct" 1,95 $ 4
"105" "Supermarket2" "ZProduct" 2,05 $ 6
"100" "Supermarket1" "XProduct" 1,90 $ 7
"103" "Supermarket2" "YProduct" 1,90 $ 8
"105" "Supermarket1" "ZProduct" 2,05 $ 1
"102" "Supermarket2" "XProduct" 2,00 $ 2
"101" "Supermarket1" "YProduct" 2,20 $ 6
"104" "Supermarket2" "ZProduct" 2,20 $ 3
"100" "Supermarket1" "XProduct" 1,95 $ 9
"102" "Supermarket2" "YProduct" 2,05 $ 4
"105" "Supermarket1" "ZProduct" 2,00 $ 5

The table shows the product a consumer made in a specific supermarket
in a specific week at a certain price.

I need to create a query which displays the customers choice as above
including all other products the supermarket sold within a 5 week time
period (and excluding the product he/she bought) with their average
prices. Thus the output would be all the choices the consumer faced
when buying the product.

To illustrate what I would like to create, here an example output using
the first row from teh above table.

"Customer" "Store" "Product" "Price" "week" "choosen"
"100" "Supermarket1" "XProduct" 2,20 $ 5 "true"
"100" "Supermarket1" "ZProduct" 2,05 $ 5 "false"
"100" "Supermarket1" "YProduct" 2,08 $ 5 "false"

So consumer with id 100 chose "Xproduct" and also saw most likely
ZProduct and YProduct, but did not choose them. I hope that example
clarifies my goal.


I would like to do this in pure SQL if possible. Any help would be very
much appreciated!

Regards,

Ray

 

[Allen Browne]

You can do this in queries, but it will take a few steps.

First query will be to get a listing of the products actually available at
any store in any week, with the average price. This kind of thing:
SELECT Store, Product, Week,
(SELECT Avg(Price) AS AvgPrice
FROM Table1 AS Dupe
WHERE (Dupe.Store = Table1.Store)
AND (Dupe.Product = Table1.Product)
AND (Dupe.Week Between Table1.Week - 2
And Table1.Week + 2)) AS AvgPrice
FROM Table1
GROUP BY Store, Product, Week;

It may be that customer 100 bought the same product at the same store more
than once in the week, so the next query will be to deduplicate that and get
the average price they actually paid. Example:
SELECT Customer, Store, Product,
Avg(Price) AS AvgPricePaid
FROM Table1
GROUP BY Customer, Store, Product;

Now you can create another query using those 2 as input "tables."
In the upper pane of query design, join them on Store.
In Criteria under Query2.Product, you want:
<> Query1.Product
You can now get results showing a record for every alternative product
available at that store with its average price, compared to the average
price paid by that customer at that week for the product they actually
bought.

From there, you can generate a report with the groupings you want.

I'm guessing that you provided just a concrete example (always a good way to
ask a question), and that your actual needs will differ somewhat. You may
also need to know how to use a subquery. For an introduction, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[bau]

Thank you Allen!!!

The concept of subqueries and duplicating tables within them was new to
me, but the solution to the task.