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?
Click to expand...

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.
 
M

msnews.microsoft.com

find solutions of problems and solution regarding CISCO, CCNA, CCNP, MCSE,
LINUX, WIRELESS, SECURITY, NETWORK, IT NEWS

http://www.theitstuff.co.cc/

http://www.theitstuff.co.cc/understand-ospf-lsalink-state-advertisement-types/

http://www.theitstuff.co.cc/explore-different-types-of-ethical-hacking/

http://www.theitstuff.co.cc/what-network-management-covers/

http://www.theitstuff.co.cc/simple-network-management-protocol/

http://www.theitstuff.co.cc/important-consideration-in-designing-of-the-server-room/

http://www.theitstuff.co.cc/router-as-a-dhcp-server/

http://www.theitstuff.co.cc/assembling-a-motherboard-help-with-video-tutorial/

http://www.theitstuff.co.cc/ccna-and-ccnp-tutorial-vlan-trunking-protocol-vtp/

http://www.theitstuff.co.cc/nat-configuration-on-a-cisco-router/

http://www.theitstuff.co.cc/how-to-make-a-cat-6-network-cable-video-tutorials/

http://www.theitstuff.co.cc/types-of-network-attacks/

http://www.theitstuff.co.cc/advantages-of-optical-fiber-over-other-media/

http://www.theitstuff.co.cc/learn-tcp-ip-with-beautiful-animation-video/

http://www.theitstuff.co.cc/firewall-techniques/
 
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.
Click to expand...

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

Thank you John.
 
Top