Relationship Help

M

Michael

I have three tables. Trip, Expenses and Check. What I want to do is to
allow someone to open up the trip form and input all their expenses in a
subform. Later on when that person gets their reimbursement check for their
trips I would like them to open up the Check form and select all the expenses
that the check covers. The problem I am running into is that I can't figure
out the relationship the Check table has with the other two tables so that I
can have a subform on the Check form that shows all the expenses from the
different trips. I have a 1 to Many relationship between Trip and Expenses.
Thank you in advance
 
D

DevalilaJohn

Michael,

You might want to try the reverse approach, namely have the user indicate on
the expense record which check covered it. I'm going to assume that at a
minimum you have a common field on Trip and Check, namely the employee. I
can't make the same assumption about the individual expenses (they may be
tagged to trips).

You might try building an enhancement for your existing trip/expense form
for a Check ID field and let the user fill it in via a drop down listing of
checks issued to them.

With that in place, you can display results, summary or detail via reporting.

Should the above not do, you would need to duplicate the trip/expense
relationship on the check side because it is another one to many relationship.

Hope that helps.
 
R

Roger Carlson

Actually what you have is a many-to-many relationship between Trips and
Checks, with Expenses providing a natural linking table. Therefore, you
will create a 1:M between Trip and Expenses and an 1:M between Checks and
Expenses. Something like this:

tblTrip tblExpenses tblCheck
======= ========== ========
TripID----| ExpenseID |----CheckID
other... |--< TripID | other fields...
CheckID>----|

Create a form based on Trip with a subform for expenses for creating the
expenses and another based on Check with a subform for expenses to apply
checks to expenses.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
M

Michael

Thank you very much for your reply. I did what you suggested ( I have a 1 to
Many relationship between Trips and Expenses and a 1 to Many relationship
between Checks and Expenses) but I still have one issue that I am still
having is the subform on the check form. Everytime I try to create an
Expense Subform in the Check form, the subform doesn't show anything even
though there are several expenses that were entered. My guessing is that
since there haven't been any checks entered there can't be any expenses
related to them. However my problem is that when I start to enter in the
check I can pick from all the expenses that have been incurred from the trips
that were input from the Trips form. Thank you again for your time and help.
 
M

Michael

You're right about the check covering more than 1 trip. I went about
creating the subform a few different ways. 1) I created a subform with just
the Expense table and included every field (including tripid (child field to
Trip Table) and checkid (child field to Check Table). The result was no
expenses were showing in the subform. 2) I made a Query that had the Expense
Table and the Trip Table in which I used a few fields in the Trip Table and
all of the Expense fields. When I ran the query I got all of the expenses.
However when I put it into a subform on the Check Form, I get nothing. 3) I
made a Query that had all 3 tables but when I ran it, nothing was showing up.
As soon as I took out the Check Table and re-ran it, the expenses were
showing up. Again the relationships are TRIP-Expense 1:M and Expense-Check
M:1. Thank you once again.
Eric
 
R

Roger Carlson

Sorry for the delay in responding. I was unavailable Thursday and Friday.

I've been trying to fit the traditional Form/Subform structure into this
problem, and I cannot. Here's a couple things I would try:

1) Create your main form on the Check table. Base your subform on a query
of the Expense table but exclude any records that have a value in CheckID.
DO NOT create any linking fields. This will give you a subform with just
the unassigned expenses. Have an unbound check box on the subform and
attach code that sets the CheckID to the Expense table to the value from the
mainform and deletes it if the value is unchecked.

2) Create your form/subform as above, but instead of attaching code to the
checkbox, have a button that runs an update query that sets all the checked
records in the subform to the appropriate CheckID. This has complications
in that you'd have to take into account if people uncheck some, then run it
again and so forth.

I hope this gets you started.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
M

Michael

Thank you very much Mr. Carlson

Roger Carlson said:
Sorry for the delay in responding. I was unavailable Thursday and Friday.

I've been trying to fit the traditional Form/Subform structure into this
problem, and I cannot. Here's a couple things I would try:

1) Create your main form on the Check table. Base your subform on a query
of the Expense table but exclude any records that have a value in CheckID.
DO NOT create any linking fields. This will give you a subform with just
the unassigned expenses. Have an unbound check box on the subform and
attach code that sets the CheckID to the Expense table to the value from the
mainform and deletes it if the value is unchecked.

2) Create your form/subform as above, but instead of attaching code to the
checkbox, have a button that runs an update query that sets all the checked
records in the subform to the appropriate CheckID. This has complications
in that you'd have to take into account if people uncheck some, then run it
again and so forth.

I hope this gets you started.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
Click to expand...
 
Top