Nov 2 2006, 11:46 AM
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?
Nov 2 2006, 12:38 PM
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
Nov 2 2006, 12:46 PM
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.
Nov 2 2006, 01:01 PM
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.
Nov 2 2006, 01:04 PM
You could use this as a subquery
SELECT tableB.XID, Count(tableB.XID) AS CountOfXID
GROUP BY tableB.XID;
Nov 2 2006, 01:13 PM
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.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here