CODEWITHSUNDEEP
sqlms-access
jerry
jerry

Reputation: 2789

SQL - multiple conditions in DCount() function

I am using MS Access 2007.

A: DCount("[Name]","[Main]","[Name] = 'Mark'")/
   DCount("[Entry]","[Main]","[Entry] = 1")

Okay, so I am basically counting the number of people with the name Mark and I am dividing it by the number of Entry's that = 1 in my database. That's easy enough, but I am trying to apply a third condition, where

[Location]![City] = 'Chicago'

, but Access isn't letting me do this (It can't find the table, even though it's in the table I specified above.

   DCount("[Name]","[Main]","[Name] = 'Mark' AND [Location]![City] = 'Chicago'")/
   DCount("[Entry]","[Main]","[Entry] = 1")

I have also tried filtering the city with a Where clause in the Design view, but the condition is being applied after the calculation above, so the calculation is the same regardless of the city. I just need it to perform the above calculation for the city of Chicago.

Is something like this possible with DCount?

Also, I would die a happy man if you could tell me how to Group By the city While performing the calculations for each one separately, but I would also be very thankful if someone could just show me how to do it the first way too.

Thanks

Upvotes: 0

Views: 10689

Answers (2)

HansUp
HansUp

Reputation: 97101

What is [Location]![City]? My answer is based on the presumption it refers to a field named City in a table named Location.

If that is correct, I think your problem is because you're attempting to specify a condition based on a field which is not part of the domain ([Main]) you told DCount to use.

From Microsoft's Documentation, the domain is "A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for a query that does not require a parameter."

So if you want your DCount criteria to refer to fields in two tables, consolidate the tables in the form of a query into a single "domain". Maybe your query could be something like this, "qryMainWithCity":

SELECT m.[Name], m.Entry, m.City_ID, l.City
FROM
    Main AS m
    INNER JOIN Location AS l
    ON m.City_ID = l.City_ID;

If that query works for your situation, you should be able to get what you want with a DCount expression like this:

DCount("*","qryMainWithCity","[Name] = 'Mark' AND City = 'Chicago'")

Upvotes: 1

David-W-Fenton
David-W-Fenton

Reputation: 23067

I was just posting the same answer as @HansUp's came up. I have an alternative way to do it, and that's to use an instant recordset lookup:

  Dim varReturnValue as Variant

  varReturnValue = CurrentDB.OpenRecordset("SELECT Main.[Name] FROM Main INNER JOIN Location ON Main.City_ID = Location.City_ID WHERE Main.[Name] = 'Mark' AND Location.City = 'Chicago';")(0)

That returns the first field in the recordset returned (the index is zero-based). That way you don't have to save a query.

Upvotes: 1

Related Questions