I am building a query in design mode. Let's say I have a table A and table B. Field XID is a numeric ID field in both tables. Table A has a single record for each value of XID, while table B has multiple entries for each value of XID. I am trying to count the number of records in table B for each value of XID AND where field Y in table B has a value of n.

For example, if my query is based on Table A and I have XID displayed as one of the fields in the query:

MyCount: DCount ("[Y]", "B","([XID] = [B.XID]) AND ([Y] = 1)")
returns the total number of records where [Y] is 1 but not limited to where B.XID is limited to the current row of A.

MyCount: DCount ("[Y]", "B","([A.XID] = [B.XID]) AND ([Y] = 1)")
gives a bracketing error.

How should I construct this expression?
Thanks,
Dale

I don't know if this will help, But since you got a bracketing error this might help.

when it comes to building criteria expressions or a WHERE clause. What i do is built a separate query and just add those fields that have a criteria and put my criteria in. Then what i do is go to the SQL view, CTRL+C (Copy) everythng after the WHERE clause and paste it into my expression. With the quotes.

Hope that helps

I think my problem is that I do not know how to specify the XID field of the table on which the query is based in the DCount function call.

In VBA, I could do something like:

rcdA!MyCountField = DCount("[Y]", "B", "([XID] = " & rcdA!XID & ") AND ([Y] = 1)")

I suppose I could write a function to do this or a subquery, but I thought it should be possible by simply using DCount in my query.

Thanks,
Dale

Dale

You should be able to do basically the same thing - creating expressions is just like writing code.

Just replace rcd!XID or whtaever with the appropriate field name.

You could use this as a subquery

SELECT tableB.XID, Count(tableB.XID) AS CountOfXID
FROM tableB
WHERE (((tableB.Y)=1))
GROUP BY tableB.XID;

Thanks everyone. I solved the problem with the clue from norie.

The solution was something like:

MyCount: DCount ("[Y]", "B","[Y] = 1 AND [XID] = " & [A.XID])

I just needed to put my reference to A.XID outside the quoted string.

Thanks,
Dale