MS Access 2003 Query/Report Problem

[gazsharpe101]

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

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.

[Rabbit]

The font used is not equal width for every character, surround blocks of text if you want to use a font that will allow you to line things up. As long as you get the spacing correct that is.

As for your problem. Use the DCount function.