Query to return only recent transfer data

[josephbupe]

Please, allow me to ask this question again which has gone un answered since the first post.

I have three tables namely:

T_Personnel
personnelID=PK
FamilyName

T_Division
DivisionID=PK
DivisionName

T_Stations
StationID=PK
DivisionID=FK
StationName

I am recording personnel transfer history in the following table

T_PersonnelTransfers
TransferID=PK
PersonnelID=FK
DivisionID=FK
StationID=FK
TransferDate

Now please, i need to know how i can create a query that will return only the latest transfer date along with corresponding DivisionName and StationName for a given Personnel.

Joseph

 

[AnthonyE]

Hey Joseph,

I'm sure there is a better way, but you could probably make two queries.
The first would be off of T_personeltransfers
Do an aggregate query with max of TransferDate, and group by PersonelID
Then create a new query using the same table and first query. Connect the personelID and Transferdates connect the rest of the tables up so you can pull the names instead of pks and you should be good.

Hope this helps or points you in the right direction

Anthony

 

[YNWA]

Max function transfer date and max on TransferID?

 

[bob fitz]

Max function transfer date and max on TransferID?

IF TransferID is an Auto-Number type then I don't think you guarantee that it will alway be greater that the last saved record, only that it is unique.

 

[josephbupe]

Thanx people.

Please, remember that a Person could have more than one transferDates to different divisions/stations. Hence, I want the query to include the following fields based on the maximum (recent) TransferDate:

PersonnelID--TransferDate---DivisionName-----StationName
1 -------------22/09/2012 -----Northern --------- Station 1
2 -------------18/09/2012 -----Southern ----------Station 2
3 -------------11/08/2012 -----Western -----------Station 3

The query works well when only returning the TransferDate as a Max. But including the others fields with a Max on each seems to mix up the records by not returning corresponding DivisionName and StationName to a given TransferDate

Any help will be appreciated?

Joseph

 

[AnthonyE]

Did you try the two query approach? Create the first one like I said max of transfer date and group by PersonnelID. Run the query you should see the most recent transfer date for each PersonnelID with no duplicates.

Create a query with the T_personneltransfers and the new query. Join the two fields that are the same between them, and add the rest of columns from T_personneltransfer and you should have a good skeleton to start connecting the other tables. No need to group or max the new query

Hope that helps

Anthony

 

[josephbupe]

Hi Anthony,

I have followed your instructions and the first part worked where i created a query to group PersonnelID and TransferDate without duplicate. Fine.

I have also created another query Q_PersonnelTransfers from T_PersonnelTransfers that holds all transfers. Then I joined the two queries: Q_Recent_TransferDate and Q_PersonnelTransfers on PersonnelID, but the PersonnelID is duplicated even before I can think of adding more fields from other tables.

See attached mdb file, please.

Joseph

 

[MSAccessRookie]

Did you try something like this?

[B]Q_PersonnelTransfers:[/B]

SELECT TransferID, PersonnelID, DivisionID, StationID, Max(TransferDate) As MaxTransferDate
FROM T_PersonnelTransfers
GROUP BY PersonnelID, TransferID, DivisionID, StationID

[B]NEW_Query:[/B]
 
SELECT Q_PersonnelTransfers.TransferID, T_Personnel.FamilyName, T_Division.DivisionName, T_Station.StationName, Q_PersonnelTransfers.MaxTransferDate 
FROM (((Q_PersonnelTransfers INNER JOIN T_Personnel ON PersonnelTransfers.PersonnelID=T_Personnel.PersonnelID) INNER JOIN T_Division ON PersonnelTransfers.DivisionID=T_Division.DivisionID) INNER JOIN T_Station ON PersonnelTransfers.StationID=T_Station.StationID)

 

[AnthonyE]

Joseph,

Posting from my iPhone so can't open file.

When you connect the two queries, connect by both TransferDate and PersonnelID, that will eliminate duplicates.

You are almost there.

Anthony

 

[josephbupe]

Hi Anthony,

It worked! Thank you so much for helping me out.

Stay well.

Joseph

 

[AnthonyE]

Great! glad to hear you got it working.

Anthony