Count records

[Irv1010]

Hi all

I have 2 fields [StaffName] & [ClientJobTitle] from 2 different tables. I
would like to run a report that will show the staff name (once only) along
with number of records where [ClientJobTitle] is null.

John Smith - 29
Karen Jones - 62
etc.

I can't enter the staff names separately as they are continually changing.
Anyone got any ideas?

 

[KARL DEWEY]

Your explaination is lacking so I will assume the following --
The two table both have the two fields. You want a total count of records
with the ClientJobTitle as null for each StaffName.
First use an union query named Staff_Client--
SELECT [StaffName], [ClientJobTitle]
FROM Table1
UNION ALL SELECT [StaffName], [ClientJobTitle]
FROM Table2;

Then a totals query --
SELECT [StaffName], Sum(IIF([ClientJobTitle] Is Null, 1,0)) AS CountOfNull
FROM Staff_Client
GROUP BY [StaffName];

 

[Irv1010]

Thanks for getting back to me Karl

The [StaffName] field comes from a table called PersonalDetails which
details a client record and the staff member allocated to work with that
client. The [ClientJobTitle] field comes from another table called
JobsFilled which the staff member would complete when the client gets a job.

What I need to do is run a query/report that shows how many clients each
staff member has got into work. I can't just have a list of every record
where [ClientJobTitle] is null, as it would be far too long. Also I can't
put each staff member name into an unbound field in the report header as the
staff members constantly change. Any advice would be appreciated.

Your explaination is lacking so I will assume the following --
The two table both have the two fields. You want a total count of records
with the ClientJobTitle as null for each StaffName.
First use an union query named Staff_Client--
SELECT [StaffName], [ClientJobTitle]
FROM Table1
UNION ALL SELECT [StaffName], [ClientJobTitle]
FROM Table2;

Then a totals query --
SELECT [StaffName], Sum(IIF([ClientJobTitle] Is Null, 1,0)) AS CountOfNull
FROM Staff_Client
GROUP BY [StaffName];

--
KARL DEWEY
Build a little - Test a little

Hi all

I have 2 fields [StaffName] & [ClientJobTitle] from 2 different tables. I
would like to run a report that will show the staff name (once only) along
with number of records where [ClientJobTitle] is null.

John Smith - 29
Karen Jones - 62
etc.

I can't enter the staff names separately as they are continually changing.
Anyone got any ideas?

 

[KARL DEWEY]

Post the table structure with field names and datatype.
How are the tables related?
Post sample data.
What do you want the results to look like?
--
KARL DEWEY
Build a little - Test a little

Thanks for getting back to me Karl

The [StaffName] field comes from a table called PersonalDetails which
details a client record and the staff member allocated to work with that
client. The [ClientJobTitle] field comes from another table called
JobsFilled which the staff member would complete when the client gets a job.

What I need to do is run a query/report that shows how many clients each
staff member has got into work. I can't just have a list of every record
where [ClientJobTitle] is null, as it would be far too long. Also I can't
put each staff member name into an unbound field in the report header as the
staff members constantly change. Any advice would be appreciated.

Your explaination is lacking so I will assume the following --
The two table both have the two fields. You want a total count of records
with the ClientJobTitle as null for each StaffName.
First use an union query named Staff_Client--
SELECT [StaffName], [ClientJobTitle]
FROM Table1
UNION ALL SELECT [StaffName], [ClientJobTitle]
FROM Table2;

Then a totals query --
SELECT [StaffName], Sum(IIF([ClientJobTitle] Is Null, 1,0)) AS CountOfNull
FROM Staff_Client
GROUP BY [StaffName];

--
KARL DEWEY
Build a little - Test a little

Hi all

I have 2 fields [StaffName] & [ClientJobTitle] from 2 different tables. I
would like to run a report that will show the staff name (once only) along
with number of records where [ClientJobTitle] is null.

John Smith - 29
Karen Jones - 62
etc.

I can't enter the staff names separately as they are continually changing.
Anyone got any ideas?