2 databases or 2 separate relationships?

[Mike]

I am building a database for my reciprocity items...
Pension and Welfare departments. For each departments,
depending on the union money gets reciprocated back to a
particular fund office. If I keep it as two tables
(Union and Fund Office) where one fund office can have
several unions reporting to them.

Things get messy if I dont separate between Pension and
Medical as separate databases. One fund office could
accept both Pension and Medical from a particular union
OR Fund Offce may only accept a union's medical, but for
that same union the pension go to a separate fund
office. I tried to normalize this without much luck.

Now the question... the user would enter an amount in the
Union table and then a summation query will total what
amount is going to a particular fund office. The user
will also need to work with pension and welfare at the
same time. Will access allow for two separate
relationships? Would it be best to design this as two
separate databases, if so can this be done within Access
or would I need to create this in VB?

If you had the patient to read this whole thing and have
any suggestions. Thanks a lot in advance!

Mike

 

[Tim Ferguson]

I am building a database for my reciprocity items...
Pension and Welfare departments.

I'm afraid I don't understand most of the technical language here, but some
things you say worry me.

If I keep it as two tables
(Union and Fund Office) where one fund office can have
several unions reporting to them.

Generally a table doesn't hold a thing -- it's things, one per record. In
particular, it's a bad idea to spread one thing into separate tables. The
golden rule is Exactly One Type of Thing in Exactly One Table.

Things get messy if I dont separate between Pension and
Medical as separate databases. One fund office could
accept both Pension and Medical from a particular union
OR Fund Offce may only accept a union's medical, but for
that same union the pension go to a separate fund
office.

Once the design is correct, then you can build the business rules to
implement this kind of thing

I tried to normalize this without much luck.

Normalisation is about identifying entities (types of things) to base
tables on. The kind of things that you have mentioned so far are
FundOffices, Unions, Pensions (thus, I guess, People and Payments too),
Medicals. I don't have any idea how these fit together, and there are bound
to be others too, but that is kind of where you should be starting.

All the best


Tim F