• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

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:
SELECT t_Dtail.ItemID, Max(t_Dtail.tdate) AS MaxDate
FROM t_Dtail
WHERE (((t_Dtail.ActiveYN)=True))
GROUP BY t_Dtail.ItemID; 

Open in new window

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 

Open in new window


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.
0
wlwebb
Asked:
wlwebb
  • 5
  • 3
1 Solution
 
wlwebbAuthor Commented:
I have attempted this code:
SELECT a.*
FROM 
t_Dtail AS z 
INNER JOIN 
(t_dtail AS a 
INNER JOIN 
(SELECT max(b.tdate) AS maxtdate, b.itemid FROM t_dtail AS b 
INNER JOIN 
(SELECT Max(w.InvID) as MaxInvID FROM t_dtail AS w 
GROUP BY b.itemid, b.ActiveYN 
HAVING b.ActiveYN=True)  AS c 
ON (a.tdate = c.maxtdate) AND (a.itemid = c.itemid)) 
ON (a.InvID = w.MaxInvID);

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
0
 
hnasrCommented:
Upload an example.
Show the expected output.
0
 
wlwebbAuthor Commented:
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
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
GozrehCommented:
Hi

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;

Open in new window


Do you want to remove the last DetailID from the InvID, or the Last InvID from the ItemID ?

M Gozreh
0
 
wlwebbAuthor Commented:
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
0
 
GozrehCommented:
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;

Open in new window

0
 
wlwebbAuthor Commented:
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
0
 
GozrehCommented:
SELECT t_Dtail.InvID, 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.InvID;

Open in new window

0
 
wlwebbAuthor Commented:
That was what I was looking for.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now