Solved

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

Posted on 2013-02-02
9
288 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…

688 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