Database Help!?

Question

I'm trying to make a small task database that will record the staff members payroll. I have three tables which are :
Staff
Department
Staff Payroll

The reason why i have a seperate table for payroll because i want access to work out each staff members wages. Each department has a different pay rate and the staff table just shows the staff details.
I am having trouble with the relationship and also the layout of the tables. My problem lies here:
Many staff members can work in one department, and one department has many payrolls
Staff -< Department->Payroll
But i cant work out payroll unless i have staff details on the payroll table. But if i link staff and payroll together it has TO MAKE a one-to-one relationship. I cannot get this working, its too difficult and MS access wont allow me to make it.
Is there anyway i can change my style around so i can show working out staff Payroll with having 3 seperate tables?

I cant use SQL!!!
Its for my ICT coursework and my syllabus doesnt cover SQL. Only Basic Access!

One main problem with the one-to-many relationship is that it doesnt make sense in my database when it is with Payroll and Staff.
One staff member cannot have many payrolls!
Im doing this on a monthly bases, not yearly.

Wait a minute..........
Monthly bases????
I could have, one to many if i say that the staff members get paid weekly and show it on a monthly bases....
you just might be on to something!!!!!!!
@_@

Answer 1

You can add a table to the relationships more than once....
Access will append an underscore and a nuber to the copy.

You should be able to make a seperate one to many relationship between the payroll and staff tables.

Another option is to create a query which joins payroll information to staff information. Then establish a relationship between the query and department table.

Answer 2

Id use a database more like SQl then MS Access.

MS Access is only good for single person databases like cookbooks (Heard from a MS Employee :D )

But your best bet would be with an SQL Type database, not Access.

Answer 3

If I understand your question, what you are after is actually fairly complex to do in Access, due to its limited support for joins; you need to create a couple subqueries and join them together.

Also, you don't have quite enough information available to make the joins you need to make.

It's easier to simply do this and let you look at it / customize if for your needs, so I've made an Access database that does what I think you want. You can download it from here:

http://rapidshare.com/files/18249556/ya.mdb

Answer 4

This is my solution

Staff should have and ID which is a primary key

Each department has its own id primary key and has various staff primary keys as it's contents

Payroll has a calculation for each staff ID - it also has its own primary key