How can I return only one record per person?

Question

I have a database that includes superannuation contributions for staff, I have tried to run a query to determine the frequency of their contribution and when I run the query I get one entry for each time the staff member has contributed.

All I want is one entry per staff member that is asked for in the criteria. eg. weekly,fortnightly etc.

Answer 1

You will need to group by the staff member ID or name field. In the query builder you can do this by changing the query type to Crosstab and then back to Select. Then you will have an option "Total" for each field. For the ID field set this to "Group By". To count the fields use a field with option "Count".

I'm not sure exactly what your criteria should be, I would need to see how your table is set up and exactly what you want to know. You'll probably just want how many records there are for each person over some time period.

Alternately, you can go to the SQL editor and write a query involving a GROUP BY clause. Access's help should give examples.

Answer 2

The Query it sounds like you want should look like this:

SELECT Table1.StaffName, Count(Table1.StaffName) AS NumberOfEntries
FROM Table1
WHERE Table1.DateEntered Between #6/2/2009# And #6/15/2009#
GROUP BY Table1.StaffName

You can use any date range you want to and it will return only one row per person. One column will display said person's name, the other column will show a number representing how many records that person had within the selected date range.