find the most frequent value

[Spira]

hi, im making a computer repair system and would like to know how to extract the most frequent customer ID (integer) using a query. thanks

 

[Pat Hartman]

SELECT TOP 1 UserID, Count(*) AS CallCount
FROM tblRepair
GROUP BY UserID
ORDER BY Count(*) DESC;

 

[Spira]

SELECT TOP 1 UserID, Count(*) AS CallCount
FROM tblRepair
GROUP BY UserID
ORDER BY Count(*) DESC;

where do i put this?
is there any way of putting this in a QBE? ( i only know how to use this)
thanks

 

[Pat Hartman]

Switch to SQL view.
Change UserID to your field name (both places)
Change tblRepair to your table name

Once you have changed the two names, you can switch to QBE view if you prefer.

 

[Spira]

Switch to SQL view.
Change UserID to your field name (both places)
Change tblRepair to your table name

Once you have changed the two names, you can switch to QBE view if you prefer.

thanks for your help, it now works!!