DCOUNT Expression help required..

[boostbyslinky]

Folks,

Have a quick nose at the following query...

SELECT Engineers.Engineer_Name, Count(dbo_ReportData.[Job No]) AS [CountOfJob No], DCount("[Ret Call]","[dbo_reportdata]","[Ret Call]= 'N'") AS Expr1
FROM Engineers INNER JOIN dbo_ReportData ON Engineers.Engineer_ID = dbo_ReportData.Engineer
WHERE (((dbo_ReportData.[Log Num]) In (1,2,3,5,6,24,12,36,99)) AND ((dbo_ReportData.[Completion Date])>=Date()-31) AND ((Engineers.Department)="HD"))
GROUP BY Engineers.Engineer_Name
ORDER BY Count(dbo_ReportData.[Job No]) DESC;

At present the DCOUNT in expr1 is returning all Jobs with a ret call equal to N, and I can see why, but what I can't quite work out is how to get expr1 to only carry out this DCOUNT against the jobs found by the initial SELECT / Count ...

Any thoughts gratefully received!

TIA..

 

[CBrighton]

Try something like this:

DCount("[Ret Call]","[dbo_reportdata]","[Ret Call]= 'N' AND 
dbo_ReportData.[Log Num] In 1,2,3,5,6,24,12,36,99 AND 
dbo_ReportData.[Completion Date]>=Date()-31 AND 
Engineers.Department='HD'") AS Expr1

I assume you want the same criteria as the query plus the Ret Call = N which was in the DCount already?

 

[boostbyslinky]

You're correct on the criteria front..

That expression just gives me heaps of errors though I'm afraid (Access 2010 btw)...

 

[CBrighton]

I removed a load of your brackets to tidy it up and make it more readable.

You may need to add some back in there.

However, I would expect that function to give the same result for every record as it has static criteria. I suspect this is not what you want.

 

[boostbyslinky]

I'll have a tinker with some brackets!

The initial query returns the number of jobs (total) processed by an engineer, ideally the third column should give the number of jobs for which the field "ret call" equals "N" (indicating a cleared call)..

hmmmmm

 

[CBrighton]

I can't see that the DCount function would limit the count to a single engineer, as it's not part of the criteria it has been given.

 

[boostbyslinky]

Any thoughts on how I'd go about doing it then?

The initial query (without the failing DCount expression) returns
Engineers | Count of Jobs
The purpose of the addition of the expression was to return
Engineers | Count of Jobs | Cleared Jobs

What I'm attempting to do is, from the jobs selected (and counted), count how many had a "ret call" value of N...

hmmmmmm

 

[CBrighton]

OK, we should be able to get rid of the extra criteria I added to your DCount then.

However, we still need to add one for it to identify the engineer.

DCount("[Ret Call]","[dbo_reportdata]","[Ret Call]= 'N' AND [B][Field in dbo_reportdata which holds engineer name][/B] = '" & [B][Field in this query which holds engineers name][/B] & "'") AS Expr1

 

[boostbyslinky]

Perfect! I've added some additional criteria in to ensure that the same criteria are being applied to both sections of the query, and here we have it..

SELECT dbo_ReportData.Engineer, Count(dbo_ReportData.[Job No]) AS [CountOfJob No], DCount("[Ret Call]","[dbo_reportdata]","[Ret Call]= 'N' AND [engineer] = '" & [Engineer] & "' AND [Completion date]>=Date()-31 AND [Log Num] in (1,2,3,5,6,12,24,36,99) AND [Docket]=[Job No]") AS expr1
FROM Engineers INNER JOIN dbo_ReportData ON Engineers.Engineer_ID = dbo_ReportData.Engineer
WHERE (((dbo_ReportData.[Log Num]) In (1,2,3,5,6,24,12,36,99)) AND ((dbo_ReportData.[Completion Date])>=Date()-31) AND ((Engineers.Department)="HD"))
GROUP BY dbo_ReportData.Engineer
ORDER BY Count(dbo_ReportData.[Job No]) DESC;

I think I now understand how this works, so should be able to apply it elsewhere in my database.. Thanks very much indeed for your help!

 

[boostbyslinky]

Afternoon all..

I've made a return to this query, as we finally managed to review some of the data on Friday and have decided that additional information is required in this particular report..

So, to that end, I need to extend the dcount to include data from a 2nd table..

I have extended the base query accordingly (one additional table/select/group by) but can't seem to work out exactly how to extend the dcount expression to handle this..

SELECT dbo_ReportData.Engineer, Count(dbo_ReportData.[Job No]) AS [CountOfJob No], DCount("[Ret Call]","[dbo_reportdata]","[Ret Call]= 'N' AND [engineer] = '" & [Engineer] & "' AND [Completion date]>=Date()-31 AND [Log Num] in (1,2,3,5,6,12,24,36,99) AND [Docket]=[Job No]") AS expr1, Prod_ShortCode.HighLevelGroup, dbo_ReportData.[Completion Date]
FROM (Engineers INNER JOIN dbo_ReportData ON Engineers.Engineer_ID = dbo_ReportData.Engineer) INNER JOIN Prod_ShortCode ON dbo_ReportData.Description = Prod_ShortCode.Description
WHERE (((dbo_ReportData.[Log Num]) In (1,2,3,5,6,24,12,36,99)) AND ((dbo_ReportData.[Completion Date])>=#1/1/2010#) AND ((Engineers.Department)="HD"))
GROUP BY dbo_ReportData.Engineer, Prod_ShortCode.HighLevelGroup, dbo_ReportData.[Completion Date]
ORDER BY Count(dbo_ReportData.[Job No]) DESC;

At present, the dcount is (correctly) returning the number of jobs handled by each engineer (repeated for each Prod_ShortCode.HighLevelGroup), without paying any heed to Prod_ShortCode.HighLevelGroup

I'd be very grateful is anyone could lead me on my path to enlightenment!

Cheers..

 

[boostbyslinky]

Any thoughts folks?