Hi all,

I am using access 2000 and been having a difficulty producing accurate results that give the last matched item in the "SHOW last matched applications" query.

The data format is in the form

Req #
Type
Request Item
Status



The code is shown below shows all the queries used that have been broken down.

Please help.


Access query


SELECT T1.*
FROM [Excel 8.0;HDR=YES;IMEX=1;Database=C:\Access.xls].[Access$A1:U65536] AS T1;



Inactive applications


SELECT T1.*
FROM [Excel 8.0;HDR=YES;IMEX=1;Database=H:\Inactive Apps.xls].[Inactive Apps$A1:U65536] AS T1;



Adds n Modify query


SELECT Access.[Req #], Access.Type, Access.[Request Item], Access.Status
FROM Access
WHERE (((Access.Type)="Add") AND ((Access.Status)="Complete" Or (Access.Status)="Pending")) OR (((Access.Type)="Modify") AND ((Access.Status)="Complete" Or (Access.Status)="Pending"));



Deletes


SELECT Access.[Req #], Access.Type, Access.[Request Item], Access.Status
FROM Access
WHERE (((Access.Type)="Delete") AND ((Access.Status)="Complete" Or (Access.Status)="Pending"));

Adds n Modify without matching deletes


SELECT [Adds n Modify].[Req #], [Adds n Modify].Type, [Adds n Modify].[Request Item], [Adds n Modify].Status
FROM [Adds n Modify] LEFT JOIN Deletes ON [Adds n Modify].[Request Item] = Deletes.[Request Item]
WHERE (((Deletes.[Request Item]) Is Null));



Adds n Modify without matching deletes without inactive applications


SELECT Last([Adds n Modify Without Matching Deletes].[Req #]) AS [LastOfReq #], [Adds n Modify Without Matching Deletes].Type, [Adds n Modify Without Matching Deletes].[Request Item], [Adds n Modify Without Matching Deletes].Status
FROM [Adds n Modify Without Matching Deletes] LEFT JOIN [Inactive Apps] ON [Adds n Modify Without Matching Deletes].[Request Item] = [Inactive Apps].[Request Item]
GROUP BY [Adds n Modify Without Matching Deletes].Type, [Adds n Modify Without Matching Deletes].[Request Item], [Adds n Modify Without Matching Deletes].Status, [Inactive Apps].[Request Item]
HAVING ((([Inactive Apps].[Request Item]) Is Null));



SHOW last matched applications
************************************************** *********************

SELECT Last([Adds n Modify].[Req #]) AS [LastOfReq #1], [Adds n Modify].Type, [Adds n Modify].[Request Item], [Adds n Modify].Status, Last(Deletes.[Req #]) AS [LastOfReq #], Deletes.Type, Deletes.[Request Item], Deletes.Status
FROM [Adds n Modify] INNER JOIN Deletes ON [Adds n Modify].[Request Item] = Deletes.[Request Item]
GROUP BY [Adds n Modify].Type, [Adds n Modify].[Request Item], [Adds n Modify].Status, Deletes.Type, Deletes.[Request Item], Deletes.Status
ORDER BY Last([Adds n Modify].[Req #]) DESC , Last(Deletes.[Req #]) DESC;