Counting and displaying all different values (1 Viewer)

[bbwolff]

As part of the report I'd like to have a table that would list all different values in column in column A and the number of times they appear in the table in column B.

I have a bit of a hard time how to populate the table the easiest way.
I have all the values in a separate source table, so first coloumn is easier.
but do i have to write the dcount statement for each row for second coloumn?
any easier way to do it? Could I display results of query as subquery in report.

And a subquestion. If you have a bunch of textboxes with increasing names like NameBox1... for for/next loops in reports... is there an easy way to assign those names?

 

[pr2-eugin]

Have you looked at TOTALS Query?

SELECT columnA, Count(columnA) As TotalCount
FROM yourTable
GROUP BY columnA;

Regarding your sub-question. Yes you can loop through the controls. A bit more info would be helpful to help you. This should be a start: http://www.access-programmers.co.uk/forums/showthread.php?t=165872

 

[bbwolff]

That works, but what is the best way to display it as a part of report?

the other thing probably won't work as i'm getting run time error 2136

For i = 1 To 18
Controls("text" & i).SetFocus
Controls("text" & i).Name = "Myname" & i

Next

 

[pr2-eugin]

That works, but what is the best way to display it as a part of report?

Explain... I am not able to understand.

the other thing probably won't work as i'm getting run time error 2136

Why are you trying to rename the control? You cannot do that.

 

[ConnorGiles]

Hi bbwolf,

Sorry to butt in Paul.

This can easily be achieved via a cross tab query - On the wizard it should ask you if you would like totals for each column.

P.S.

Make a report sourcing this CrossTab Query for your own personal design.

 

[bbwolff]

I think a lot of my problems come from trying to make one big report that would have all the data i want and just be able to send it all together on a click
I'll make a separate report for each thing and then go on to adding things together now.

New problem
My query

SELECT OpTbl.bpart, Count(OpTbl.bpart) AS TotalCount, Avg(OpTbl.OpTime) AS AvgOpTime
FROM OpTbl
GROUP BY OpTbl.bpart;

how can i add fourth column that would count a number of times a specific value would appear in column nr 4c (opType)

In particular, how many times a value 'local' appears in column AnType for each value in column bPart.
Hope I make some sense.

 

[bbwolff]

maybe some additional data

table structure

bPart - string - 25 possibilities here, want the query on how many times they appear
opTime- duration, number (minutes)
AnType - string, can be local or general (other options, but this will do for now)

query
bpart - how many times it is used ---- average time ----- nr times it's local ---- nr times it's general

 

[bbwolff]

Sorry for the flood of replies.

Using this

SELECT opTbl.bPart, opTbl.AnType, Avg(opTbl.OpTime) AS PovprečjeodOpTime, Count(opTbl.ID) AS ŠtetjeodID
FROM opTbl
GROUP BY opTbl.bPart, opTbl.AnType;

I get the left result

What do i have todo to get the right composition?

 

[pr2-eugin]

As Connor suggested, use a CrossTab Query.

 

[bbwolff]

TRANSFORM Count(opTbl.[ID]) AS ŠtetjeodID
SELECT opTbl.[AnType], Count(opTbl.[ID]) AS [Vsota ID]
FROM opTbl
GROUP BY opTbl.[AnType]
PIVOT opTbl.[bPart];

It works... I'll see if I can do more complex stuff with this.