Solved

Access - Query of another Query.  Select LDetailsID Last 2 Items for Each TransactTypeID

Posted on 2013-02-02
9
285 Views
Last Modified: 2013-02-02
Hello all
Please forgive me for being so dense on these queries.  Trying to learn and understand....

Tried to do this with a Top 2  Query but it doesn't work for what I wanted.  SO.. I tried using MatthewPattricks method from an earlier question I had posted,  to select using Derived Table (??terminology??).  Anyway

I am attempting to get the Last 2 records For Each LZMachNbr
Where MPZed is True AND LZTransactTypeID is 1 (number 1)

My code gives me the Error
You Tried to execute a query that does not include the specified expression 'LZReportingID' as part of an aggregate function. ????????????????

When I open the original query the field LZReportingID is in fact there.   I even opened that query and copied and pasted the name into this code to make sure I hadn't typoed.

My Code is:
SELECT t1.* 
FROM qry_LZReportingDetailsMPZ_Step1_BusDateAdj t1 INNER JOIN
(SELECT MAX(t2.LZReportingDetailsID) AS MaxDetailID, t2.LZReportingID, t2.LZMachNbr, t2.LZTransactTypeID, t2.LZTransactAmt, t2.MPZed, t2.ShiftID, t2.LZReportingTypeID, t2.LZReportingDate, t2.LZReportingBusDate, t2.VoidLZReporting, t2.LocID, t2.BeginTime, t2.AdjReportingDateTime
FROM qry_LZReportingDetailsMPZ_Step1_BusDateAdj t2
WHERE t2.MPZed = True AND t2.LZTransactTypeID=1 
GROUP BY t2.LZMachNbr) AS X on t1.LZReportingDetailsID = X.MaxDetailID
ORDER BY t1.LZMachNbr;

Open in new window

0
Comment
Question by:wlwebb
  • 6
  • 2
9 Comments
 
LVL 16

Expert Comment

by:kmslogic
ID: 38847896
Ok your query is beyond easy repair I think.  When you use an aggregate function like MAX() or SUM() all columns that aren't part of that aggregate have to be part of a GROUP BY, so in your subquery you can't include all of these

t2.LZReportingID, t2.LZMachNbr, t2.LZTransactTypeID, t2.LZTransactAmt, t2.MPZed, t2.ShiftID, t2.LZReportingTypeID, t2.LZReportingDate, t2.LZReportingBusDate, t2.VoidLZReporting, t2.LocID, t2.BeginTime, t2.AdjReportingDateTime

Open in new window


without using GROUP BY on them like

(SELECT MAX(t2.LZReportingDetailsID) AS MaxDetailID, t2.LZReportingID, t2.LZMachNbr, t2.LZTransactTypeID, t2.LZTransactAmt, t2.MPZed, t2.ShiftID, t2.LZReportingTypeID, t2.LZReportingDate, t2.LZReportingBusDate, t2.VoidLZReporting, t2.LocID, t2.BeginTime, t2.AdjReportingDateTime
FROM qry_LZReportingDetailsMPZ_Step1_BusDateAdj t2
GROUP BY t2.LZReportingID, t2.LZMachNbr, t2.LZTransactTypeID, t2.LZTransactAmt, t2.MPZed, t2.ShiftID, t2.LZReportingTypeID, t2.LZReportingDate, t2.LZReportingBusDate, t2.VoidLZReporting, t2.LocID, t2.BeginTime, t2.AdjReportingDateTime
WHERE t2.MPZed = True AND t2.LZTransactTypeID=1)

Open in new window


(also it looks like you are missing the closing paren on your SELECT)

Anyhow even with all that I don't think this is going to yield the result you want so maybe we could step back and try to solve the original problem

You say "I am attempting to get the Last 2 records For Each LZMachNbr
Where MPZed is True AND LZTransactTypeID is 1 (number 1)"

So, what does "last 2 records" mean in this context?  It implies that all the records where MPZed is true and LZTransactTypeID is 1 are sorted by something, so what are they sorted by?

SELECT TOP 2 (your list of columns)
FROM (your query)
WHERE MPZed=True and LZTransactTypeID=1
ORDER BY (whatever sort you want) DESC

Open in new window


On the desc part you want to sort in the OPPOSITE order of the way you actually want to sort so the TOP 2 will actually load the LAST 2.  Does that make any sense?
0
 

Author Comment

by:wlwebb
ID: 38847921
kmslogic
Thank you kindly for the reply!

