Problem with last record in ms access query (1 Viewer)

[VBANewBie :)]

Hello guys thanks for having me here , I have 3 Access queries , The first query (Q0) filters a table to get specific data :

    SELECT Table1.ID, Table1.Machine, Table1.Po, Table1.Priority, Table1.Zdate, Table1.Status
FROM Table1
WHERE (((Table1.Status)<>"Not Needed"));

The second query (Q1) Sorts the first query depending on multiple condition like date and priority :

 SELECT Q0.Machine, Q0.Priority, Q0.Zdate, Q0.Po, Q0.ID, Q0.Status
FROM Q0
GROUP BY Q0.Machine, Q0.Priority, Q0.Zdate, Q0.Po, Q0.ID, Q0.Status
ORDER BY Q0.Machine, Q0.Priority DESC , Q0.Zdate;

The third query (Q2) :

 SELECT Table2.MachineNumber, Last(Q1.Priority) AS LastOfPriority, Last(Q1.Zdate) AS LastOfZdate, Last(Q1.Po) AS LastOfPo, Last(Q1.ID) AS LastOfID, Last(Q1.Status) AS LastOfStatus
FROM Table2 LEFT JOIN Q1 ON Table2.MachineNumber = Q1.Machine
GROUP BY Table2.MachineNumber;

Q2 should get the last record for each machine number in the Q1 with the rest of data in the same record , So the priority should be all "1" with the right date , the right Po and the right status , Unfortunately it doesn’t happen and get the record with the last ID in each machine number .

The correct result should be like

I just need the last record from each machine number in Q1 no matter what condition because it is not always the latest date or the highest priority i need the Q2 to be Strictly tied to the last record of each machine number

Sorry For long question guys , Thanks in advance

 

[plog]

The term "last" is ambigous unless you tell us how to define "last". In a table of data, there is no order. No first, no last, no next, no 43rd. You need to use a better term than "last" to describe what you want. Perhaps "Maximum value", "the record with the latest date", etc. Last only exists in context.

With that said, you did provide expected results which is very helpful. So I am going off of that for what you expect. Let me try and put into english what you expect--and please correct me where I am wrong:

For every unique Machine in Table1 you want the smallest priority value, the earliest Zdate, the smallest Po, the smallest ID and if any of the Statuses are "In Progress".

Is that correct? If not, please corect me where I am wrong without using the term "last".

 

[plog]

Technically this query will give you the expected results:

SELECT Table1.Machine, Min(Table1.Priority) AS MinOfPriority, Min(Table1.Zdate) AS MinOfZdate, Min(Table1.Po) AS MinOfPo, Min(Table1.ID) AS MinOfID, Table1.Status
FROM Table1
WHERE (((Table1.Status)="in progress"))
GROUP BY Table1.Machine, Table1.Status;

It's just that I am not certain its figuring the Status correctly. Better sample data would demonstrate what you want better.

 

[The_Doc_Man]

Looking at your query Q1, it has both ORDER BY and GROUP BY but no aggregate SQL functions. Therefore, it really doesn't do much other than the sort, and it might even be overkill since an ORDER BY sequence will have the effect of a GROUP BY but will involve less work for the DB engine.

You are trying to then bind a table and a query together using that LEFT JOIN in Q2 - but the order of that query's evaluation in joining will be "left to right" and thus the order of presentation will be that of the table, not of Q1. (Q1 is on the RIGHT, not the LEFT). So you lost the order of the Q1 sort when you did what you did. As Paul has pointed out to you, tables are not sorted in any particular way. (There is a long-winded technical reason for this but we don't need to go that deep.) Just understand that the order of your records, because they will come from the table, isn't what you expected.

I think you could do away with a lot of this layering to get your intended results - though I have to ask whether you actually have two tables or were you just a bit sloppy? Because one of the queries uses Table1 but another uses Table2 yet they appear to be based on the same data. For us to correctly understand your intent, this needs to be clarified.

I saw while I was typing that Paul offered you something to consider as a way to get your answer in a single query. If that Table1/Table2 issue was just being unclear and it's all the same table, then his suggestion should come close to what you want. At worst change his WHERE clause from ="InProgress" to <>"Not Needed" and you should be golden.

 

[VBANewBie :)]

Is that correct? If not, please corect me where I am wrong without using the term "last".

Thanks for reply , For Every unique machine in table one i need the smallest priority if there are any differences in priority number , it could be the same priority if it is the case the result should be the latest date , if the case is the same priority with the same date then the identifier would be the ID number >> So if the priority is the same and the date is the same so it returns the record with biggest ID
The status and Po Number are irrelevant in this example .

 

[VBANewBie :)]

I think you could do away with a lot of this layering to get your intended results - though I have to ask whether you actually have two tables or were you just a bit sloppy? Because one of the queries uses Table1 but another uses Table2 yet they appear to be based on the same data. For us to correctly understand your intent, this needs to be clarified

