what does 'My query only works for one employee.' mean? what is the expected outcome? what is the actual outcome?
The only thing I can see different is the relationships don't match between the two diagrams in any way
And why do you have employeeid_fk and payfrequecy in the employee table?
You would make things simpler for yourself if you had a consistent naming convention - how is someone supposed to know that a field called employeeid in one table is called salaryid in another
edit: hit the save button in error
I would suggest your employee table does not require the employeeid_fk and payfrequency fields
the payrollsalary table should have the salaryid_fk field renamed employeeid_fk
The other two tables need changing as well, but it is not clear what they are supposed to do so cannot advise. The issues are you are joining pk's to pk's which implies a one to one relationship - in which case I would expect them to be one table
There are potentially other issues - what happens if an employee changes position? or the rates change?
As Ajax has said, either your naming convention is awful or your linking of the tables is way off.
You should never have circular paths in the main table relations.
Assuming your database is called payroll - adding Payroll to the front of every table is unnecessary and confusing.
It should be tblEmployees, tblSalary etc.
Why have you got a matching fk field for the pk field in every table ??
Thanks for the information guys. I am sorry But I am very new. I have tried to clean up the tables as best I know and also tried to create the relationships but it seems as if I am not willing. I will paste what I have done so you can see. Please can you make some suggestions on how to make this work. If you need me to sent the DB so you can physically see it, I do not have a problem.
Payroll Query relationship..(I know is incorrect because no information at all populates the query)
Database Table relationship (Must also be very incorrect as it is the same as the query relationships)
Apologies guys but I am stumped and I am just trying to learn from you
The first thing which hurts the eye - you pay salaries to salary table not to employee?
Rename the table PayrollPayrollTbl to tblEmployeePayroll, and link it with PayrollEmployeeTbl (which will be more conventional when renamed to tblEmployees).
When employees salary never changes, then you can calculate monthly salary based on working time in tblEmployeePayroll and rates in tblEmployeeSalary. When the rates can change, then read the current rate value from tblEmployeeSalary into tblEmployeePayroll (p.e. using combo with single column), and make further calculations only based in tblEmployeePayroll. Anyway, look at the table tblEmployeeSalary as some kind of registry - you can relate it to tblEmployees, but that's all. Anyway, no need to estabilish relationship between tblEmployeePayroll and tblEmployeeSalary.
Thank you Guys....you are all my Hero's
Might I suggest some homework reading before you delve further into your db?
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html
and/or
http://holowczak.com/database-normalization/
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm
The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.