First, using your bottom code, which is pretty much what I too had.

My SELECT TOP 2 * gave me just the Last two Items regardless of the LZMachNbr... In my feeble attempt I end up with only the top two items also.....

There could be any number of LZMachNbr... In my sample I have 5 different LZMachNbrs and each record in the table/query is assigned a number 1 to 5 with multiple entries for each LZMachNbr.

I am needing the Top two LZReportingDetailsID for each of the LZMachNbr.


Now then Going back to your assistance with the code that I have posted....
Ok so then anything I want "Selected" that doesn't have an Expression has to be also in the GROUP BY (I See - I think - It Makes sense thinking of the MS Query Builder Grid if I Click "Totals" then everything is set to GROUP BY) --- Correct????
0
 

Author Comment

by:wlwebb
ID: 38847925
The Code I adjusted my original code to, based on what I interpreted you were saying is:

With this I get Syntax error in FROM Clause and it highlights ORDER BY.????
SELECT t1.* 
FROM qry_LZReportingDetailsMPZ_Step1_BusDateAdj t1 INNER JOIN
(SELECT MAX(t2.LZReportingDetailsID) AS MaxDetailID, t2.LZReportingID, t2.LZMachNbr, t2.LZTransactTypeID, t2.LZTransactAmt, t2.MPZed, t2.ShiftID, t2.LZReportingTypeID, t2.LZReportingDate, t2.LZReportingBusDate, t2.VoidLZReporting, t2.LocID, t2.BeginTime, t2.AdjReportingDateTime
FROM qry_LZReportingDetailsMPZ_Step1_BusDateAdj t2
GROUP BY t2.LZReportingID, t2.LZMachNbr, t2.LZTransactTypeID, t2.LZTransactAmt, t2.MPZed, t2.ShiftID, t2.LZReportingTypeID, t2.LZReportingDate, t2.LZReportingBusDate, t2.VoidLZReporting, t2.LocID, t2.BeginTime, t2.AdjReportingDateTime
WHERE t2.MPZed = True AND t2.LZTransactTypeID=1)
ORDER BY t2.LZReportingDetailsID DESC;

Open in new window

0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 16

Expert Comment

by:kmslogic
ID: 38847926
I'm still having trouble picturing what you're trying to get--would it be possible to post some sample records and what you expect the output to be?  Or perhaps attach an Access database?
0
 

Author Comment

by:wlwebb
ID: 38847932
Give me a sec
0
 

Author Comment

by:wlwebb
ID: 38847943
Ok

Here is some sample data... One table dta........

I did not include all the fields......... ONLY the ones that are key to what I am doing.......
Test.accdb
0
 

Author Comment

by:wlwebb
ID: 38847948
So (Based on the Sample Db) the records that get selected should End up with is: (with all the other fields that I am going to end up with) ( For this step I need JUST the LZTransactTypeID = 5)

LZReportingDetailsID       *       LZMachNbr       *       AdjReportingDateTime
56       *       1       *       1/10/13 2:20 PM
76       *       1       *       1/18/13 6:12 PM
72       *       2       *       1/14/13 10:17 AM
77       *       2       *       1/18/13 6:12 PM
48       *       3       *       1/7/13 8:20 AM
58       *       3       *       1/10/13 2:20 PM
39       *       4       *       1/3/13 12:20 AM
49       *       4       *       1/7/13 8:20 AM
40       *       5       *       1/3/13 12:20 AM
75       *       5       *       1/14/13 10:17 AM


Copy the list to Excel and you can Text to Column on the *
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 38848064
In your sample DB you can use this query:
SELECT dta.LZReportingDetailsID, dta.LZMachNbr, dta.AdjReportingDateTime
FROM dta
WHERE (((dta.LZReportingDetailsID) In (SELECT TOP 2 q0.LZReportingDetailsID
FROM dta as q0
WHERE (((q0.LZMachNbr)=dta.LZMachNbr) AND ((q0.LZTransactTypeID)=5) AND ((q0.MPZed)=True))
ORDER BY q0.AdjReportingDateTime DESC , q0.LZReportingDetailsID)))
ORDER BY dta.LZMachNbr, dta.AdjReportingDateTime;

Open in new window

0
 

Author Closing Comment

by:wlwebb
ID: 38848076
ALS

THAT WORKED PERFECT!!!!!!!!!!!!  I even converted it to my DB and success!!!!!!!

THANK YOU THANK YOU THANK YOU!!!!!!!
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

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…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

777 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