Using Last in Query

[vgersghost]

I have a query where I need to use last record, but the problem is this prevents the records form being updatable. Add / Update / Delete. Query is the recordsource for a form.

I have tried sub queries, seperate and in the exsiting query to no avail.
Any guidence would be apprecited.

thx

Attached:

 

[Guus2005]

select * from table1 where fieldid in (select last(fieldid) from table1)

This should do the trick!

Enjoy!

 

[vgersghost]

Thank you

Thats works great for one record, but I need all the records for one clockid showing the last date for each program code.

I tried a few options, but all i get is errors.

 

[Guus2005]

post your db or a part of it.

 

[vgersghost]

I thought I had on the first message. Oops!!

Thanks for your help

 

[Brianwarnock]

Note that if you want the last date use MAX not Last, Last is to do with the last chronologically entered record, they may not be the same.

Brian

 

[Brianwarnock]

SELECT tblTrainingRecords.ClockID, tblTrainingRecords.ProgramCode, Max(tblTrainingRecords.ActivityDate) AS MaxOfActivityDate
FROM tblTrainingRecords
GROUP BY tblTrainingRecords.ClockID, tblTrainingRecords.ProgramCode;

This will give you the last(max) date per program code per clockID i called this
lastofprogforclockid

If you want to get info from the other table , say the descrition then this should do the trick.

SELECT tblTrainingRecords.ID, lastofprogforclockid.ClockID, lastofprogforclockid.ProgramCode, lastofprogforclockid.MaxOfActivityDate, tblTrainingPrograms.Description
FROM tblTrainingPrograms INNER JOIN (lastofprogforclockid INNER JOIN tblTrainingRecords ON (lastofprogforclockid.MaxOfActivityDate = tblTrainingRecords.ActivityDate) AND (lastofprogforclockid.ProgramCode = tblTrainingRecords.ProgramCode) AND (lastofprogforclockid.ClockID = tblTrainingRecords.ClockID)) ON tblTrainingPrograms.ID = tblTrainingRecords.ProgramCode;

Brian

 

[vgersghost]

Thank you

I understand how to get max and last now, but the problem still remains, when you run this queries they are not updatable. Add / Update or Delete. Iwould like to be able to add to the list. Seeing what the last date it was held.

 

[vgersghost]

Any Other Suggestions

Any more ideas on how to make these queries updatable?

thx dmh

 

[Brianwarnock]

I don't know the correct answer to your question as I have had no ACCESS training and thus have no in depth insight , however my gut instinct tells me that ACCESS will not let you modify the results of a query obtained by filtering, whether that be using Joins, criteria , or aggregate functions , as to change the data would alter the query results and thus... , well it just doesn't seem logical.

Brian

 

[Rabbie]

This link explains when a query is read-only.

 

[vgersghost]

Thanks.

Read the the link. That make sense. I'll just create am seperate entry form. I was hoping I could show the last date on this form and then add a new entry.

thanks for your help

 

[Brianwarnock]

Thanks Rabbie.
I think the answerto the problem is to use a temporary table.
Create a new query, a make table query

SELECT tblTrainingRecords.ID, tblTrainingRecords.ClockID, tblTrainingRecords.ProgramCode, tblTrainingRecords.ActivityDate INTO bjw
FROM lastofprogforclockid INNER JOIN tblTrainingRecords ON (lastofprogforclockid.ClockID = tblTrainingRecords.ClockID) AND (lastofprogforclockid.ProgramCode = tblTrainingRecords.ProgramCode) AND (lastofprogforclockid.MaxOfActivityDate = tblTrainingRecords.ActivityDate);

This table will be identical in format to the original but contains only the required entries with max date, this can then be joined to the original table on ID and all other fields will be updateable.

Brian

 

[Darren Bartrup]

Sorry, haven't had a look at your database but an inital idea is to use UPDATE and DELETE queries based on your select query?

If I can (read as if my other half lets me) I'll log on at home tonight and take a look.

 

[vgersghost]

Not sure?

Tnak you Darren Bartrup and Brianwarnock for your assitance.

BrianQarnock, I'm not sure how the temporary table would work. I can see how it duplicates information, but how would the original table get update or joined.

 

[ajetrumpet]

Ghost,

Have you considered doing this work in Visual Basic. It seems like that would make this process a whole lot easier! Unless of course I misunderstand. Every one of your posts says to me...

"I want to query records by the last date entered grouping them by the clockID and ProgramCode combination (which Brian has already done). Then I want to use the query like a table and add data to it."

May I ask, for what reason? I didn't see where you mentioned that it had to be done this way. Why do you need this query and not just the table as the recordsource for a form? By reading everything so far, I'd say that no matter what you are needing to do, there is more than one way to do it.

I understand how to get max and last now, but the problem still remains, when you run this queries they are not updatable. Add / Update or Delete. I would like to be able to add to the list. Seeing what the last date it was held.

You can filter any record(s) in a form without trying to use a query. You can probably see the last date (whatever you mean by this) using this method anyway. Just a few things to think about...

 

[Brianwarnock]

I said

"This table (The Temp)will be identical in format to the original but contains only the required entries with max date, this can then be joined to the original table on ID and all other fields will be updateable."

However I think on rereading the thread that you want to be able to work on the whole table , not just the records with last date, but for whatever reason you want to show the last date in the grouping with each record.
I don't know whether this can be done using a sub form getting its info from the query. You could carry this info on the original table,but you would need to run an update sequence before each time you worked on the table.
However neither would be dynamic ie if you changed it during work on the table the new data would not be shown.

Brian

 

[vgersghost]

Explanation

What I have is a form with a person id number and then in the details I list the programs they have attended only showing the last date they attended. They attend these programs every year or they might miss them, so that's the reason for the last date. Then what I would like to do is click a button "Add" and this is where the problem begins. I cannot get passsed the point saying the recordset is not - updatable, because of the grouping. I can create an "Add" sub-form, but I was hoping I could just change the recodsource on the current form, add the new record, requery and then show the last date of all programs they attend once again.

Does this make sense.

Thanks for your advise and time.

 

[ajetrumpet]

Then what I would like to do is click a button "Add" and this is where the problem begins. I cannot get passsed the point saying the recordset is not - updatable, because of the grouping. I can create an "Add" sub-form, but I was hoping I could just change the recodsource on the current form, add the new record, requery and then show the last date of all programs they attend once again.

I know you can't do this, but what you CAN do is open tables and change the data in them when there is a query still opened. You can change this data, but it will not be reflected in the query that has the groupings until it is requeried. I wonder if you could use Visual Basic to open the recordset, do whatever updating you needed to do, and then requery the form to reflect the updated information? The form is based on a query, right? If so, this wouldn't be a bad way to go, and it wouldn't take long either, AND it could be used more than once when similar situations arise.