435,560 Members | 2,989 Online
Need help? Post your question and get tips & solutions from a community of 435,560 IT Pros & Developers. It's quick & easy.

# difficulties creating a complex query

 P: n/a 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 Jan 3 '07 #1