Problems Counting in Query 1

[rdgskier8]

Hey all,
Here's my newest problem...

I have a query (qryP1Pound) pulling data from two tables: FailureHub and SalvageHub. I want the query to count entries in two timelines (2005/2004) in both tables, then subtract the two counts to give me total un-salvageable failures in each year.

After adding the date in so I could see individual entries, I discovered that the problem is whenever there are salvaged items - the failure items shows up as the same number (it shouldn't be) and when I subtract them it appears as zero.

Any suggestions? Here's my SQL:

SELECT Count(FailureHub.IDfailure) AS fai, Format([failurehub].[Date],"yyyy") AS [Year], Count(SalvageHub.IDsalvage) AS sal, [fai]-[sal] AS Expr1
FROM FailureHub LEFT JOIN SalvageHub ON FailureHub.Date = SalvageHub.Date
WHERE (((FailureHub.Date)>=#5/19/2005# And (FailureHub.Date)<=#5/23/2005#)) OR (((FailureHub.Date)>=#5/19/2004# And (FailureHub.Date)<=#5/23/2004#))
GROUP BY Format([failurehub].[Date],"yyyy");

I'd really appreciate any help.

 

[rac2]

I suspect that the two tables are not really related by date, that there is nothing in particular about the date of failure of one hub and the date of salvaging another hub.

Maybe make two queries first, one to count failures by year and the other to count salvages by year. These can then be JOINed on the Year and each row in the join will have the total failures for that year in one column and the total salvages for the same year in a second column. Then the query is straightforward.

Suppose you make queries named YearlyFailures and YearlySalvages like so

SELECT Count(FailureHub.IDfailure) AS fai, Format([failurehub].[Date],"yyyy") AS [Year]
FROM FailureHub
WHERE (((FailureHub.Date)>=#5/19/2005# And (FailureHub.Date)<=#5/23/2005#))
    OR
 (((FailureHub.Date)>=#5/19/2004# And (FailureHub.Date)<=#5/23/2004#))
GROUP BY Format([failurehub].[Date],"yyyy");

and

SELECT Format([SalvageHub].[Date],"yyyy") AS [Year], Count(SalvageHub.IDsalvage) AS sal
FROM SalvageHub
WHERE (((SalvageHub.Date)>=#5/19/2005# And (SalvageHub.Date)<=#5/23/2005#))
    OR
 (((SalvageHub.Date)>=#5/19/2004# And (SalvageHub.Date)<=#5/23/2004#))
GROUP BY Format([SalvageHub].[Date],"yyyy");

Then the numbers you seek are -

SELECT YearlyFailures.fai,
YearlyFailures.[Year],
YearlySalvages.sal,
 YearlyFailures.[fai] - YearlySalvages[sal] AS FailuresExceedSalvages

FROM YearlyFailures 
LEFT JOIN YearlySalvages ON YearlySalvages.[Year] = YearlyFailures.[Year]