Question Retrieve only current office for personnel

[josephbupe]

I am creating a personnel database. The personnel are often transferred from one office to another in different divisions around the country.

In the personnel details form, I have a subform with two cascaded combo boxes that for selecting Divisions and office of transfer, respectively. This works fine.

However, I was wondering how am going to store or query personnel currently posted to a particular office, since in my case above one personnel may have been transferred to different offices which all are listed in the subform.

By the way, my table structures are as follows and transfer information is saved to the table T_PersonnelTransfers as foreign keys:

T_Personnel
PersonnelID=Auto

T_Divisions
DivisionID=Auto
DivisionName=Text

T_Office
OfficeID=Auto
DivisionID=Number
OfficeName=Text

T_PersonnelTransfers
TransferID=Auto
DivisionID=Number
OfficeID=Number
TransferDate=Date/Time

I will appreciate any help.

Thanx

joseph

 

[Beetle]

You should be able to determine the employees current Office by looking up their most recent TransferDate (i.e. - by using the domain aggregate function DMax, or Max in a Totals query).

On a side note, having the DivisionID in T_PersonnelTransfers is a bit redundant, as it can be determined by the OfficeID.

 

[josephbupe]

You should be able to determine the employees current Office by looking up their most recent TransferDate (i.e. - by using the domain aggregate function DMax, or Max in a Totals query).

On a side note, having the DivisionID in T_PersonnelTransfers is a bit redundant, as it can be determined by the OfficeID.

I appreciate your response and advice.

Could you kindly have a look at my table structures and relashionship and advise further.

Thank you.

joseph

 

[Beetle]

If you want to upload a copy of your db, with any sensitive data removed, I can take a look at it. If you do, it will need to be in .mdb (A2003 or earlier) version.

 

[josephbupe]

Hi Beetle,

Thanxs alot.

I have attached a copy. My intention is to link a subform whose record source will be based on the crosstab quiry and link it to the form Form1.

I will appreciate your advise.

Regards.

joseph

 

[Beetle]

OK, I took a look at your db but I need some clarification on your organizational structure. You have not established a relationship between T_Offices and any other table, so it's unclear how it fits into the hierarchy. Also, your PersonnelTransfers table has no field for OfficeID (only DivisionID and StationID).

You said you want to retrieve which personnel are currently assigned to each office, but the way your tables and relationships are currently structured that's not possible because there is no relationship between Personnel and Offices (i.e. - they are assigned to Divisions/Stations, not Offices).

What I would normally think about an organizational structure like this is;

1) A Division will have one or more Stations.
2) A Station will have one or more Offices.
3) Personnel will be assigned to one of these Offices.

However, I have no way of knowing if that's actually the case here, so I can't give you advice on how to proceed without more details.