Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2005
    Posts
    7

    Unanswered: Multiple DBs - IDs conflict

    Please can anyone help?

    Can anyone point me to a neat solution to this problem I am sure I could “walk” through recordsets and create new tables but is there an easier solution?

    I have 3 DBs each containing several tables. The 3 DBs have the same number and structure of tables. I am using Access 2002 and the DBs are on my own PC

    The data relates to individuals who have unique names, but in each DB they have different ID#s

    In each DB, related data is stored for each individual but the tables that store this data use the ID#s as the linking key. So we could have something like:-

    Database ONE

    Person Table
    ID# 1
    Name ABC

    Data Table 1
    ID# 1
    Tbl1 DF1 3
    Tbl1 DF2 7

    Data Table 2
    ID# 1
    Tbl2 DF1 7
    Tbl2 DF2 13

    Database TWO

    Person Table
    ID# 9
    Name ABC

    Data Table 1
    ID# 9
    Tbl1 DF1 4
    Tbl1 DF2 8

    Data Table 2
    ID# 9
    Tbl2 DF1 8
    Tbl2 DF2 1

    Database THREE

    Person Table
    ID# 46
    Name ABC

    Data Table 1
    ID# 46
    Tbl1 DF1 5
    Tbl1 DF2 9

    Data Table 2
    ID# 46
    Tbl2 DF1 1
    Tbl2 DF2 113

    I want to be able to combine and query the data from all 3 databases for each individual.

    So for example, I would like to be able to get that the total of Data Table 1; Tbl1 Data Field 1 (DF1) is 3+4+5 = 12 for person ABC. Or that the totals of Tbl2 DF2/ totals of Tbl DF1 = (13+1+113)/(3+4+5)

    Clear as mud? Anybody any experience of this?

    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    OMFG!!!! What an absolute nightmare ... Having to rely upon the name being identical across 3 datasets ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jan 2005
    Posts
    7
    Are you serious or is this irony (sarcasm)? Have I missed the glaringly obvious? If so, a gentle push would be appreciated.

    TYIA

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by TrevorD
    Are you serious or is this irony (sarcasm)? Have I missed the glaringly obvious? If so, a gentle push would be appreciated.

    TYIA
    Oh, I am quite serious ... This is the worst possible scenario. Are these names entered in independently?

    I should say that yes you can do it ... But it is "theoretically" a nightmare because of the issues of orphans (names not matching to each other - creating "false rows"). If there are no "typo issues" then your joins/matches should go smoothly.

    Then of couse I could be reading your intent wrong ... Are the 3 datasets to be merged into 1? The people are not duplicates across the 3 sets are they?
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Now, if this is just a merge, then it's simple... Just read each ID for m the dataset and create a new one for the wirtes in the new dataset ...
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Jan 2005
    Posts
    7
    Quote Originally Posted by M Owen
    Now, if this is just a merge, then it's simple... Just read each ID for m the dataset and create a new one for the wirtes in the new dataset ...
    Thanks for your time. In answer to the question in the previous post, there is no danger of typos. The 3 databases collect data automatically on the same people and there is no possibilty (realistically) that the "Name" could be wrong. So if ABC exists in DB1 and in DB2 but NOT in DB3 then the names would be identical for sure in DB1+2 but the ID#s which link to the other tables in those DBs would be different. There would NOT be an occurrence of a person called ABC in DB3 since all names over all the DBs must be unique.

    The data continues to be updated in the 3DBs so a "one off merge" is not possible. However, since I am not analysing the data in real time, I would have no problem linking to the DBs and creating a new merged DB on the fly so to speak and query that one instead EXCEPT I am not sure that I understand your quote above. To "just read each ID" sounds like a "walk through" which I am sure i could do but as you say "it's simple" and I think the walk through may not be you seem to have seen a solution that eludes me.

    Could you put me straight please

    Thanks again

    Trevor

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Again, You didn't define your intent ... I was operating under the assumption that this was to be a 1 time combination of the 3 datasets (no going back)... This is for ad-hoc querying/reporting where you'll repeatedly pull the data from the 3 LIVE active datasets right?

    Here's the nightmare of keying on names: You have Lovie Smith in set #1. You have him in set #2. IS THIS THE SAME Lovie Smith? Well you have have 2 different Joe Black's one from Dallas and one in Houston ... Do you combine them together?
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Jan 2005
    Posts
    7
    Quote Originally Posted by M Owen
    Again, You didn't define your intent ... I was operating under the assumption that this was to be a 1 time combination of the 3 datasets (no going back)... This is for ad-hoc querying/reporting where you'll repeatedly pull the data from the 3 LIVE active datasets right?
    Correct. However, although the 3 live datasets will continue iindependently, I only deal with the data while it is in a fixed state on my HDD. It is NOT being updated as I do my analysis. In that case, I am quite happy to create a NEW merged DB for myself to query each and very time I do the analysis work. The exisiting 3 DBs will still be sitting there on my HDD, and the next time I pull in data the 3 DBs will update ready for me to analyse again some time in the future.

    Quote Originally Posted by M Owen
    Here's the nightmare of keying on names: You have Lovie Smith in set #1. You have him in set #2. IS THIS THE SAME Lovie Smith? Well you have have 2 different Joe Black's one from Dallas and one in Houston ... Do you combine them together?
    Yes I combine them. In this scenario. These 2 Joe Blacks are without doubt the SAME person! The 3 DBs are looking at the SAME group of people. Not eveyone is in every DB but, rather like creating a user name for this forum, if someone else had already chosed TrevorD, I would be unable to choose that name.

    There are other similar issues further down the chain in the relationships as well but I thought 1 step at a time might be good since the solution to the first part might but me in the mood for the rest.

    Thanks again for your help; much appreciated

    Trevor

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Basically that is how you'll have to proceed ... Operationally, the easiest way to do this would be to round robin each dataset and assign a new ID for each person where you'd query to see if that person was already in the system prior to doing so. If so then use that ID # for the writing of the rest of that person's records ... If not, query for the other records for that person using the old ID in the source dataset and write out the records using the new ID in the new dataset ...
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    Join Date
    Jan 2005
    Posts
    7
    Quote Originally Posted by M Owen
    Basically that is how you'll have to proceed ... Operationally, the easiest way to do this would be to round robin each dataset and assign a new ID for each person where you'd query to see if that person was already in the system prior to doing so. If so then use that ID # for the writing of the rest of that person's records ... If not, query for the other records for that person using the old ID in the source dataset and write out the records using the new ID in the new dataset ...
    Gee thanks. It took me several readings to understand it but I got there

    Can all this be done using the access - select, make table and append queries?

    I am relatively inexperienced but not completely green.

    If it can then I shall have some fun and try and get it to work. If not, it is off to the code.

    Thanks for your help much appreciated.

    Trevor

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by TrevorD
    Gee thanks. It took me several readings to understand it but I got there

    Can all this be done using the access - select, make table and append queries?

    I am relatively inexperienced but not completely green.

    If it can then I shall have some fun and try and get it to work. If not, it is off to the code.

    Thanks for your help much appreciated.

    Trevor
    Well ...No. (or perhaps maybe ... I'm not sure) Given my druthers, I'd recommend using DAO or ADO to do this ... And I think in order to use make tables and stored queries, and such you'd have to link the datasets (and their tables) in in order for the query binding to work ... Just go with the DAO or ADO ... Plenty of code examples around here (mine and for DAO try izyrider) ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •