giving totals in a report

[aman909]

Hello,

I have a stock control database system. Im trying to produce a report of the stock levels we have. At the end of the report i would like to be able to give a final total of stock.

By this i mean, adding up all the stock we each item and giving a overall stock for it. For a example we have t-shirts in stock in many sizes as well as in a number of colours.

So my table for t-shirts is like this:
Colour Size S Size M Size L Total

I have used a query which gives me a total for each colour t-shirt.

What i need to be able to do is add up all the totals for all the colours and display a grand total in the report.

Can you please sugguest the best way to be able to do this.

 

[lark_tilly]

Are t-shirts the only item you have or do you have others like hats that
will be in the same report but need their own grand total?

If the report is just for t-shirts then add a textbox to the report footer
and set the control source to =Sum(field showing color qty).

For example, if the textbox in the detail section which contains the total for the color has Total as it's Control Source, then the textbox in the report footer has =Sum(Total) as it's Control Source.

 

[boblarson]

Your data isn't normalized and as such makes it much more difficult to pull data out in a meaningful way. You should not have repeating fields and you should really have a separate table that stores the size information. For example:

Products Table
ProductID - autonumber (primary key)
ProductName - text
...more info can be here if one-one relationship

Sizes Table
SizeID - autonumber (primary key)
SizeDescription - Text

ProductsSizes Table
ProdSizeID - autonumber (primary key)
ProductID - Long Integer (foreign key from Products table)
SizeID - Long Integer (foreign key from Sizes table)
Qty - Integer (quantity you have of that size)