help needed with possible query issue

  • Thread starter Thread starter Associates
  • Start date Start date
A

Associates

Hi,

I was wondering if i could get some help or advises here. I have a database
designed to work out the cheaper cost for consumers in grocery shopping.
There are 5 supermarkets that i am focusing on and may be more than 100 items
available across the supermarkets.

What information i have at hand is the quantity of the selected items.

so here is something i need to figure out how to store the following numbers

1 2 3 4 5
================================
Item1 $4 $2 $3 $4.5 $3.5
Item2 $2 $2 $2.5 $1.5 $1.5
Item3 $5 $6 $5.5 $6 $5
Item4 $3 $2.5 $3.5 $3 $3.5
Item5 $3.5 $5 $4.5 $5 $3


To find out the minimum cost for grocery shopping, i need to generate a
combination of (let's say 2 supermarkets) 2 out of 5 supermarkets. So i am
talking about
1,2
1,3
1,4
1,5
2,3
2,4
2,5
3,4
3,5
4,5

Say, we have 5 items selected with specified quantities

2,5
=====
item1 $2
item2 $1.5
item3 $5
item4 $2.5
item5 $3

Because there are lots of numbers floating around that needs remembering for
analysis use.
My question is whether i could use query to generate these numbers and
somehow store it somewhere (perhaps, in multiarray)

Any help would be greatly appreciated.

Thank you for your help and tips in advance
 
Hi,

First, you need to set up tables to store the information. You will
need at least four. Here are some suggestions as a starting point:

tblStores
StoreID
StoreName
(Other store-specific information)

tblItems
ItemID
ItemName
ItemPrice
(Other item-specific information)

tblStoreItemPrices
StoreID
ItemID
Price
(Other information?)

tblShoppingTrip
ItemID
Quantity
(Other information)

Once you have the first three tables filled, you can fill in the
shopping trip. Then you can set up some summary queries that calculate the
cost of shopping for all items at all of the stores. Or maybe what happens
if you buy the least expensive item from certain stores? In which case do
you want to include the added travel cost? These things will impact what
tables you have and what is in them.

Once you get it set up and have played around; should you need further
help, post again.

Clifford Bass
 
Back
Top