Grouped Query

[Guest]

I need some helpe with a grouped query I have. Here is the SQL:

SELECT Trim([unit_id]) AS NT_unit, qryNT.test AS NT_test, Last(qryNT.result)
AS LastOfresult, qyrNT.unit_type, Last(qryNT.run_date) AS LastOfrun_date,
qryNT.status AS NT_status INTO tblNT_Copy
FROM qryNT
GROUP BY Trim([unit_id]), qryNT.test, qryNT.unit_type, qryNT.status;

This query is based on a pass through query to FoxPro tables. I need all
of these fields in the local table and I want only one record for each unit
with the latest run_date. This issue is that "result" could have 2
different values. This query works except that it can mis-match the result
value to the wrong record.

Is there a better way to do this? I have tried other designs but the
resulting report runs very slow.

Thanks
LeAnn

 

[Tom Ellison]

Dear LeAnn:

Last() is not "most recent" but is relatively random in which row it
chooses.

SELECT Trim([unit_id]) AS NT_unit, test AS NT_test,
result, unit_type, run_date, status AS NT_status
INTO tblNT_Copy
FROM qryNT Q
WHERE run_date = (SELECT MAX(run_date)
FROM qryNT Q1
WHERE Q1.unit_id = Q.unit_id)

This will return ALL the records from each unit_id that have the "most
recent" (or future!) date in run_date. Could there ever be more than one
row for a unit_id that has this "most recent" date? If so, how would you
choose which one to show?

Tom Ellison

 

[Guest]

My mistake - meant to change to MAX(qryNT.run_date).

I tried your excellent suggestion. I'm new to sub-queries and just glanced
at an article today. It worked perfectly!

Thanks so much

Dear LeAnn:

Last() is not "most recent" but is relatively random in which row it
chooses.

SELECT Trim([unit_id]) AS NT_unit, test AS NT_test,
result, unit_type, run_date, status AS NT_status
INTO tblNT_Copy
FROM qryNT Q
WHERE run_date = (SELECT MAX(run_date)
FROM qryNT Q1
WHERE Q1.unit_id = Q.unit_id)

This will return ALL the records from each unit_id that have the "most
recent" (or future!) date in run_date. Could there ever be more than one
row for a unit_id that has this "most recent" date? If so, how would you
choose which one to show?

Tom Ellison

I need some helpe with a grouped query I have. Here is the SQL:

SELECT Trim([unit_id]) AS NT_unit, qryNT.test AS NT_test,
Last(qryNT.result)
AS LastOfresult, qyrNT.unit_type, Last(qryNT.run_date) AS LastOfrun_date,
qryNT.status AS NT_status INTO tblNT_Copy
FROM qryNT
GROUP BY Trim([unit_id]), qryNT.test, qryNT.unit_type, qryNT.status;

This query is based on a pass through query to FoxPro tables. I need all
of these fields in the local table and I want only one record for each
unit
with the latest run_date. This issue is that "result" could have 2
different values. This query works except that it can mis-match the
result
value to the wrong record.

Is there a better way to do this? I have tried other designs but the
resulting report runs very slow.

Thanks
LeAnn

 

[neha choudhary]

hi i am neha