Thanks for your reply , there are actually two tables one for machines numbers with some relevant information , and the other for Po’s related to the machines , About my example it is just example the original database is way complicated than this and no duplicated tables don’t worry about that .

About my method Q1 is just for sorting in specific way as you noticed just to order the table data in a way make it easy to understand what i need which is as i previously mentioned in my thread .

I hope we could get some help with that since i am average user of access not that advanced.

 

[plog]

Sorry, I can't parse your description. I think sample data is the best way to communicate what you want. Please provide 2 sets similar to what you did in your initial post. I need:

A. Starting data from your table. A sample database like the prior post will suffice.

B. Expected results. Show me what data you expect to end with--just like you did in your initial post.

Be sure to include a more robust dataset than the first one so that you can demonstrate the issue. If you check out my query you will find it produces the exact result you want using your first set of sample data. However, I don't believe your first set of data includes enough sample to demonstrate all the cases. So find a case for which my query fails and include it in your sample data and show what you expect to have returrned by the query.

 

[VBANewBie :)]

Sorry, I can't parse your description. I think sample data is the best way to communicate what you want. Please provide 2 sets similar to what you did in your initial post. I need:

A. Starting data from your table. A sample database like the prior post will suffice.

B. Expected results. Show me what data you expect to end with--just like you did in your initial post.

Sorry for your troubles , I will put something together with expected result for each condition , But tomorrow because i will black out right now

 

[VBANewBie :)]

A. Starting data from your table. A sample database like the prior post will suffice.

Hello , Sorry for late reply
Please find attached my original table TblPoDtls with big variety of data , You will find a table named Result this table has the desired result for 6 machines not all but still covering all conditions .
You also will find a remark in MyRemarks Field in table result explaining why this result.
Thanks for your help

If you need more explanation about the main idea of the database read the following If not just ignore it :
We have 40 Machines in our factory , We need to know the status of every machine is it already producing (Po Status InProgress) or it has finished the Po and waiting for new Po (Po Status Done) , In some cases the Po could be Paused and begin in following one then return to the paused one to resume production that’s why the date isn’t the main identifier But Priority1 InProgress which is unique in every machine (the machine can’t proceed in two Po’s in the same time obviously) , in the previous case if we depend on the date the result will always show the Po with the latest date which is wrong in the previous mentioned case (when we return to the paused Po to resume production the result will stay the following Po with latest date which will be Priority2 and correct Po with Priority1 won’t be in the result report) , Hope my explanation was good enough to give you the bold lines i work on . Thanks So MUCH

 

[plog]

Here's what I have for the criteria:

You want to find 1 record per machine from TblPoDtls which does not have "Studying" as its status. That record has the lowest priority, if there is a tie then use the latest Zdate, if there is still a tie you want the record with the highest ID value.

You will need 3 subqueries to do this. Here's the SQL of the first one:

SELECT TblPoDtls.Machine, Min(TblPoDtls.Priority) AS LowestPriority
FROM TblPoDtls
WHERE (((TblPoDtls.Status)<>"Studying"))
GROUP BY TblPoDtls.Machine;

Paste that into a query and name it 'sub1'. It identifies the lowest priority of each machine. Then,

SELECT sub1.Machine, sub1.LowestPriority, Max(TblPoDtls.Zdate) AS LatestZdate
FROM TblPoDtls INNER JOIN sub1 ON (TblPoDtls.Priority = sub1.LowestPriority) AND (TblPoDtls.Machine = sub1.Machine)
WHERE (((TblPoDtls.Status)<>"Studying"))
GROUP BY sub1.Machine, sub1.LowestPriority;

Paste that into a query and name it 'sub2', It identifes the latest Zdate in sub1. Then,

SELECT TblPoDtls.Machine, Max(TblPoDtls.ID) AS HighestID
FROM TblPoDtls INNER JOIN sub2 ON (TblPoDtls.Zdate = sub2.LatestZdate) AND (sub2.LowestPriority = TblPoDtls.Priority) AND (TblPoDtls.Machine = sub2.Machine)
WHERE (((TblPoDtls.Status)<>"Studying"))
GROUP BY TblPoDtls.Machine;

Paste that into a query and name it 'sub3'. It finds the ID of the actual records you want. Then finally

SELECT TblPoDtls.*
FROM sub3 INNER JOIN TblPoDtls ON sub3.HighestID = TblPoDtls.ID;

Will produce the results you want. Again, test it and if you find a condition it doesn't work for, give me sample data to demonstrate it.

 

[VBANewBie :)]

Here's what I have for the criteria:

Will produce the results you want. Again, test it and if you find a condition it doesn't work for, give me sample data to demonstrate it.

Works like charm thank you so much for your help and your patience