• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 500
  • Last Modified:

How do I use multiple criteria inside Access 2007 dcount expression

Experts,

I have a group by query I need some help with.  basically 4 columns:
1.  Week Beginning (converts date to the current monday)
2.  Overall loans reviewed  (count *)
3. Loans with errors (where dcount returns issue = error)
4.  Loans with no error (where dcount returns issue = none)

Column 1 and 2 work as expected.  How do I write this so that dcount only returns the corresponding error count for that weeks record set.
In the attched screen shot (docx), using the 3/7 week example, there should be 1 error and 1 no error.  Currently, the dcount has no additional clause that limits it, and counts all errors and all non errors.  I am not getting the syntax correct and need some help.

sql code is also attached.
ps - is there a better, non excel pivot table way, to do something like this ?

Many thanks!
 
SELECT DateAdd("d",(CInt(Format([Date],"w"))-2)*-1,[Date]) AS [Week Beginning], Count(*) AS [Overall loans Reviewed], DCount("Issue","tbl_loans_Reviewed","[Issue] =" & "'Error'") AS [Loans with Errors], DCount("Issue","tbl_loans_Reviewed","[Issue] =" & "'None' and 
DatePart('ww',[Date])") AS [Loans with no errors], [Loans with no errors]/Count(*) AS [Percent of loans with no Errors], [Loans with errors]/Count(*) AS [Percent of loans with Errors], tbl_loans_reviewed.[Loan Type]
FROM tbl_loans_reviewed
GROUP BY DateAdd("d",(CInt(Format([Date],"w"))-2)*-1,[Date]), tbl_loans_reviewed.[Loan Type], DatePart('ww',[Date]);

Open in new window

ee-screenshot.docx
0
MrAutomate
Asked:
MrAutomate
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
JAMcDoCommented:
I've made some changes but since I cannot test the query, I don't know if I caught all the problems.

SELECT DateAdd("d",(CInt(Format([Date],"w"))-2)*-1,[Date]) AS [Week Beginning], Count(*) AS [Overall loans Reviewed], DCount("[Issue]","tbl_loans_Reviewed","[Issue] ='Error'") AS [Loans with Errors], DCount("[Issue]","tbl_loans_Reviewed","[Issue] = 'None' and DatePart('ww',[Date])") AS [Loans with no errors], [Loans with no errors]/Count(*) AS [Percent of loans with no Errors], [Loans with errors]/Count(*) AS [Percent of loans with Errors], tbl_loans_reviewed.[Loan Type] AS Expr1
FROM tbl_loans_reviewed
GROUP BY DateAdd("d",(CInt(Format([Date],"w"))-2)*-1,[Date]), tbl_loans_reviewed.[Loan Type], DatePart('ww',[Date]);

The main thing I thought would be giving the error was the lack of [ . . .] around some items.

Try it and see if it works for you.

J.
0
 
MrAutomateAuthor Commented:
JAMcDo:,

Thasnks for the quick response and taking a look.  I astill get the same results.  I have zipped a copy of the db so you can see the objects and test.  

On the 3/7/2011 line I need it to read Loans with Errors 1, loans with no errors 1 (you will see the table for that week has 2 examples, 1 error, and 1 no error.

Thanks again
ee-test.zip
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

 FYI, the Domain Functions (DLookup, DCount, etc) should never be used within a SQL expression as they are un-optimizable by the query parsing engine and will yield very poor performance.

 You need to be using a sub select.

  The Domain functions are intended to be used in places where SQL expressions are not allowed, but a VBA expression is.  All the domain functions are really SQL SELECT statements that are encapsulated.

  DCount() is really:

  Select COUNT(<fieldName>) FROM <table name> WHERE <criteria>

  which is what you should be writting in your SQL as a sub select.

JimD.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
MrAutomateAuthor Commented:
JimD.  I am unsure what you are saying.  Are you saying I have :

1.  DCount("Issue","[tbl_loans_reviewed]","issue ='none'
Then a seperate  
2. Dcount(DateAdd("d",(CInt(Format([Date],"w"))-2)*-1,[Date])=[qry_rpt_Weekly_temp]![Week Beginning]")

and how do I write the piece to put them togther?

0
 
JAMcDoCommented:
I have to get back to work know, so I will come back to it later as time permits - sorry.

JDettman has a good point and it is worth following.  See if you can comvert some of the DCOUNT statments to SELECT statements.

J.
0
 
MrAutomateAuthor Commented:
Thanks all.  After my cafine kicked in , I realzed a crosstab query would work, so I have this solved, but I DO want to use this example to learn more.  

Please show me an example of best practice inside of the DB I zipped up, on how to convert dcount to select quesries, using sub selects, etc.  I will award points based on the easiest to follow, as I am not a professional developer, but learn by studying examples .

Thanks again
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<JimD.  I am unsure what you are saying.  Are you saying I have :>>

  No, you should not be using DCount at all.  Your SQL should look like this:

SELECT DateAdd("d",(CInt(Format([Date],"w"))-2)*-1,[Date]) AS [Week Beginning], Count(*) AS [Overall loans Reviewed], (Select Count([Issue]) From tbl_loans_Reviewed Where [Issue] ='Error') AS [Loans with Errors],

 as an example.

JimD.

0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  Another way of doing that would be to write a seperate query, save it, then use that as a "table" in the current query and do a join to it.

JimD.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now