Count Query

[paalschau]

I have a query that shows this:

User customer
xx1 Hans Hansen
xx1 Ole Olsen
xx1 Kari Karlsen
xx2 Nils Nilsen
xx2 Per Persen

I wont to use a query to count the total for user and still use the Group By function.
Like this:

User customer counts
xx1 Hans Hansen 3
xx1 Ole Olsen 3
xx1 Kari Karlsen 3
xx2 Nils Nilsen 2
xx2 Per Persen 2

Is it possible to not use SQL…

I don't want it in a report because I need to use the info for further querying.

Thanx...

 

[Jon K]

You can use the DCount() function for counting.

Assuming you have saved your original query as qryOne, you can create a new query based on qryOne (type in the SQL View of the new query):-

SELECT User, Customer, DCount("*","qryOne","User= '" & User & "'") AS Counts
FROM qryOne;

 

[paalschau]

Thanks for your input and help!
This works great!

 

[paalschau]

I have tried this in a query - but the table is too big and the job takes ours…

Could anyone help me with a function – that offer the same result?

Tanks in advance

 

[The_Doc_Man]

A totals query might do what you want.

Start with a new query. Click the Sigma (Greek letter) on the toolbar. This changes the SELECT query to a Totals query. (It is still also technically a SELECT query.) A new row becomes visible in the query grid.

OK, select a field. In this new row you can choose whether this will be a GroupBy or Sum or Count (or several other options). Or a criteria only.

The DCount works but as you said, is rather slow. That is because to work it has to open a separate recordset with all the overhead you might imagine of having two recordsets open at the same time.

 

[paalschau]

But is the DCount formula also slow if it’s in a module/VBA.
Any tips on how making it ?

 

[Rich]

Why do you have to use a query to get a count, why not do it at form or report level?