Counting specific text

[yeleek]

Hi,

Within my database each record is owned by at least one staff member (I have a field called owner).

For a report I want to be able to provide a summary to say staff member 'x' owns 'y' records, staff member 'a' owns 'b' records, etc.

There are only 5 possible staff members so I'm happy to create calculated fields for each staff member in my query.

I'm having problems though - I cannot figure out how to create a calculated field which gives me the total number of records staff member 'x' owns.

Any ideas? Is there a better way of doing it? To complicate things, certain records could be owned by multiple staff members so ideally it needs to support wildcards.

Thanks in advance

 

[Ranman256]

Why do you need a virtual field?

If the table has [owner] then your report will show,
select owner, count([field]) from table

to get each owners count.

 

[yeleek]

Thanks for the reply.

The SQL (though I'm not good at reading it) looks like this for my existing query

SELECT tbl_main.Ref, tbl_main.Tier, tbl_main.Category, tbl_main.Status, tbl_main.Asset, tbl_main.[Risk Owner], tbl_main.[Risk Summary], tbl_main.[Current Risk Rating], tbl_main.[Residual Risk Rating], tbl_main.Progress
FROM tbl_main
WHERE (((tbl_main.Status)="Open"));

What I want ideally is a way within the report/query to show how many records are owned by bob, jane or freddy. If there is a better way of doing it am more than happy to try, but creating a 'virtual field' per staff member was the first thing that came to mind.

Thanks in advance

 

[plog]

yeleek, what you want is a totals query. Give this link a read:

https://support.office.com/en-us/ar...-a-query-b84cdfd8-07ba-49a7-b067-e1024ccfcca8

Or this one:

http://www.w3schools.com/sql/sql_func_count.asp

 

[yeleek]

Hi,

I've tried adding totals and selecting 'Count' on the status field (which has a criteria of 'open').

I get an error

'You tried to execute a query that does not include the specified expression 'Ref' as part of an aggregate function'.

What am i doing wrong?

Thanks

 

[Minty]

Please post up the SQL of your query - we can't guess...

 

[arnelgp]

if you have separate table for Staff Names:

SELECT tblStaff.StaffName, (Select Count(*) From theTableWithOwnerField Where Instr([Owner], [tblStaff].[StaffName]) >0) AS [Owned Records]
FROM tblStaff;

 

[Ranman256]

no, turn on SUMMATION sign (counting)

select [Risk Owner], count([Ref]) as CountOfRef from tbl_main WHERE (((tbl_main.Status)="Open"));

 

[yeleek]

Thanks all - figured it out with the last one from Ranman. Set my criteria and where, then count the number of ref's. Thanks