Solved

Access07 - Query to get Last Records Except THE very last record

Posted on 2014-01-26
9
291 Views
Last Modified: 2014-01-27
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
Comment
Question by:wlwebb
  • 5
  • 3
9 Comments
 

Author Comment

by:wlwebb
ID: 39811614
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
 
LVL 30

Expert Comment

by:hnasr
ID: 39811905
Upload an example.
Show the expected output.
0
 

Author Comment

by:wlwebb
ID: 39811942
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 10

Expert Comment

by:Gozreh
ID: 39812413
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
 

Author Comment

by:wlwebb
ID: 39812462
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
 
LVL 10

Expert Comment

by:Gozreh
ID: 39812475
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
 

Author Comment

by:wlwebb
ID: 39812495
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
 
LVL 10

Accepted Solution

by:
Gozreh earned 500 total points
ID: 39812559
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
 

Author Closing Comment

by:wlwebb
ID: 39812612
That was what I was looking for.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question