open to suggestions (1 Viewer)

[AN60]

I will be creating an Employees db with the following spec;
Initially two tbls;
1. tbl.Employees
EmployeeID (PK) autonumber
FirstName
MiddleName
LastName
Address
EmployeeCode (unique code)
Superannuation
TFN
DOB

2 tbl. Job Details
JobID(PK) autonumber
FirstName
LastName
EmployeeCode (drop down list, Unique code as in tbl. Employees)
JobDescription (combo)
DailyPay
Tax
Allowances
Anyother related Fields

Job Details tbl. will only need to extract employee names and unique code from the Employees tbl. When I select an employees unique code in Job Details, Access will then fill in the employees first and last name (from Employees tbl). How should I do that? Any other suggestions would be appreciated as I am at the beginning now & this is the best time to do it right.

 

[Mile-O]

Well, I don't know the full structure and/or issues/reasons but:

Drop the first name and last name from the second table. You don't need to duplicate it.

Drop the EmployeeCode from the second table. Replace it with EmployeeID from the first table. Relate these in the Relationships window.

 

[AN60]

M-O-P
The first tbl is to be for part time employees details which are necessary for good record keeping, an enter and forget (almost) thing for use at end of financial year purposes. The second tbl is a detailed record of the above employees daily payments & brief job description.

Thank you for the advice, I followed it and it is working ok using a one to many & entering data with main form (part tbl1)+ sub (tbl2).

I'm using a combo to select employees on the form. When I select the employee their name only remains visible until I move to another record or save/exit. Upon return to any employee's job details the combo is empty. It still links the data ok, the combo field just empty on the form. What can I do to keep that text visible in the combo, I'm sure it is only a combo property thing?

 

[AN60]

adendum

I have constructed my tbl's as above. Now I have to add a third tbl OR incorporate three "Tax payment" fields in the Job Details tbl? I've tried both ways and am prevented entering data due to the need to enter a record in Employees tbl. I really do not want to add anything in the Employees, so it's got to be one of the two earlier choices? My current link is a one to many with Employees being the one side. Any suggestions on what I should do & which links?

 

[pcEars]

I'm using a combo to select employees on the form. When I select the employee their name only remains visible until I move to another record or save/exit. Upon return to any employee's job details the combo is empty. It still links the data ok, the combo field just empty on the form. What can I do to keep that text visible in the combo, I'm sure it is only a combo property thing?

Is your combobox bound to a field in your table? if not, it will not record/remain visible, and hence the message that you need to enter a record in the Employees table.

 

[AN60]

PCE
Thanks, it was a bound/unbound thing with the combo, but only on the visibility problem.

 

[AN60]

Unfortunately i am having problems with the "old" linking again so if you guys don't mind I will elaborate a bit here. I now have currently two tbls as follows;

Employee tbl
Employee_ID
FirstName
MiddleName
LastName
DOB
TFN
Address
One or two other minor detail type fields.

Job Record tbl
JobRecord_ID
Employee_ID
date
job
hoursAtWork
dailypay
TaxHeld (yet to be paid on employees behalf )
Superannuation (yet to be paid on employees behalf)
Netpay

These two tables are linked one to many with Emlpoyees being One. They both work 100%.

I now need to introduce three extra pieces of information to complete the puzzle. They are;
TotalTaxPaid (tax which the Employer pays on behalf of all employees)
TotalSuperannuationPaid. (similar to above)
Date (of payments)

The Tax & Super payments are totals of the fields in Job Record tbl. They (new fields) do NOT have to be linked to any individual employee. That's where I have trouble. If I add the taxPaid & SuperPaid into Job record tbl the one to many link wants an entry in the Employees tbl. If I add a New tbl containing the new fields I can't get a proper link established?
I'm not sure where to go from here????