MS Access 2003 Query/Report

[gazsharpe101]

Hi, I have a problem which should have a relatively simple solution, I just cannot find it.

Please accept my apologies if this is in the incorrect forum, I wasn't sure whether to place it in the 'Reports' forum but I figured that my question was to do with the results of a query so I put it in here.

I have a select query which gives me the following results:

Company_Name Staff_FirstName Staff_Surname Staff_DOB
ABC Ltd Joanne Robinson 08/12/1950
DEF Ltd Kevin Smith 25/04/1963
ABC Ltd Paul Jones 08/06/1947
DEF Ltd Lisa White 12/09/1965
.... .... .... ....
etc etc etc etc

Apologies for the layout of the above query, it doesn't look like this when I am editing my post

The field 'Company_Name' comes from the table 'tblCompany' and the fields 'Staff_FirstName', Staff_Surname' and Staff_DOB' come from the table 'tblStaff'.

I want to get a report for each company showing the company name and then underneath the details for each staff member for that particular company.

In the report footer I want to show number of companies and number of staff members overall.

How am I able to do this? I am using the count function on the report to count staff numbers, but I cannot get it to count the unique company name because of the data redundancy. Please remember that the 'CompanyID' field is hidden on the query, but this has no bearing on the data duplication.

I have thought about creating a query based on the above query and just select the 'CompanyID' and 'Company_Name' fields and then use the SELECT DISTINCT function and use the 'Count' feature on my report. This, however, would involve me having to have multiple Record Sources for my main report. I have also thought about creating a subreport on my main report and putting the RecordSource for the subreport as the new query and then somehow linking the two reports.

Thanks for any help it is much appreciated.
Gareth.

 

[neileg]

You use Sorting and Grouping in the Wizard to create a Company Header and list the detail records below.

You need to add a hidden control in the Company header. Set the control source to =1, set the RunningSum to Over Group and Visible to No. Then in your report footer, add another control and set its control source to the name of the hidden control in the Company header.

This is covered in Access help.