General discussion

Locked

Access Report Question

By howardhenderson ·
Hi All,

I'm trying to figure out how to get a count of groups in a recordset in an Access 2000 report. For example, I've got a list of employees that work on different jobs with multiple income details on each job.

When I sort by 1) employee, 2) job, 3) details, how can I count how many different JOBS the employee worked on? I don't want a count of the number of detail records in each group.

No matter how I seem to set up various count() controls, it always seems to count the detail records themselves, not the # of jobs the fellow worked on this payroll.

I mainly need this in the context of an Access report. Should I achieve the count from SQL or is there a way to get the headers/footers to track a count?

I've dug through all my books and sample databases and am now wondering if I'm just missing something simple, or if that really is something of a trick...

Perhaps I need to use VBA?


Thanks for any ideas or suggestions,
Howard Henderson

This conversation is currently closed to new comments.

9 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access Report Question

by Mancca74 In reply to Access Report Question

I would just make a SQL query to do the count for me and insert this into the report. This would execute the query everytime the report is run and in such kepp up with the changes in your database.

I'm pretty sure it wouldn't be too much of a hassle doing it with VBA but I think SQL would do it easier for you since you could easily incorporate it into your report.

Whatever you decide to do, Good luck!

Collapse -

Access Report Question

by howardhenderson In reply to Access Report Question

The question was auto-closed by TechRepublic

Collapse -

Access Report Question

by comp_instructor In reply to Access Report Question

Howard,

The good news is that it isn't too difficult if you use a query trick.

1. Create a TOTALS query that consists of Employee and Job, each listed as "Group By". (this will effectively filter for only unique job titles for each employee)

2. Create a report based on the query that is grouped by Employee and in the group footer create a calculation that counts the "job" field.

Have fun!

Collapse -

Access Report Question

by howardhenderson In reply to Access Report Question

The question was auto-closed by TechRepublic

Collapse -

Access Report Question

by chainsawz In reply to Access Report Question

This might be an easier way. Make a report with the three fields: 1) employee 2) job and 3) detail. Create a grouping on the fields 'employee', and 'job' with the group header option set to "yes" for both. Drag the fields into their correct grouping header, and then change the data properties of the field 'job' to '=Count([Job])'. This provides a report with the correct count of jobs for the employee, while still displaying the description. I have a sample database available, if needed. Good luck!

Collapse -

Access Report Question

by howardhenderson In reply to Access Report Question

The question was auto-closed by TechRepublic

Collapse -

Access Report Question

by Glen_McLeod In reply to Access Report Question

The easiest way is to insert an unbound field in your report and set it's source to:

"count(ReportFieldName)"

Where "ReportFieldName" is the name of the field IN THE REPORT that you want to count. (This field name might be different that the name of the field in the database.) The report engine is smart enough to find the field in the report itself and will not go into the database to look for it.

Glen

Collapse -

Access Report Question

by howardhenderson In reply to Access Report Question

The question was auto-closed by TechRepublic

Collapse -

Access Report Question

by howardhenderson In reply to Access Report Question

This question was auto closed due to inactivity

Back to Software Forum
9 total posts (Page 1 of 1)  

Related Discussions

Related Forums