Merge (sort of) 2 databases into one

[PS]

I am puzzled as to how to proceed, I'm hoping someone can give me guidance or
suggestions. Please bear with my long-winded explanation as there is a lot
going on here.

I have an Address database containing our Clients, our client's clients
(which we refer to as EndUsers) and their respective Contacts, as well as the
Contract number a contact/address might be associated with. It is noteworthy
to mention that not all the addresses are contractually related, but may be a
vendor or other type of address. (Also, this was the first DB I ever worked
in and I fell into the trap of using Contract1, Contract2, Contract3, etc.)

I am now trying to build a Contract database; thus far all the relationships
in this DB are one-to-one - all the tables of information are governed under
the contract number. I should mention also, that this was brought into
Access from a FileMaker Pro version dating back to 1990 - so there are
pre-existing records. Because of this, I had to copy the contract number
into each table when I separated the flat file, so all the information would
have a connection. This has obviously created redundant information.

A Contract has a Client address and an EndUser address; and sometimes not
only 1 of each. The Clients and EndUsers are often repeat customers.
Additionally, there may be several contacts at each of these locations who
have worked with us on prior contracts.

I am uncertain how to bring only the records I need from the Address DB into
the Contract DB and avoid the Contract1, Contract2, Contract3, etc. trap.

I know I can query the Addresses that have applicable Contracts assigned to
them, but I'm not sure how to setup the relationship once the records are in
the Contracts DB - and how do I now get rid of the trap I've set for myself?

Essentially, I'm lost at this point. I understand (somewhat) the mechanics
of how to get the data in, but I'm baffled as to how to related them to what
I already have.

Any guidance will be greatly appreciated.

Thanks so much.

 

[Fred]

Hopefully someone else will answer on this also, here's a few thoughts.

It looks like you are setting up a pretty cool and complex CRM/Contract
management system. Hopefully someone responder can tackle this.....I have
a feeling that it would take a few posts to get/understand the important
information. And then a transition from an old work done by someone else
makes it even more fun/complicated than designing it from scratch.

I'd suggest starting by thinking about what table structure you want to end
up with, and use that as your "lighthouse" to work everything else towards.
Figure out what the main "entities" are that you are databasing, and what
type of relationships there are between them. Start by answering these
questions in "outside world" terms and THEN in table/relationship terms.
Only about 30% of your post was even on this topic, and even that 30% kind of
hopped around rather than making the core statements. Post this info if you
need help to design a good table structure.

There's my 2 cents, hopefully others will have stuff to say.

 

[KARL DEWEY]

Here is a penny and half.
It seems to me that Clients, our client's clients (which we refer to as
EndUsers) and their respective Contacts are just three groups of people that
all have the same data - name, address, phone, fax, cell, etc. So it seems
to me the they can all go into one table, just omitting any data one might
not have such as text address. So, one table, each with an unique ID.

Next maybe build a table that has a list of Relations - Contract, Client,
EndUser, Contact, etc.

Another table listing Contracts or Systems, StartDate, EndDate, etc.

They all have either an up or down or both relationship to others. Use a
Junction table - Enity_1, Enity_2, Relation (Client - EndUser), Active
(DateTime), Ended (DateTime), Contracts , etc.

Set up one-to-many relationships between the related tables.
Enity - Junction
Relations - Junction
Contracts - Junction
etc -- Junction

Use form/subform for each one-side/many-side (Joe to Moe, Benny, & Homer)
with a combo to select many-side folks.

There will be several of the one-side/many-side displays.

 

[PS]

Thank you, Fred, for your advice. There is quite a lot going on, I was even
considering not posting for that reason alone.

I've got my tables laid out and I have a good understanding of how the data
will be used in terms of reports the database will generate, as I've been
with the company for many years now.

It's how to bring it all together that has me boggled.

Thanks again for taking the time.

PS

 

[PS]

Karl,

That was a little more than 1.5 pennies. Thanks. I think I am following
you. The client, enduser and contacts are exactly as you have stated,
although I've got the contacts in a separate table and displayed as a subform
in the "Data Entry" form.

The Contracts have a lot of very specific details about the equipment,
process, fabrication and cost and I've broken them out into these separate
tables - but again, it all seems to be one-to-one - revolving around the
common contract number.

If I build a table with the Relations as you suggest aren't I then adding
redundant data, or am I just not getting what you're trying to tell me?

I have to look at my tables again. I had thought I had a pretty good
structure and all I had to do was focus bringing it all together.

The junction tables as you describe them sound like a viable solution and I
will take a look in that direction.

Thanks so much for helping me out.

-PS

 

[Fred]

I think that Karl's idea is a very sophisticated solution that will adapt to
all of the possibilities, including many-to-many relationships, and a
situation where the entities and relationships can't be defined by a pyramid
structure, with links of only a few types and (only) between adjacent layers,
and thus of only "one to many" type. The down side is that the structure
is somewhat abstract/complex for us mere mortals.

We've built a lot of similar databases where the situation 99.99% fit into a
pyramid structure and just used that approach which I suspect may the
structure that you had in mind before your post.


Either way, I think that the 30,000' view advice is to, either way, finalize
your decision on your table structure, then work everything else towards
that, including posting for help where needed.

 

[PS]

Thanks again, Fred.

When I initially started I thought the hardest part would have been getting
is successfully (and intact) out of the old database format and into Access!

It's a new day; time to learn something new.

Thanks.

PS