One to Many relationships 1

[JimMacD]

I hope this does not sound like a stupid question, but I can't seem to properly establish a relationship between tables in an employee leave database.

For example every employee will have several leave types, such as annual, sick, maternity, etc. My employees table contains all the data on the employee, and the individual leave tables incluse the start and end dates, the amount of leave earned, and the amount of leave taken. I want to use the employee name and number in the main form, and the individual leave table in three sub forms.

I must link the names table to each of the leave tables (I assume) but I can only get it to work with one link, not all three.

What might I be doing wrong?

Thanks

JimMacD

 

[Spellman]

Could you post the table structures that you have. I'm not quite sure I follow. Mark

The key to immortality is to make a big impression in this life!!

 

[montrose]

I would suggest eliminating the different types of leave tables and using one lookup table as a descriptor to use in validation of the leave type. Something like:

tlbEmployees
EmployeeID (PK)
EmpFstName
EmpMidName
etc

tblEmpLeave
LeaveID (PK)
EmployeeID (FK from tblEmployees)(get your 'manys' here)
LeaveType (or LeaveID)
LeaveStart
LeaveEnd
etc.

tlkpTypeEmpLeaves
LeaveTypeID (if you want-do not neccessarily need to use a primary key in a lookup table, but can be more efficient with alot of records)
LeaveType (maternity, sick, annual, whatever)

This is a little more efficient/normalized, less redudant data, allows for unlimited one to many between tblEmployees and tblEmpLeave. Otherwise, every time you have to enter leave information, you'll have to go to a different form instead of just one Leave form where your use a combo box to 'look up' to tlkpTypeEmpLeaves. To get reports, you'll only need one qry sorted by leave type or employeed id. You'll be able to group by and sum in one query vs one for each type of leave. If you happen to get extra leave time added, you can just add this new type of leave to the lookup tbl without having to add new forms, qrys, etc. I hope I've understand the gist of your design, hth. Montrose
Learn what you can and share what you know.

 

[JimMacD]

Thank, it's a lot clearer now!

JimMacD

 

[montrose]

You're welcome Learn what you can and share what you know.