Adding a field count on my query

[wassimdaccache]

Hello

Could you please provide me a method to add on my query a field that count me the number of records. Example.

[waredrobe]------[counter]

A-------------------------1
V-------------------------2
T-------------------------3
*-------------------------4
7-------------------------5


Any Idea ???


Best regards,

WASSIM S DACCACHE

[Stewart Ross]

Hi Wassim. Counting rows is straightforward . The following exemplar counts the number of transaction rows for particular customers in particular departments:

SELECT DepartmentCode, CustomerID, Count(*) as Transactions
FROM TransactionTable
GROUP BY DepartmentCode, CustomerID
ORDER BY Department Code, CustomerID

You can easily create such queries using the Access query editor to do the work for you. In the query editor window select View, Totals then change Group By to Count, Sum or whatever for the fields you wish to summarise as necessary.

Please note that the key concept here is that of grouping the data by the specified fields; the counts (or sums, max values etc) apply to the grouping as a whole. It is for that reason I have shown Count(*) above, and not Count(somefield ) - all rows within the grouping are actually counted whether or not you specify a particular field.

If you need to have different grouping within the same row (a total for a group along with a count for a different grouping) then in general you need more than one query to do this.

-Stewart