Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

Access Query Continued

I have the following SQL that shows me all the the count on one side and the count on the other of a join. here is the fun part. there will always be a count from each table IF there is a count from [SeviceFailure]. in other words. for the year, month and carrier there can be a count from [ServiceFailure] and that would match exactly the match in tblSVF. However there can be a count of 0 in [ServiceFailre] and a count in tblSVF. I need to combine these two but not count them twice. so if there are is a count for one record of 2 in [ServiceFailures] there will also be a count for the same record of 2 from tblSVF. It seems like I need to compare the two counts. if they are the same then just count one of them. if there are more on the tblSVF than [ServiceFailures] then use the count from tblSVF. Confused yet? Help?

Here is the SQL

SELECT Year([SVFDATE]) AS LocalYear, MonthName(Month([SVFDate])) AS LocalMonth, tblSVF.CarrierCode, Count(tblSVF.CarrierCode) AS CountOfCarrierCode, Count(ServiceFailures.[SVORD#]) AS [CountOfSVORD#]
FROM tblSVF LEFT JOIN ServiceFailures ON tblSVF.CarrierCode = ServiceFailures.CARRIER
GROUP BY Year([SVFDATE]), MonthName(Month([SVFDate])), tblSVF.CarrierCode;

Here are the results

LocalYear      LocalMonth      CarrierCode      CountOfSVFORD      CountOfSVORD#
2012      March                 FILSPA                           1                              1
2012      March                GINSHE                           2                         0
2012      March                OTITEN                           4                              4
2012      March                QTRFRE                           4                              4
2012      March                STOSEA                           1                              1
0
JArndt42
Asked:
JArndt42
  • 2
1 Solution
 
JArndt42Author Commented:
I do believe I figured it out using an iif statement.  IIf([LC]=[AS],[AS],[LC]) AS CombinedCount
0
 
JArndt42Author Commented:
the entire sql statement is.

SELECT Year([SVFDATE]) AS LocalYear, MonthName(Month([SVFDate])) AS LocalMonth, tblSVF.CarrierCode, Count(tblSVF.SVFORD) AS LC, Count(ServiceFailures.[SVORD#]) AS [AS], IIf([LC]=[AS],[AS],[LC]) AS CombinedCount
FROM tblSVF LEFT JOIN ServiceFailures ON tblSVF.CarrierCode = ServiceFailures.CARRIER
GROUP BY Year([SVFDATE]), MonthName(Month([SVFDate])), tblSVF.CarrierCode;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now