Counting specific text (1 Viewer)

Y

yeleek

Registered User.

Local time

Yesterday, 17:13

Joined

Nov 26, 2009

Messages

10

• Sep 8, 2015

• #1

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

Well-known member

Local time

Yesterday, 20:13

Joined

Apr 9, 2015

Messages

4,339

• Sep 8, 2015

• #2

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.

 

Y

yeleek

Registered User.

Local time

Yesterday, 17:13

Joined

Nov 26, 2009

Messages

10

• Sep 8, 2015

• #3

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"));

Click to expand...

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

 

P

plog

Banishment Pending

Local time

Yesterday, 19:13

Joined

May 11, 2011

Messages

11,638

• Sep 8, 2015

• #4

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

 

Y

yeleek

Registered User.

Local time

Yesterday, 17:13

Joined

Nov 26, 2009

Messages

10

• Sep 8, 2015

• #5

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

AWF VIP

Local time

Today, 01:13

Joined

Jul 26, 2013

Messages

10,366

• Sep 8, 2015

• #6

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

 

arnelgp

..forever waiting... waiting for jellybean!

Local time

Today, 08:13

Joined

May 7, 2009

Messages

19,228

• Sep 8, 2015

• #7

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

Well-known member

Local time

Yesterday, 20:13

Joined

Apr 9, 2015

Messages

4,339

• Sep 8, 2015

• #8

no, turn on SUMMATION sign (counting)

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

 

Y

yeleek

Registered User.

Local time

Yesterday, 17:13

Joined

Nov 26, 2009

Messages

10

• Sep 8, 2015

• #9

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