wlwebb
asked on
Access07 - Query to get Last Records Except THE very last record
Hello all
Yesterday I posted a question regarding needing a query to pull all of the last records of every itemID that is ActiveYN = Yes.
My table [t_Dtail] has fields
DetailID, tdate, ItemID, ActiveYN
MGozreh suggested:
Both worked.....
HOWEVER. Now I have to modify it
My Fields will now be:
DetailID, InvID, tdate, ItemID, ActiveYN
Now I have to get all of the Last Records for Each [ItemID] Where ActiveYN is True EXCEPT FOR any DetailID that has the LAST [InvID] (which is the Primary Key in another table.
Yesterday I posted a question regarding needing a query to pull all of the last records of every itemID that is ActiveYN = Yes.
My table [t_Dtail] has fields
DetailID, tdate, ItemID, ActiveYN
MGozreh suggested:
SELECT t_Dtail.ItemID, Max(t_Dtail.tdate) AS MaxDate
FROM t_Dtail
WHERE (((t_Dtail.ActiveYN)=True))
GROUP BY t_Dtail.ItemID;
Capricorn1 suggested: select a.*
from t_dtail as a
inner join
(select max(b.tdate) as maxtdate, b.itemid
from t_dtail as b
group by b.itemid,b.ActiveYN
having b.ActiveYN= true
) as c
on a.itemid = c.itemid and a.tdate=c.maxtdate
Both worked.....
HOWEVER. Now I have to modify it
My Fields will now be:
DetailID, InvID, tdate, ItemID, ActiveYN
Now I have to get all of the Last Records for Each [ItemID] Where ActiveYN is True EXCEPT FOR any DetailID that has the LAST [InvID] (which is the Primary Key in another table.
Upload an example.
Show the expected output.
Show the expected output.
ASKER
OK.
Upload
Expected Result is for InvID it would Not use the Max which in this case is
for ItemID
1 - 24
2 - 24
5 - 24
6 - 24
3 - 23
4 - 23
Thus the Max ItemID with ActiveYN of TRUE Excluding the MaxInvID is
1 - 21
2 - 21
3 - 22
4 - 22
5 - 22
6 - 22
EE-Exclude-Max-Rec.accdb
Upload
Expected Result is for InvID it would Not use the Max which in this case is
for ItemID
1 - 24
2 - 24
5 - 24
6 - 24
3 - 23
4 - 23
Thus the Max ItemID with ActiveYN of TRUE Excluding the MaxInvID is
1 - 21
2 - 21
3 - 22
4 - 22
5 - 22
6 - 22
EE-Exclude-Max-Rec.accdb
Hi
I'm not sure i understand what you need, but maybe this will help you
Do you want to remove the last DetailID from the InvID, or the Last InvID from the ItemID ?
M Gozreh
I'm not sure i understand what you need, but maybe this will help you
SELECT t_Dtail.ItemID, Max(t_Dtail.tdate) AS MaxDate
FROM t_Dtail LEFT JOIN (SELECT Max(t_Dtail.DetailID) AS MaxOfDetailID, t_Dtail.InvID
FROM t_Dtail
GROUP BY t_Dtail.InvID) AS LastDetail ON t_Dtail.DetailID = LastDetail.MaxOfDetailID
WHERE (((t_Dtail.ActiveYN)=True) AND ((LastDetail.MaxOfDetailID) Is Null))
GROUP BY t_Dtail.ItemID;
Do you want to remove the last DetailID from the InvID, or the Last InvID from the ItemID ?
M Gozreh
ASKER
nGozreg,,,
Close... I was looking for the Max ItemID for the Max INVID EXCLUDING whatever the MaxInvID is for each ItemID
With ActiveYN=True
So That's remove the Last INVID
Close... I was looking for the Max ItemID for the Max INVID EXCLUDING whatever the MaxInvID is for each ItemID
With ActiveYN=True
So That's remove the Last INVID
Try this
SELECT t_Dtail.ItemID, Max(t_Dtail.tdate) AS MaxDate
FROM t_Dtail LEFT JOIN
(SELECT Max(t_dtail.InvID) AS MaxInvID, t_dtail.ItemID
FROM t_dtail
WHERE (((t_dtail.ActiveYN)=True))
GROUP BY t_dtail.ItemID) AS LastInvID
ON t_Dtail.InvID = LastInvID.MaxInvID
WHERE (((t_Dtail.ActiveYN)=True) AND ((LastInvID.MaxInvID) Is Null))
GROUP BY t_Dtail.ItemID;
ASKER
CLOSER.....
That gives me the list but when I add t_dtail.[InvID] to the results (which I need) I get all records other than the last record (which I wanted excluded) But I only wanted the LAST (Max) tdate for each InvID (again, excluding the last one)
Hope the above made sense
Seems like you almost have to have two inner joins or left joins (but I REALLY don't know)
One to get the Max date for every INVID then using that MaxDate for Each INVID selecting the Last INVID Excluding the one for Each InvID found in step 1
That gives me the list but when I add t_dtail.[InvID] to the results (which I need) I get all records other than the last record (which I wanted excluded) But I only wanted the LAST (Max) tdate for each InvID (again, excluding the last one)
Hope the above made sense
Seems like you almost have to have two inner joins or left joins (but I REALLY don't know)
One to get the Max date for every INVID then using that MaxDate for Each INVID selecting the Last INVID Excluding the one for Each InvID found in step 1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That was what I was looking for.
ASKER
Open in new window
BUT, it doesn't like the way I write it .. ;-(
Getting an Error on My JOIN clause
I also think I have to have a WHERE clause in there to say to Select a where a.InvID is NOT the z.MaxInvID