Hi!

I have two tables in a databse, one storing player related data and one
containg mission data. I need a column to auto update with the last active
date for a player by looking at the mission tables highest/latest end date.
How do I set this up (I assume it's possible).
(data to transfer is of Date/Time type).

"Thomas Andersson" <thomas (AT) tifozi (DOT) net> wrote
You do not describe how the player table and mission table are related.

On the assumption that mission table records contain a player id, then you
should be able to set up a query to retrieve the TOP 1 records * (with the
result sorted in descending order by date time) to obtain the date. But,
unless there are other factors in the "last active date" determination, it
would be better to use such a query to determine the date instead of storing
data redundantly.

* right click on the upper area of the Query Builder
screen (but not on a table or join line) and look
at Query Properties

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access


"Thomas Andersson" <thomas (AT) tifozi (DOT) net> wrote

Access Developer wrote:
The player table is related by Player ID one-to-many to the soertie table.
Sorties have start and end time and I want to copy the latest endtime to the
player table.

Hmm, I have managed to make a querie that returns the latest date for each
player (select query) using the max() function to select from the endtime
list for each player (so query output shows player id, current player table
time (empty) and the latest time for that player from sortie table.
I run and it looks fine.
Convert to update query
Under the last active column in the designer on "update to" I add
[Sortie].[Ended] (which is where the data come from).
I thought that would be it but when I hit run it says it's going to update
672 rows (which is the number of sorties) ; NOT 15 which is the current
number of players in the db.. so I never dare running it as I don't want to
trash the sortie db.

Am I missing something here or doing something seriously wrong?
Sure, I could run the select querie and manyally copy the dates from right
to left column, but that will get tiresome fast as the db grows...

"Thomas Andersson" <thomas (AT) tifozi (DOT) net> wrote in
news:8c0thkFo3gU1 (AT) mid (DOT) individual.net:

should NOT be stored in the player table. You use the select query to
display that data in Forms, Reports, Data Exports and other Queries.

You will need to run the update query every time you change a record
in the soertie table. If that is forgotten, or the query fails to
complete because thare is a locked record, your data will be wrong.


You can also use the Dmax() function to retrieve the data for a given
player.

You could use dmax("ended", "sortie", "[player ID] = " & [players].
[player id]) in the "update to" box of a query based only on the
players table.