Combining 3 Queries

[MarkNie]

Hi All

Just stuck again with combining a 3 queries. I have tried to combine them but I don't get the correct value at the end.

What the query is suppost to do:

I have a date which all 3 of these fields have to be signed off by (Attn, IP, SLA). If any of these miss the date that it needs to be signed off by then it should show on this query. This should also happen if any one of the 3 are null and the due date has passed.

tblTracking.PerfDate is the due date
These are the fields which have to be signed off.
qrybAttn.Date
qrybIP.Date
qrybSLA.Date

I had to use an int option as the dates have time on them too.

Here are the 3 queries I am trying to combine:

1.
SELECT tblTracking.PortfolioCode, tblTracking.PerfDate, IIf(IsNull([qrybAttn].[Date]),Null,CDate(Int([qrybAttn].[Date]))) AS AttnD_Date, Count(*) AS CountOfPerfDate, tblTracking.ProductType
FROM tblTracking LEFT JOIN qrybAttn ON tblTracking.TrackingID = qrybAttn.TrackingID
WHERE (((Int(Nz([qrybAttn].[Date],Date())))>[tblTracking].[PerfDate]) AND ((tblTracking.PortfolioCode) Not In ("630541","630542")) AND ((tblTracking.PerfDate)<Date())) OR (((tblTracking.PortfolioCode) Not In ("630541","630542")) AND ((tblTracking.PerfDate)<Date()) AND ((qrybAttn.Date) Is Null))
GROUP BY tblTracking.PortfolioCode, tblTracking.PerfDate, IIf(IsNull([qrybAttn].[Date]),Null,CDate(Int([qrybAttn].[Date]))), tblTracking.ProductType
ORDER BY tblTracking.PortfolioCode;

2.
SELECT tblTracking.PortfolioCode, tblTracking.PerfDate, IIf(IsNull([qrybIP].[Date]),Null,CDate(Int([qrybIP].[Date]))) AS IPD_Date, Count(*) AS CountOfPerfDate, tblTracking.ProductType
FROM tblTracking LEFT JOIN qrybIP ON tblTracking.TrackingID = qrybIP.TrackingID
WHERE (((Int(Nz([qrybIP].[Date],Date())))>[tblTracking].[PerfDate]) AND ((tblTracking.PortfolioCode) Not In ("630541","630542")) AND ((tblTracking.PerfDate)<Date())) OR (((tblTracking.PortfolioCode) Not In ("630541","630542")) AND ((tblTracking.PerfDate)<Date()) AND ((qrybIP.Date) Is Null))
GROUP BY tblTracking.PortfolioCode, tblTracking.PerfDate, IIf(IsNull([qrybIP].[Date]),Null,CDate(Int([qrybIP].[Date]))), tblTracking.ProductType
ORDER BY tblTracking.PortfolioCode;

3.
SELECT tblTracking.PortfolioCode, tblTracking.PerfDate, IIf(IsNull([qrybSLA].[Date]),Null,CDate(Int([qrybSLA].[Date]))) AS SLAD_Date, Count(*) AS CountOfPerfDate, tblTracking.ProductType
FROM tblTracking LEFT JOIN qrybSLA ON tblTracking.TrackingID = qrybSLA.TrackingID
WHERE (((Int(Nz([qrybSLA].[Date],Date())))>[tblTracking].[PerfDate]) AND ((tblTracking.PortfolioCode) Not In ("630541","630542")) AND ((tblTracking.PerfDate)<Date())) OR (((tblTracking.PortfolioCode) Not In ("630541","630542")) AND ((tblTracking.PerfDate)<Date()) AND ((qrybSLA.Date) Is Null))
GROUP BY tblTracking.PortfolioCode, tblTracking.PerfDate, IIf(IsNull([qrybSLA].[Date]),Null,CDate(Int([qrybSLA].[Date]))), tblTracking.ProductType
ORDER BY tblTracking.PortfolioCode;

This is what I have done to combine them, but as I said I am still not getting the correct value.

