?
Solved

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

Posted on 2013-02-02
9
Medium Priority
?
289 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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

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