Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do I use multiple criteria inside Access 2007  dcount expression

Posted on 2011-03-09
10
Medium Priority
?
476 Views
Last Modified: 2012-05-11
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
Comment
Question by:MrAutomate
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 3

Expert Comment

by:JAMcDo
ID: 35083412
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
 

Author Comment

by:MrAutomate
ID: 35083572
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
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 35083668

 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:MrAutomate
ID: 35083722
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
 
LVL 3

Expert Comment

by:JAMcDo
ID: 35083942
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
 

Author Comment

by:MrAutomate
ID: 35084147
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
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 35084233
<<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
 
LVL 58
ID: 35084247

  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
 
LVL 71

Expert Comment

by:Qlemo
ID: 36032522
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question