SELECT tblTracking.PortfolioCode, tblTracking.PerfDate, IIf(IsNull([qrybAttn].[Date]),Null,CDate(Int([qrybAttn].[Date]))) AS D_Date1, IIf(IsNull([qrybIP].[Date]),Null,CDate(Int([qrybIP].[Date]))) AS _Date2, IIf(IsNull([qrybSLA].[Date]),Null,CDate(Int([qrybSLA].[Date]))) AS D_Date3, Count(*) AS CountOfPerfDate, tblTracking.ProductType
FROM ((tblTracking LEFT JOIN qrybAttn ON tblTracking.TrackingID = qrybAttn.TrackingID) LEFT JOIN qrybIP ON tblTracking.TrackingID = qrybIP.TrackingID) LEFT JOIN qrybSLA ON tblTracking.TrackingID = qrybSLA.TrackingID
WHERE (((Int(Nz([qrybAttn].[Date],Date())))>[tblTracking].[PerfDate]) AND ((tblTracking.PortfolioCode) Not In ("630541","630542")) AND ((tblTracking.PerfDate)<Date())) OR (((tblTracking.PortfolioCode) Not In ("630541","630542")) AND ((tblTracking.PerfDate)<Date()) AND ((qrybAttn.Date) Is Null))
AND (((Int(Nz([qrybIP].[Date],Date())))>[tblTracking].[PerfDate]) AND ((tblTracking.PortfolioCode) Not In ("630541","630542")) AND ((tblTracking.PerfDate)<Date())) OR (((tblTracking.PortfolioCode) Not In ("630541","630542")) AND ((tblTracking.PerfDate)<Date()) AND ((qrybIP.Date) Is Null))
AND(((Int(Nz([qrybSLA].[Date],Date())))>[tblTracking].[PerfDate]) AND ((tblTracking.PortfolioCode) Not In ("630541","630542")) AND ((tblTracking.PerfDate)<Date())) OR (((tblTracking.PortfolioCode) Not In ("630541","630542")) AND ((tblTracking.PerfDate)<Date()) AND ((qrybSLA.Date) Is Null))
GROUP BY tblTracking.PortfolioCode, tblTracking.PerfDate, IIf(IsNull([qrybAttn].[Date]),Null,CDate(Int([qrybAttn].[Date]))), IIf(IsNull([qrybIP].[Date]),Null,CDate(Int([qrybIP].[Date]))), IIf(IsNull([qrybSLA].[Date]),Null,CDate(Int([qrybSLA].[Date]))), tblTracking.ProductType
ORDER BY tblTracking.PortfolioCode;

If you would like to have a look at thread number 1330813 to see how I got this far with another query.

Any help with this will be great.

Thanks in advance.
Regards
Mark

 

[MarkNie]

It is very strange as I am just going through the data now and it seems to be working for some records and not working for others which are exactly the same data just a diff account number.

Thanks
Regards
Mark

 

[rac2]

Well I cant handle all that code (at least not for free) but I can offer you a pattern for this kind of criterion. I trust you can add the GROUP BY and other details.

SELECT * 
FROM tblTracking
LEFT JOIN qrybAttn 
       ON tblTracking.TrackingID = qrybAttn.TrackingID 
LEFT JOIN qrybIP 
       ON tblTracking.TrackingID = qrybIP.TrackingID 
LEFT JOIN qrybSLA 
       ON tblTracking.TrackingID = qrybSLA.TrackingID
WHERE (
       DateDiff("d", Date(), qrybAttn.Date) > 0
       OR
       DateDiff("d", Date(), qrybIP.Date) > 0
       OR
       DateDiff("d", Date(), qrybSLA.Date) > 0
      )
   OR (
       DateDiff("d",tblTracking.PerfDate,Date()) > 0
       AND
       (
        qrybAttn.Date IS NULL
        OR
        qrybIP.Date IS NULL
        OR
        qrybSLA.Date IS NULL
       )
      )

The WHERE clause has two kinds of conditions, either one of which should apply for rows of interest, either a completion date exceeds the due date, or the due date has passed and some item has not been completed.

In either case, failure on any one of the completion dates will cause that row to be retrieved.

Take a look at the Help for Date/Time functions, I used DateDiff() which returns an integer. If the second date is after the first date, the interger is positive.

And I used the IS NULL expression to determine whether a date is filled in or not.

HTH