creating tables that will be a subform

[buckeye77]

It has been an extremely long time since doing anything like this in Access.... I need to create 3 different tables or could be 2.
Basically the end result is a form that shows two sections... one being advances and the other being reimbursements, therefore I thought the main form/table would simply have basic employee info and there would be 2 additonal froms (subforms)/ tables, one for advances and the other for reimbursements. Can anyone point me in the right direction to get everything linked up correctly or any suggestions?

 

[straybullet]

So, each employee could potentially have multiple advances with multiple reimbursements for each advance - correct?

If so, IMO the following should be a start:

tblEmployee
EmployeeID
txtFirstName
txtLastName
etc.

tblAdvance
AdvanceID
intEmployeeID <-FK to tblEmployee
dtAdvance
curAdvanceAmt
etc.

tblReimburement
ReimbursementID
intAdvanceID <-FK to tblAdvance
dtReimbursement
curReimburseAmt
etc.

Let them hate - so long as they fear... Lucius Accius

 

[buckeye77]

Appreciate the start... only a couple questions before I get going...
1) what is FK? relationships?
2) If we have no real employeeID would autonumber work? or should we assign, 1,2,3, etc?
3) If FK is relationships do I assign in the tables or when creating the forms, do the FK's become the linking fields?

Thanks again for the refresher, it is greatly appreciated as my memory is fuzzy.

 

[lespaul]

1. A FK is a Foreign Key. It means that it is a Primary Key in a different table. straybullet should have added PK at the end of EmployeeID and AdvanceID to make that a little more clear.
2. Yes you can assign an autonumber to create a PK.
3. not sure I understand what you are asking for here.

Maybe check out Fundamentals of Relational Database Design and Understanding SQL Joins. I would suggest adding a PaidInFull field to the Advance table to easily identify those that still have an outstanding advance.

HTH


Leslie

Have you met Hardy Heron?

 

[buckeye77]

That was my next point... either in the table or in the form when entering data, I need a field to display how much total (advances and Reimbursements) were paid for 2008, 2009, etc. Therefore, I was going to add a field named "calandaryear" because even if requested on 12/15/08, it may be for a class that starts on 1/1/09 therefore that advance needs to be applied to 2009 and not 2008.

Is there away to post this database once I get a framework up so that I could possibly get help? I do not see a "browse" to be able to pick a file in the attach section.

 

[lespaul]

The 'attach a file' allows you to basically link to a web hosted document...not a local file.

Maybe you just need to add a classStartDate field to the table design...that way you would be able to do:

SELECT year(classStartDate) FROM tableName

and get the year you need.

You can always just post textual information about your tables and structures to get feed back....

Leslie

Have you met Hardy Heron?

 

[buckeye77]

Okay.... Here is what I have so far....
tblAdvance
AdvanceID - PK - Autonumber
intEmployeeID - FK - Number
dtAdvance
CalandarYear
curAdvanceAmt
txtCourses
txtGrades
txtCourseStudy
comGraduated
txtDegree
txtComments

tblEmployee
EmployeeID - PK - AutoNumber
txtFirstName
txtLastName
txtMiddleInt
txtSuffix

tblReimbursement
ReimbursementID - PK - Autonumber
intAdvanceID - FK - Number
dtReimbursement
Calandaryear
curReimbursement
txtReason
txtComments


Now if I understand the help section correctly, I link the tables FK to PK?

 

[lespaul]

ok a few questions....what do these fields have to do with the Advance?
[tt]
txtCourses
txtGrades
txtCourseStudy
comGraduated
txtDegree
txtComments[/tt]

isn't this information about a course? Shouldn't it be in a COURSE table then?
Is the CalendarYear in Advance and Reimbursement the same thing or could you have 2009 in Advance and 2008 in Reimbursement? If it's the same thing, it should only be in ONE table.

What exactly are you doing here? Is a company paying for courses and the employee has to reimburse them?


Leslie

Have you met Hardy Heron?