Counting from 2 different tables

[scott04]

Hi everyone,
I am having trouble with one of my reports. I would like to be able to
count the number of audits an auditor has completed. I have two tables
tblOngoingAdt and tblAuditLife with each table containing the field name
"auditor". I would like to only count it if the status = 3. I have been
able to get this to work for only one table each but not for both tables.
Can someone please point me in the right direction? As always thank you.
I did have a query that reference my tblauditlife and had criteria for
status = 3 and calculated fields in the query Test: IIf([auditor]=1,1,0).
Then in my report i had a textbox of Sum([Test]). My problem is i need to do
the same from another query and it will not work.

 

[Jeff Boyce]

I'm not very clear on the structure of your data. "How" you'd do a query
depends on "what" the data looks like.

Is there a reason you couldn't create a new query against the second table
and use the selection criterion of 3 for the status field in that table?

(By the way, are the two tables essentially identical in structure? If so,
why do you have two, instead of a single table with an additional field to
indicate which "type" of audit it is?)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[scott04]

Jeff,

Great idea of just adding the field...i think i will use that approach...

I'm not very clear on the structure of your data. "How" you'd do a query
depends on "what" the data looks like.

Is there a reason you couldn't create a new query against the second table
and use the selection criterion of 3 for the status field in that table?

(By the way, are the two tables essentially identical in structure? If so,
why do you have two, instead of a single table with an additional field to
indicate which "type" of audit it is?)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi everyone,
I am having trouble with one of my reports. I would like to be able to
count the number of audits an auditor has completed. I have two tables
tblOngoingAdt and tblAuditLife with each table containing the field name
"auditor". I would like to only count it if the status = 3. I have been
able to get this to work for only one table each but not for both tables.
Can someone please point me in the right direction? As always thank you.
I did have a query that reference my tblauditlife and had criteria for
status = 3 and calculated fields in the query Test: IIf([auditor]=1,1,0).
Then in my report i had a textbox of Sum([Test]). My problem is i need to
do
the same from another query and it will not work.