Another set of eyes

[goslincm]

Hi, I'm still trying with this but think I'm closer. I have the following tables set up:

Tbl!Employee: Emp_id, Emp_lname

Tbl!Audit: Audit_id, audit_type, audit_auditee_Emp_id

Tbl!Expense: Exp_id, exp_type, exp_amount, exp_date

Tbl!Junction: Audit_id, Exp_id

When I create my forms and start entering information, it appears to be working correctly but when I look at where the information goes into my tables, I don't see my audit_id and exp_id going into the junction table. So, do I even need this table? When I look in table view, and click on all the "+" signs, employee table shows audit table and audit table shows nothing under exp_id

when I click on the "+" on the audits table, again nothing under exp_id

when I click on the "+" on the expense table, nothing under audit_id

What am I missing??????

 

[lespaul]

when you enter the expense report you need to have a process that captures the Audit_ID from the form and the expense_ID you just entered and insert that information into the junction table.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[goslincm]

Any suggestions on the type of process, or an example? I'm very new at all this, limited experience (as I'm sure you can see).

Are you saying I need a new field in my expense table or is this something I do from the forms area?

 

[Darrylles]

Hi goslin,

You need to understand more about relational database concepts, although your tables look ok.

I think that what you are intending needs to be done via subforms:

frmEmployee holds tblEmployee
frmAudit holds tblAudit
frmJunction holds tblJunction
frmExpenses holds tblExpenses

frmEmployee is the parent form.
frmAudit is a subform on frmEmployee
frmJunction is subform on frmAudit
frmExpenses is a subform on frmJunction

As you drop each subfrm on it's parent, Access will ask for the key fields on each form.

Regards,

Darrylle




tblEmployee needs to be on the outer form
tblAudit on a subform


Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[Darrylles]

Goslin,

Sorry for last 2 lines of my post above - (missed these on editing reply).

P.S. Don't know if this is even possible, but do not name your tables tbl!name - leave the exclamation point out.

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[goslincm]

Thanks for the form layout. I'm not certain I need, or at least I'm not seeing the immediate purpose of the table Junction. If I remove it everything seems to tie together.

I'm not sure why it even needs to be amongst all the forms, does it?

I'm trying and trying to learn more about relationship database concepts. I do appreciate everyones help out here.

 

[PHV]

Have a look here:

http://r937.com/relational.html

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

I'm not seeing the immediate purpose of the table Junction

I believe from one of your earlier posts you indicated that a single person could have multiple expenses from multiple audits, that's the immediate purpose of the junction table.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[goslincm]

Lespaul, okay I thought that was why the immediate purpose of the junction table, I just don't know why when I look at everything in table view, and click on the "+" I don't see anything in that table.

Sorry for my inexperience here.