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" "Supermarke t1" "XProduct" 2,20 $ 5
"101" "Supermarke t2" "YProduct" 2,05 $ 2
"102" "Supermarke t1" "ZProduct" 2,10 $ 1
"103" "Supermarke t2" "XProduct" 2,05 $ 3
"104" "Supermarke t1" "YProduct" 1,95 $ 4
"105" "Supermarke t2" "ZProduct" 2,05 $ 6
"100" "Supermarke t1" "XProduct" 1,90 $ 7
"103" "Supermarke t2" "YProduct" 1,90 $ 8
"105" "Supermarke t1" "ZProduct" 2,05 $ 1
"102" "Supermarke t2" "XProduct" 2,00 $ 2
"101" "Supermarke t1" "YProduct" 2,20 $ 6
"104" "Supermarke t2" "ZProduct" 2,20 $ 3
"100" "Supermarke t1" "XProduct" 1,95 $ 9
"102" "Supermarke t2" "YProduct" 2,05 $ 4
"105" "Supermarke t1" "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" "Supermarke t1" "XProduct" 2,20 $ 5 "true"
"100" "Supermarke t1" "ZProduct" 2,05 $ 5 "false"
"100" "Supermarke t1" "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]

Answered in microsoft.publi c.access