That part of it is out of the scope of this application. We have
proprietary software that handles the employer payment and all that good
stuff.

A very non-technical person will be doing the data entry. So entering via
the datasheet would be bad, very bad (this guy is amazed by word wrap). I
am using your suggestion with the form. I think this is working with the
initial testing.


"rpw" wrote in message
...
So, in addition to all the rest of the criteria, the worker (or the
employer)
pays into the appropriate fund. The funds are transferred to the
appropriate
administrative FundOffice (if necessary) and then remitted to the
LocalUnion
who can then pay out to the worker if the time comes. I don't think
there's
enough tables. By the way, who's doing the data entry?

Hmmm. Beer sounds good. Maybe I'll have one when I get home in about 3
hours.

"Squid" wrote:

Regarding the question on the purpose of this... This database will be
used
for a FundOffice to compute payments due to foreign fund offices. Funds
(pension, medical, etc) are remitted back to a local union's fund office.
Say someone from NewYork local union 978 works in the Pittsburgh
juristiction. His benefits are reported to the area he is working in
(Pittsburgh Fund Office - Steeler Country!). The Pittsburgh Fund Office
will reciprocate the money back to local union's fund office(s). The
foreign fund office can administer various funds for various local
unions.

Need a beer yet?

"rpw" wrote in message
...
Hi guys,

Thanks for your comments Tim.

tblFundLocal 'this would be the junction table...
Represented by CanAdminister in my diagram.
Awkward, isn't it? It's hard (in Access) to constrain this table to
allow
only ONE medical office and ONE pension office per LocalUnion.

Ahhh, good point - one I didn't consider. However, after reading your
comments, I went ahead and built the tables. It's not hard to make
those
constraints if you create a unique indexing (nulls OK) on the
combination
of
the LocalUnionID and RelTypeID fields of the table.

Using two
FKs to implement the one-to-many's, though, makes the Transactions
harder
to control.
If I understand you right, that's why I went with an autonumber PK
(placeholder) in this table.

tblRelationshipType
Probably redundant, depends on what else needs to be known about
Relationships

I don't see how this would be redundant. I put this table in for two
reasons. First, there is the possibility that in the future more fund
types
might be handled. Currently only two - pensions & medical - but Strike
funds
comes to mind and there might be others. Second, the 'relationship
type'
ID
can be used in the indexing of the junction table (so as to avoid
duplicate
or multiple relationships).

I'll try to illustrate the relationships:

FundOffice RelType LocalUnion
\ 1:m |1:m /1:m
\-------- tblFundLocal-----/
|1:m
tblTransactions

With this table setup I was able to duplicate Squid's example. Here's
the
data from the junction table (names replace the IDs for visual).

FundLocalID FundOfficeID LocalUnionID RelTypeID
1 A 76 Pension
2 A 76 Medical
3 A 55 Pension
4 B 55 Medical
5 A 12 Pension
6 B 23 Medical

tblTransactions
TransactionID (PK)
FundLocalID (FK) 'establishes Local Union, Fund Office, &
Type
TransactionDate
TransactionAmount
Using a FK to reference the FundLocal (CanAdminister) table means that
you
can't make a medical payment to a pension-only union.

??? Maybe I missed something somewhere, or I'm misunderstanding you. I
read
the problem as a method of tracking the union's remittance of funds to
a
fund
office. If payouts from the fund office are done, why would a medical
payout
be paid to a union that wasn't participating in the fund? Or are you
anticipating future possibilities?

That is probably a more robust requirement than the
only-one-office-per-union rule which
could plausibly change in the future (what do I know about pensions? I
just pays me money every month!). This probably is the way to go. As a
purist, I would probably argue about using natural keys rather than
all
these (e.g FundLocalID) placeholders, but it's not a very important
point.

Yeah, no sense starting a debate eh? ;-)

All the best

Tim F

Right back at ya!