"Total" Field in A Query/Report

[strikermax]

I am doing a company inventory database, and for one of my reports (based on a query), I have entered in all the monitors and grouped the report by manuacturer. For example, Acer is listed first, then Daewoo and so on. I would like to either enter a command in my query (or maybe it should go in the report) that will count the number of records for that manufacturer and say, for example, Acer Monitor Total = 7, or Daewoo Monitor Total = 14, but display that data at the far right hand side of that section.

To explain this, I have included some dummy data below:

MF. Store Branch Model Size Serial Number
----------------------------------------------------
Acer
West Service 24A 17" M3TP6150046
East Sales 24A 17" M3TP6150047

Acer Monitor Total = 2

Daewoo
West Service 64T 19" GC94750601
East Sales 64T 19" GC94750602
East Service 64T 19" GC94750603
North Sales 64T 19" GC94750604
South Service 64T 19" GC94750605

Daewoo Monitor Total = 5

I wasn't sure if I could do this in a query since query data is viewed in datasheet view, and the query will group this data by monitor manufacturer providing all monitors are from the same store and branch, but if not, it displays a seperate record for each monitor. So instead of getting 2 monitors for Acer, the total column reads 1 and 1, since they are from different branches. Things are even worse when there are more stores and branches that have that make of monitor.

Any help on this one would be appreciated!! Thanx!!!

 

[bosully]

How you format the results within Access, I don't really know but to get the answer you want you need to do an embedded Select query like this-

Select MF, Store, Branch, Model, Size, [Serial Number],
(Select Count(MF) from TableName TN1 Where TN1.MF = TN.MF)
From TableName TN
Order by MF

The trick is in using aliases for the table name so that the embedded query only counts where the MF field matches the value in the outer query and not the whole table.

Hope this helps,

Brendan

 

[linzig]

It is very easy to do this using the report wizard. Set up the query with the fields that you require all grouped, then add the field manufacturer ID but count this field.

Next open the report wizard, select the query you have just generated, select all the fields you would like on your report included the Manufacturer ID, next group by Manufacturer ID, next click on summary options, click sum Manufacturer ID, then ok, then next until finish as normal.

Run the report, then in design view delete the information you don't need.

 

[rajeessh]

linzing may have it right.
Pl try his step
I reports u can use group wise totals i think

 

[strikermax]

I've discovered that all I have to do is add a text box to the group footer. In the control source enter =Count(*). This works so that it displays Total=____. The only thing is that since the label for the box says "Total", I cannot get it to dynamically change with the name of the group, but I guess that's OK for the bigwigs. "Three o'clock is always too late or too early for anything you want to do."

 

[jebry]

Hi!

To get the group name after the word total, just use a textbox with the group name in it. You will need to adjust the size and positioning of the box and label to get it to look right, but you can get it to look pretty good.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[strikermax]

Thanks jebry!!! Works perfectly. Now the bigwigs will be really happy. Hmmmmm.... Maybe now I'll get that raise I've been waiting for!! "Three o'clock is always too late or too early for anything you want to do."