Prev: Hyperion Financial management (HFM), Jersey City, NJ
Next: "This file might not be safe if it contains code..." message with runtime version.
From: Thomas Andersson on 5 Aug 2010 15:52
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).


From: Access Developer on 5 Aug 2010 18:27
"Thomas Andersson" <thomas(a)tifozi.net> wrote
> 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).

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(a)tifozi.net> wrote
> 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).
>


From: Thomas Andersson on 5 Aug 2010 18:50
Access Developer wrote:
> "Thomas Andersson" <thomas(a)tifozi.net> wrote
>> 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).
>
> You do not describe how the player table and mission table are
> related.

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.

> 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.

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...





From: Bob Quintal on 5 Aug 2010 21:48
"Thomas Andersson" <thomas(a)tifozi.net> wrote in
news:8c0thkFo3gU1(a)mid.individual.net:

> Access Developer wrote:
>> "Thomas Andersson" <thomas(a)tifozi.net> wrote
>>> 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).
>>
>> You do not describe how the player table and mission table are
>> related.
>
> 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.
>
>> 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.
>
> 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...
>
You are missing the fact that such redundant max(sortie.ended) data
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.


 | 
Pages: 1
Prev: Hyperion Financial management (HFM), Jersey City, NJ
Next: "This file might not be safe if it contains code..." message with runtime version.