DCount Bridging Nested Tables (?)

C

croy

I have nested tables:

tbl1
|_____tbl2
|____tbl3

They are linked by typical autonumber key fields.

On a subform that draws from a query based on tbl1, I would
like to use DCount to count the number of related records in
tbl3. But I can't seem to get the syntax right.

Here's what I've got at the moment (that doesn't work):

=IIf(IsNull([tbl1Id]),"",DCount("tbl2Id","tbl2","tbl1Id=" _
& [txttbl1Id] And "tbl3.tbl2Id =" & tbl2Id))

Is this even possible?

If so, can I persuade any of you to offer better syntax?
 
J

John W. Vinson

I have nested tables:

tbl1
|_____tbl2
|____tbl3

They are linked by typical autonumber key fields.

On a subform that draws from a query based on tbl1, I would
like to use DCount to count the number of related records in
tbl3. But I can't seem to get the syntax right.

Here's what I've got at the moment (that doesn't work):

=IIf(IsNull([tbl1Id]),"",DCount("tbl2Id","tbl2","tbl1Id=" _
& [txttbl1Id] And "tbl3.tbl2Id =" & tbl2Id))

Is this even possible?

If so, can I persuade any of you to offer better syntax?

You'll need a query joining tbl2 to tbl3 in order to do this. There are no
records in tbl2 that would match your criterion since tbl3.tbl2ID is not a
field in tbl2!

I'd create a query

SELECT tbl2.tbl1ID, Count(*) AS Count3 FROM tbl2 LEFT JOIN tbl3
ON tbl3.tbl2ID = tbl2.tbl2ID;

and save this as qryCount3; you could then simply use

=DLookUp("Count3", "qryCount3", "tbl1ID = " & NZ(txtTbl1ID))

to get the count.
 
C

croy

You'll need a query joining tbl2 to tbl3 in order to do this. There are no
records in tbl2 that would match your criterion since tbl3.tbl2ID is not a
field in tbl2!

I'd create a query

SELECT tbl2.tbl1ID, Count(*) AS Count3 FROM tbl2 LEFT JOIN tbl3
ON tbl3.tbl2ID = tbl2.tbl2ID;

and save this as qryCount3; you could then simply use

=DLookUp("Count3", "qryCount3", "tbl1ID = " & NZ(txtTbl1ID))

to get the count.

Now I remember why I keep that two-by-four next to my desk!

Thank you John.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top