asked on

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

JAMcDo

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.

MrAutomate

ASKER

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

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MrAutomate

ASKER

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?

JAMcDo

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.

MrAutomate

ASKER

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

SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Dettman (EE MVE)

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.

Qlemo

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.