compare array values

[terryvanduzee]

Hello
Is there a way to take an array, parse the values and then based on one value, add the corresponding values to a count for each record?

Ex:

Lets say a pizza place wants to track their toppings; which topping are chosen with each other topping.

Tomatoes, Peperoni, mushrooms, extra cheese etc.

I have a form where I would like to see a count of the number of times that mushrooms were selected when tomatoes were selected, when tomatoes were selected, how many times (for the entire database) how many times peperoni, extra cheese, ground beef etc were selected.

So in a combo box, the user would select tomatoes or mushrooms etc and the corresponding count for each other topping would populate text fields with the count of how many times each was choosen when the value in the combo box is selected.

So, lets say the user selects tomatoes.

Text boxes that represent mushrooms, ground beef, peperoni, extra cheese would display the number of times it was chosen as a topping when a person had ordered a pizza with tomatoes.

The array I was thinking of would be "peperoni, mushrooms, extra cheese, ground beef, tomatoes.

User selects Tomatoes from the combobox.

Record 1 has tomatoes, peperoni and extra cheese (no ground beef, mushrooms)
Record 2 has peperoni, tomatoes and ground beef
Record 3 has tomatoes, extra cheese.
... etc

The form would show under the heading of tomatoes 3, mushrooms 0, extra cheese 2, ground beef 1 etc

If Ground beef is selected from the combobox the form would display:
ground beef 1, tomatoes 1, extra cheese would display 0 etc.

The code would go through each value in the array and add 1 to the count where it is associated to each topping in the array. Then move on to the next record in the DB.

Any help would be appreciated.

Thank you
Terry

 

[The_Doc_Man]

Your language tells me you are thinking along flat-file lines, which is going to crimp your style. You must break out of the Excel mind-set. Access doesn't work on arrays. It works on lists. Linearize the lists.

tblOrder
InvNumber
Customer info
Time info
etc.

tblPizza
InvNumber (foreign key to invoice)
PizzaNumber (1 to n) one record for each pizza in the order
size
style
coupon

tbllToppingsUsed
InvNumber (for.key to invoice)
PizzaNumber (for.key to pizza)
ToppingNumber (1 to n)
SelectedTopping - a code for pepperoni, sausage, onion, ground beef, bell pepper, anchovy, shrooms, pineapple, spinach, diced grilled chicken, pate de fois gras, whatever else you put on pizzas.

Invoice to Pizza = one//many
Pizza to Toppings = one//many

On the form, first you take the customer info.
Then you ask for a pizza order.
Then you ask for toppings.
Then you ask whether a coupon is in play.
Then you ask if there is another pizza for the same order
Then you ask for the toppings.

etc.

OK, I see this as easy to define because this just cries out, LIST ME...

You JOIN the invoice table to the pizza table. You JOIN the result to the topping table. Now you use the report wizard on that query, doing a group-by for the invoice and a nested group-by for the pizza, listing the toppings.

When you want your monthly totals, you scan the TOPPINGS table and link BACKWARDS to the invoice table to get the date.