Solved

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

Posted on 2013-02-02
9
282 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 16

Expert Comment

by:kmslogic
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:wlwebb
Comment Utility
Give me a sec
0
 

Author Comment

by:wlwebb
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
ALS

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

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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now