Max Query Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am attempting to return key records from 2 linked tables based on the
Maximum negative number returned from a DateDiff calculation.

Tables (linked on [Acct])

Table 1: T_Issue Table 2: T_Results
Acct Acct
UnitNo ResultNo
IssueDT CollectDT
Result

This first query returns:
-All [UnitNo] records from the T_Issue table
-The associated [ResultNo] records from the T_Results table where the
DateDiff is a negative number.
-The results of the DateDiff calculation

Qtime
SELECT [T_Issue].[UnitNo], DateDiff("h",[IssueDT],[CollectDT]) AS LabTime,
[Lab Results].[ResultNo]
FROM (([T_Results] RIGHT JOIN [T_Issue] ON [T_Results].Account =
[T_Issue].Account)
WHERE (((DateDiff("h",[IssueDT],[CollectDT]))<0))
ORDER BY [T_Issue].[UnitNo], DateDiff("h",[IssueDT],[CollectDT]) DESC;

The second Query attempts to return the records associated with the Max
[LabTime] calculated field, grouped by [UnitNo].
However, as written, no results are returned. I have also tried to include
the Max and DateDiff in the first Query without success.

Qmax
SELECT Qtime.[UnitNo], Qtime.LabTime, Qtime.[ResultNo]
FROM Qtime
WHERE (((Qtime.LabTime) In (SELECT Max([Qtime].[ResultNo]) AS MaxLabTime
FROM [Qtime]
GROUP BY [Qtime].[UnitNo])));

I sincerely appreciate any help offered.
 
I think your Qmax query should read

SELECT Qtime.[UnitNo], Qtime.LabTime, Qtime.[ResultNo]
FROM Qtime
WHERE (((Qtime.LabTime) In (SELECT Max([Qtime].LabTime) AS MaxLabTime
FROM [Qtime]
GROUP BY [Qtime].[UnitNo])));
 
Thanks for the reply Chris.
I tried your suggestion. However, it returns more than the Max value for
each record. For example:

Qmax actual return: Qmax should return:
Unit No LabTime No Unit No LabTime No
123 -2 8010 123 -2 8010
123 -5 8078 456 -6 19780
123 -8 8134 987 -4 30488
456 -6 19780
456 -12 20171
456 -18 20371
456 -24 20429
987 -4 30488
987 -7 30434
987 -17 29980
987 -28 29521

thanx

ChrisJ said:
I think your Qmax query should read

SELECT Qtime.[UnitNo], Qtime.LabTime, Qtime.[ResultNo]
FROM Qtime
WHERE (((Qtime.LabTime) In (SELECT Max([Qtime].LabTime) AS MaxLabTime
FROM [Qtime]
GROUP BY [Qtime].[UnitNo])));


nataqe said:
I am attempting to return key records from 2 linked tables based on the
Maximum negative number returned from a DateDiff calculation.

Tables (linked on [Acct])

Table 1: T_Issue Table 2: T_Results
Acct Acct
UnitNo ResultNo
IssueDT CollectDT
Result

This first query returns:
-All [UnitNo] records from the T_Issue table
-The associated [ResultNo] records from the T_Results table where the
DateDiff is a negative number.
-The results of the DateDiff calculation

Qtime
SELECT [T_Issue].[UnitNo], DateDiff("h",[IssueDT],[CollectDT]) AS LabTime,
[Lab Results].[ResultNo]
FROM (([T_Results] RIGHT JOIN [T_Issue] ON [T_Results].Account =
[T_Issue].Account)
WHERE (((DateDiff("h",[IssueDT],[CollectDT]))<0))
ORDER BY [T_Issue].[UnitNo], DateDiff("h",[IssueDT],[CollectDT]) DESC;

The second Query attempts to return the records associated with the Max
[LabTime] calculated field, grouped by [UnitNo].
However, as written, no results are returned. I have also tried to include
the Max and DateDiff in the first Query without success.

Qmax
SELECT Qtime.[UnitNo], Qtime.LabTime, Qtime.[ResultNo]
FROM Qtime
WHERE (((Qtime.LabTime) In (SELECT Max([Qtime].[ResultNo]) AS MaxLabTime
FROM [Qtime]
GROUP BY [Qtime].[UnitNo])));

I sincerely appreciate any help offered.
Click to expand...
Click to expand...
 
Ok I see what's going on

Try this for your first query
Qtime
SELECT T_Issue.UnitNo, Max(DateDiff("h",[IssueDT],[CollectDT])) AS LabTime
FROM T_Issue INNER JOIN T_Results ON T_Issue.Account = T_Results.Account
GROUP BY T_Issue.UnitNo
HAVING (((Max(DateDiff("h",[IssueDT],[CollectDT])))<0));


and this for your second query
Q1
SELECT T_Issue.UnitNo, T_Results.ResultNo,
DateDiff("h",[IssueDT],[CollectDT]) AS LabTime
FROM T_Issue INNER JOIN T_Results ON T_Issue.Account = T_Results.Account;

and finally, the one you want
QMax
SELECT Q1.UnitNo, Q1.LabTime, Q1.ResultNo
FROM Q1 INNER JOIN QTime ON (Q1.ResultNo = QTime.LabTime) AND (Q1.UnitNo =
QTime.UnitNo);


Its a little hard testing this without data, but it should work.
It will need some refining if you want to handle duplicates
 
Thanks a million Chris !!! I see the error of my ways. Your suggestion worked
great ( with some corrections to fix duplicates as you indicated). Thanks
again.

ChrisJ said:
Ok I see what's going on

Try this for your first query
Qtime
SELECT T_Issue.UnitNo, Max(DateDiff("h",[IssueDT],[CollectDT])) AS LabTime
FROM T_Issue INNER JOIN T_Results ON T_Issue.Account = T_Results.Account
GROUP BY T_Issue.UnitNo
HAVING (((Max(DateDiff("h",[IssueDT],[CollectDT])))<0));


and this for your second query
Q1
SELECT T_Issue.UnitNo, T_Results.ResultNo,
DateDiff("h",[IssueDT],[CollectDT]) AS LabTime
FROM T_Issue INNER JOIN T_Results ON T_Issue.Account = T_Results.Account;

and finally, the one you want
QMax
SELECT Q1.UnitNo, Q1.LabTime, Q1.ResultNo
FROM Q1 INNER JOIN QTime ON (Q1.ResultNo = QTime.LabTime) AND (Q1.UnitNo =
QTime.UnitNo);


Its a little hard testing this without data, but it should work.
It will need some refining if you want to handle duplicates


nataqe said:
Thanks for the reply Chris.
I tried your suggestion. However, it returns more than the Max value for
each record. For example:

Qmax actual return: Qmax should return:
Unit No LabTime No Unit No LabTime No
123 -2 8010 123 -2 8010
123 -5 8078 456 -6 19780
123 -8 8134 987 -4 30488
456 -6 19780
456 -12 20171
456 -18 20371
456 -24 20429
987 -4 30488
987 -7 30434
987 -17 29980
987 -28 29521

thanx
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

is there such a thing as a fast pivot table? (CAN they byoptimized???) 1
Query Prob....... KRISH 1
DateDiff function throwing error in Where clause 7
Aggregate Query Question 1
Update Error 1
Complex query 0
Min/Max Dates 1
Help with Query syntax 2

Back
Top