I have 2 queries (QueryA, QueryB) in MS Access. Each of them has some records that the other one doesn't. There's one field by which I'd check it called UniqueID. Both queries have this field (Technically speaking it is not entirely unique and it's not a primary key but I don't think it's relevant to my question).
What I usually do in Excel is use the COUNTIF function counting how many records/rows with this particular UniqueID are in QueryA. So I'd be on QueryB Tab in Excel and have:
=countif(QueryA!A:A,[@UniqueID])
QueryB Tab in Excel:
--------------------------------------------
UniqueID | Instances of UniqueID in QueryA |
12341 | 2 |
44444 | 1 |
66666 | 0 |
So I know that there are 2 rows with UniqueID 12341 in QueryA Tab/worksheet, 1 instance of 44444, an NO instances of UniqueID 66666
So I'd like to add the instances field in my Access QueryB (to check how many record (if any) with each UniqueID record there are in QueryA.
Quick google showed that DCount might be what I'm after but I'm struggling with referencing the query. So in QueryB, I've added the field/column to my query:
Instances: DCount([UniqueID],[QueryA],[UniqueID]=[UniqueID])
That's obviously wrong but not sure how to do it. Thanks
I don't want to do an aggregate function 'count' as there are many more fields in this query so I can't just group this by UniqueID.
Should review MS documentation on domain aggregate functions. Enclose object references in quote marks and concatenate dynamic parameter.
Instances: DCount("*","[QueryA]","[UniqueID]=" & [UniqueID])
Or build an aggregate query that does the QueryA count by UniqueID and join that query to QueryB.