By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,560 Members | 2,989 Online
Bytes IT Community
+ Ask a Question
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
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

Jan 3 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Answered in microsoft.public.access
Jan 3 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.