If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Merge (sort of) 2 databases into one
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. |
#2
|
|||
|
|||
Merge (sort of) 2 databases into one
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. |
#3
|
|||
|
|||
Merge (sort of) 2 databases into one
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. "Fred" wrote: 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. |
#4
|
|||
|
|||
Merge (sort of) 2 databases into one
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 "Fred" wrote: 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. |
#5
|
|||
|
|||
Merge (sort of) 2 databases into one
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 "KARL DEWEY" wrote: 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. "Fred" wrote: 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. |
#6
|
|||
|
|||
Merge (sort of) 2 databases into one
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. |
#7
|
|||
|
|||
Merge (sort of) 2 databases into one
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 "Fred" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|