obtaining totals on a field

[steve]

Very new to Access,

I've created a table and some queries/reports that work fine. What I'd like
to show are the total number of occurences for items on the report based on
the query on a specific field that is formatted as text, example of field
below:

"Field"
"J"
"B"
"H"

I have tried using the Group By and Count in the query with no luck. Does
anyone have any suggestions?

Steve

 

[StCyrM]

Hi Steve

If in fact you did place the field "Field" in your query twice, where the first
column is Group By and the second column is Count, then you should obtain the
proper results.

Maurice

 

[steve]

I'm not sure I understand your reply. Let me try to explain it better.
I have a table with a column heading named "TY" the data contained is the
following text (J, H, D, S, G, B, W) what I would like Access to do is count
the instances of a specific occurance of the text in a query and then output
that number into a report so the viewer of the report won't have to count
the numbers manually.

Is this something that can be done?

Thanks again

 

[Evi]

You could use a function called DCount for each letter.

I'll call your table TblData since you haven't given us many details. And
I'll call the field you have grouped your report by MachineID - a number
field. The formula in the text box which you would put in your report would
be


=DCount("TY","TblData","[TY]= 'J' AND [MachineID]=2003")

then you'd need a different formula for each letter.

I'd prefer the Count method.

The trick with Counting and Grouping is to make sure that you don't add more
details than you need.

So if your table had an Autonumber field and you put it in your query when
you tried to Count TY, the Autonumber field would be different each time and
the Count would actually be counting
How Many Times does [TY]= "Y" when the Autonumber = 7.

Of course the answer would always be 1!

So you need to add the bare minimum to the query. You can add the [TY] field
twice though, once to Group By and once to Count. Then you would see both
the letter and the count of letters.

If you wanted to see how many times J occurred when MachineID was 6 then you
would add MachineID (and also MachineName since the same name would appear
with its ID number.

But if some of those machineIDs had a different serial number next to them
then you would not add the serial number to the grid.

Do you get the idea?

You could make a DLookup on this query in another query, replacing TblData
in the example with the name of the query, and [TY] with [CountOfTy] if you
needed lots of details with the Count of the letter in its own field.

In a report, you could either use multiple text boxes with the DCount
formula in them, in the Report footer or if you want to count within groups
you could Group by [TY] and put
=Count([Ty]) in a text box in the group footer

Evi