value of other field of a specific record according to "Last" function 1

[thevillageinn]

I have a query in which I use the "last" and "group by" functions to limit the results. I want to return the value of another field in the desired "last" record, but I have not been able to figure out how to return that value...I assume I need to use "where" or "expression" but am unsure how to go about referencing or calling it.

Any assistance would be greatly appreciated.

-Dan

 

[scriverb]

If the field is in the same table then simply also use the Last function as that field is also in the Last record for the group.

I may be oversimplifying your problem but I think that it will give you the desired results.

Bob scriver

 

[thevillageinn]

Thanks for your response...no I don't think you are over-simplifying. However, I have tried using the various functions..."last" "first" "min" and "max" on my test data and was unable to come up with the correct values. This is what has me in a rut.

I need a function something to the effect of "equivalent" or something. Any other ideas?

-Dan

 

[scriverb]

Why don't you copy and paste your SQL from the query and let me take a look at it.

Bob Scriver

 

[thevillageinn]

Sure thing... here's my SQL.

I have a table tblLRAttempts where I keep an entry each time a specific customer number is 'worked'. I also keep the error code that prompted our work, and a time stamp, as well as the action that was performed.

I have another query (Query1-1 (not my name choice)) which is a SQL pass-through query. this query links together a few tables, and provides the customers who have errors. I have a left join so that I get all results from Query 1-1 and matching results from tblLRAttempts. Customers who are in tblLRAttempts but not in Query 1-1 are ignored and don't show up we assume their issue has been resolved.

I am using "Max" to return the latest time stamp entry, but using "Max" or "First" or "Last" on LastOfAction doesn't return the corresponding entry like I would expect it to. it returns some of the correct values, but other records show incorrect values. This is the part that has me the most confused.

SELECT DISTINCT [Query1-1].CustNo, [Query1-1].UserId, [Query1-1].DateStatus, [Query1-1].StatusCode, [Query1-1].SlsManNo, tblLRAttempts.TCSI, Max(tblLRAttempts.Date) AS MaxOfDate, Count(tblLRAttempts.Action) AS CountOfAction, Last(tblLRAttempts.Action) AS LastOfAction
FROM [Query1-1] LEFT JOIN tblLRAttempts ON [Query1-1].UserId = tblLRAttempts.Wtn
GROUP BY [Query1-1].CustNo, [Query1-1].UserId, [Query1-1].DateStatus, [Query1-1].StatusCode, [Query1-1].SlsManNo, tblLRAttempts.TCSI;

 

[scriverb]

As modified call this one qrySelectMax:

SELECT DISTINCT [Query1-1].CustNo, [Query1-1].UserId, [Query1-1].DateStatus, [Query1-1].StatusCode, [Query1-1].SlsManNo, Count(tblLRAttempts.Action) AS CountOfAction, Max(tblLRAttempts.Date) AS Date FROM [Query1-1] LEFT JOIN tblLRAttempts ON [Query1-1].UserId = tblLRAttempts.Wtn
GROUP BY [Query1-1].CustNo, [Query1-1].UserId, [Query1-1].DateStatus, [Query1-1].StatusCode, [Query1-1].SlsManNo;

Here I have eliminated all the extra fields that you need from tblLRAttempts except the Max of Date and CountOfAction. This recordset now represents the records that you want displayed less the other fields that you need.

Now create another query that takes in the query above. You can call it anything you want:

Select [qrySelectMax].CustNo, [qrySelectMax].UserId, [qrySelectMax].DateStatus, [qrySelectMax].StatusCode, [qrySelectMax].SlsManNo, [qrySelectMax].CountOfAction, [qrySelectMax].Date, tblLRAttempts.TCSI, tblLRAttempts.Action FROM qrySelectMax INNER JOIN tblLRAttempts ON [qrySelectMax].UserID = tblLRAttempts.Wtn and [qrySelectMax].MaxOfDate = tblLRAttempts.Date;

I have not tested this but what I am attempting to do is use the first query to select the Query1-1 fields that you have grouped by and any of the legitimate Count and Max function items that would work correctly to create an initial recordset of selected records. Now these records have the necessary fields to link back to the tblLRAttempts to retrieve the rest of the data that is in the same row as the UserID and Max of Date row. The INNER JOIN performs this matching function in the second query. Once they are found then the fields TCSI and ACTION are added to the row.

I hope this works for you. If I have misinterpreted what you need please get back and we can make adjustments.

Bob Scriver

 

[thevillageinn]

Bob,
Thank you for your final suggestion. That was what I needed to do. I actually have 2 other queries that are driving the recordset of my final query. I was able to get the correct "Action" by joining my "Last" date query to the Date in the table, and then returning the action. I believe this is what you intended in your last post.

Thanks again for your assistance.
-Dan

 

[scriverb]

Any time Dan. Glad that I was able to help.

Bob Scriver