Question in Building an Expression

[Dmosk319]

Hi All-
So I am trying to build an expression based off of invoices that I create for my catering company. Every item that a person orders requires certain utensils (ex.fork, knife, serving utensil etc.). So here is an example of an order and the utensils that each item should contain...

• Sandwiches for 20 people- 20 Plates, 20 Knives, 2 Serving Tongs
• Salad for 19 People- 19 Plates, 19 Forks, 19 Knives, 1 Serving Tong
• Dessert for 17 People- 17 Plates, 17 Forks, 1 Serving Tong
• Paper Goods for 20 People

As you can see there can be an order for 20 people but they do not order each item for 20 people to save money. However, I still need to send enough utensils for 20 people. Now I need to create a formula that says based on this "Invoice #" the fields "Plates", "Forks", "Knives" are needed because of the items ordered but the quantity of the fields should be whatever the field "Paper Goods" says (in this case 20), and the field "serving tong" will be the sum of those 3 items

So in the end I want to run a report for each day, grouped on invoice and it simply return the values

• 20 Plates
• 20 Forks
• 20 Knives
• 4 Serving Tongs

Please help! And I thank anyone for their time and input

[ItsMe]

Sounds like a lot of variables that would be difficult to build a single formula for. You could add tables that supported your formula. For instance, a utensil table could have a relationship with an orders table. I order a sandwich and the sandwich does not have a relationship with the record for forks so, I do not get a fork. But what happens when I order two bowls of soup? Do I get two spoons?

I would use tables to support the formula and then use the GROUP BY operator to avoid multiple spoons for a single individual.