# Thread: Calculations in Queries (2000)

1. ## Calculations in Queries (2000)

Thanks is advance for any help you can give me. I am trying to create a query. This query will give me these answers
1. Item name this it does
2. Quantity (of the items sold) this it does.
3. Price, it will do this
4. ITEM CM (this is the selling price minus the cost of goods in the item), it does this.
5. Menu Mix (this is one that is giving me trouble. Menu Mix would be the quantity of each item sold, #2 above, divided by the quantity of ALL items sold.) How can I get the query to give me a total of #2? This will give me a % of each item compared to the entire inventory
6. Contribution to margin (haven't even attempted this one yet.) This one will be the total \$ amount of one item divided by the total \$ amount of all items. Again I don't know how to get the total of all prices times quantity.

I would attach the query if I knew how to attach just the query.

2. ## Re: Calculations in Queries (2000)

You need a subquery to handle this. The subquery gives you the total of all inventory items, something like:
(select sum(Quantity) from inventory)

3. ## Re: Calculations in Queries (2000)

Thank you for your help Mark. Now I have an additional question. In that sub query I have data that I don't want counted. For example I am trying to get a count of all items sold. Some of the items that are counted are not actually sold they are modifiers to the sold items but the get counted. Is there any way to remove those items? For example is there a way to say "anything with a value of 0 is not counted"?

I know this isn't much to go on. I was hoping that I have just missed something simple.

4. ## Re: Calculations in Queries (2000)

>>For example is there a way to say "anything with a value of 0 is not counted"?<<

Sure. If Value is a field, all you need is a WHERE clause that specifies Value<>0. This needs to be possible in both your mainquery and any subquery. If Value is calculated, then you need to insert the calculation instead of the word "Value".

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•