Left Join Help

[joshk6656]

I currently have a left join pulling data from a table inputted weekly with production of workers.

I developed a new table which the workers process the claims, and it enters a entry for each item processed.

I want to change over from the left join of the lump sum (entered by hand weekly), to it being calculated from my processed items table.

The join i have now is:

SELECT AuditLog.Processor, Format(CDbl(Date)-(Weekday(Date)-2), "mm/dd/yyyy") AS WeekOf, ProductionCount.WeeklyCount AS [Weekly Sum], count (*) AS Total, (sum(Correct)*-1) AS [Correct Claims], (sum(Accuracy)*-1) AS [Accuracy Mistakes], (sum(Quality)*-1) AS [Quality Mistakes], ClaimType
FROM AuditLog LEFT JOIN ProductionCount ON ( Format(CDbl(AuditLog.Date)-(Weekday(AuditLog.Date)-2), "mm/dd/yyyy") =  Format(CDbl(ProductionCount.Weekof)-(Weekday(ProductionCount.Weekof)-2), "mm/dd/yyyy")) AND (AuditLog.Processor = ProductionCount.Processor)
WHERE format([Date], "mm/yyyy") = Forms![AuditReports]![txtDate] AND format([ProductionCount.Weekof], "mm/yyyy") = Forms![AuditReports]![txtDate] and (AuditLog.ClaimType = ProductionCount.Type)
GROUP BY AuditLog.Processor, Format(CDbl(Date)-(Weekday(Date)-2), "mm/dd/yyyy"), ProductionCount.WeeklyCount, ClaimType;

This pulls each processor, and breaks it down by week for an entire month.

Processor  | WeekOf     | Weekly Sum | Total | Correct Claims | Accuracy Mistakes | Quality Mistakes | ClaimType
Josh Kraft | 06/02/2008 | 293        | 11    | 11             | 0                 | 0                | Blue Card

The Weekly Sum field is the field pulled from the other table, which I am looking to replace with a Count() of whenever the Processor / Date / Type Matches.

I thought this would work:

SELECT AuditLog.Processor, Format(CDbl(AuditLog.Date)-(Weekday(AuditLog.Date)-2), "mm/dd/yyyy") AS WeekOf, Count(ClaimsProcessed.Date) AS [Weekly Sum], count(AuditLog.Date) AS Total, (sum(Correct)*-1) AS [Correct Claims], (sum(Accuracy)*-1) AS [Accuracy Mistakes], (sum(Quality)*-1) AS [Quality Mistakes], AuditLog.ClaimType
FROM AuditLog LEFT JOIN ClaimsProcessed ON (AuditLog.ClaimType = ClaimsProcessed.Type) AND (AuditLog.Processor = ClaimsProcessed.Processor) AND ( Format(CDbl(AuditLog.Date)-(Weekday(AuditLog.Date)-2), "mm/dd/yyyy") =  Format(CDbl(ClaimsProcessed.Date)-(Weekday(ClaimsProcessed.Date)-2), "mm/dd/yyyy"))
WHERE format([AuditLog.Date], "mm/yyyy") = Forms![AuditReports]![txtDate] AND format([ClaimsProcessed.Date], "mm/yyyy") = Forms![AuditReports]![txtDate] and (AuditLog.ClaimType = ClaimsProcessed.Type)
GROUP BY AuditLog.Processor, Format(CDbl(AuditLog.Date)-(Weekday(AuditLog.Date)-2), "mm/dd/yyyy"), AuditLog.ClaimType, Format(CDbl(ClaimsProcessed.Date)-(Weekday(ClaimsProcessed.Date)-2), "mm/dd/yyyy");

But it seems like it is taking the count from the AuditLog table and multiplying it by the count in the ClaimsProcessed table for EACH field.

Test

Processor  | WeekOf     | Weekly Sum | Total | CorrectClaims | AccuracyMistakes | QualityMistakes | ClaimType
Josh Kraft | 06/09/2008 | 3          | 3     | 3             | 0                | 0               | Blue Card

This SHOULD read:

Processor  | WeekOf     | Weekly Sum | Total | CorrectClaims | AccuracyMistakes | QualityMistakes | ClaimType
Josh Kraft | 06/09/2008 | 1          | 1     | 1             | 0                | 0               | Blue Card

Any help with this is appreciated.

 

[Guus2005]

Please post a sample database

 

[joshk6656]

I attached the database, also i included the 2 things i would like joined as seperate queries. input 06/2008 into the popup to see the results of the query.

thanks for the help

 

[joshk6656]

Well, I switched from a JOIN over to a subquery and got it working perfectly using the following:

SELECT AuditLog.Processor,
Format(CDbl(Date)-(Weekday(Date)-2), "mm/dd/yyyy") AS WeekOf,
(Select Sum(ProductionCount.WeeklyCount) 
From ProductionCount
Where Format(CDbl(AuditLog.Date)-(Weekday(AuditLog.Date)-2), "mm/dd/yyyy")=Format(CDbl(ProductionCount.Weekof)-(Weekday(ProductionCount.Weekof)-2), "mm/dd/yyyy") AND (AuditLog.Processor = ProductionCount.Processor) AND (AuditLog.ClaimType = ProductionCount.Type)) AS [Weekly Sum],
count (*) AS Total, (sum(Correct)*-1) AS [Correct Claims],
(sum(Accuracy)*-1) AS [Accuracy Mistakes],
(sum(Quality)*-1) AS [Quality Mistakes],
ClaimType
FROM AuditLog
WHERE format([Date], "mm/yyyy") = Forms![AuditReports]![txtDate]
GROUP BY AuditLog.Processor, Format(CDbl(Date)-(Weekday(Date)-2), "mm/dd/yyyy"), ClaimType;

The newest problem, reporting does not support multi level GROUP BY clauses. Anyone have any ideas on how to get around this?