Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-02-02
9
Medium Priority
?
290 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 40

Accepted Solution

by:
als315 earned 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

618 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