Calculating Totals 1

[fsupport1600]

I am trying to figure out an expression to calculate the following:

I have a product table, and each product is assigned a ProductTag. Example "S1".

I then have another table called, Funiture Selection. This tbale is used to select a room number, then select the ProductTag which represent the type of funiture selected and then input the quanity for that ProductTag.

ID Room Number ProductTag Quantity
1 D123 S1 10
2 D124 S2 5
3 D124 S1 6


I would like to generate a report that will calculate the total based on quantity for each of the ProductTag.

So in the above example ProductTag "S1" would total 16. I have various ProductTags and need a way to total the quantities up for each of them.

Thank You

 

[dhookom]

I think you only need to create a totals query to base your report.

Select ProductTag, Sum(Quantity) as SumQty
FROM FurnitureSelection
GROUP BY ProductTag;

Duane
Hook'D on Access
MS Access MVP

 

[fsupport1600]

Sorry for my lack of knowledge with Access but where would I enter that code.

When I go to create query I have the following options to select from:

Field:
Table:
Total:
Sort:
Show:
Criteria
or:

 

[dhookom]

[tt]
Field: ProductTag SumQty:Quantity
Table: FurnitureSelection FurnitureSelection
Total: Group By Sum
Sort: Ascending
Show: X X
Criteria
or: [/tt]

Duane
Hook'D on Access
MS Access MVP

 

[fsupport1600]

Duane,

Greatly appreciate the clarification, that was exactly what I was looking for. Along the same line, can you assist with another one I am working on. This one is calculating a total cost for each room.

I was able to generate a total cost of each product for each room by calulating Quantity*Unit Price.

The report shows as follows:

Room Number
ProductTag UnitPrice Quantity Total Cost


Room Number
ProductTag UnitPrice Quantity Total Cost

I would like to calculate the total cost for all products selected for each of the rooms and have it based on the room number.

Thank You

 

[dhookom]

Display the Room Number Group Footer section and add a text box with a control source of:

  =Sum(UnitPrice * Quantity)

Duane
Hook'D on Access
MS Access MVP