Hi,
I am trying to create a crosstab query that should only return records with the personnel's recent transfer date, recent Station and recent Division. A Divison can have more than one Station, and and a Station can have more than one personnel. The database is aimed at tracking personnel transfers from one station to another. The transfer records are held in a separate table called "T_StationsWorked."
The following are the tables:
T_Personnel
PersonnelID=PK
FamilyName
T_Divisions
DivisionID=PK
DivisionName
T_Stations
StationID
DivisionID=FK
StationName
T_StationsWorked
StWrkd=PK
PersonnelID=FK
DivisionID=FK
StationID=FK
TransferDate=Date
In the Q_Current_Station_Crosstab I set these fields as follows:
1. TransferDate
Total=Last
Crosstab=Value
2. Station
Total=Last
Crosstab=Row heading
3. Division
Total=Last
Crosstab=Row heading
The problem comes when a personnel has more than one transfers; they are not being allocated to the Station of the current TransferDate.
To see the situation, please open F_Switchboard > click Search Persons button and type in ngwira, click details button and the personnel details form will open. Please, see below the form in the Transfer History subform. That is the input for the transfers.
I would like to the recent transfer Date to post the personnel to the correct Division and correct Station, which is not being the case since, as you can observe from the personnel details form ngwira was transferred to Interpol on 03/09/2012 but when you click SHQ under the Divisions listbox on the F_Switchboard then click Interpol in the next listbox, he does not appear there, instead he is found in Division: Eastern > ?.
I would like to understand what am doing wrong. I will appreciate if you can find time to help me resolve this. See attached mdb.
joseph