Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009
    Posts
    5

    Unanswered: Really Stuck With This Query!

    I'm building a database to compare data form various sources.

    My problem is, depending on the source, the same data can be represented in different ways.

    For example. I wish to compare the value of DataA from SourceA and SourceB.

    The trouble is, SourceA identifies DataA as "data_a" and SourceB identifies DataA as "d_a"

    Is there any way to use a database to do this?

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    russellhq, the obvious answer is to get all the data consistent.

    If that is impossible, One way that popped into my mind is a cross reference table. But you would have to know all the combinations of matching values. For example (an this is by no means the only or even the best way):

    An table with columns

    SourceA, SourceB, SourceC
    data_a, d_a, Data-A

    SELECT columns
    FROM SourceA_tab
    , XReftab
    , SourceB_tab
    WHERE XReftab.SourceA = 'data_a'
    AND Xreftab.SourceB = SourceB_tab.col-name

    Or an XRefTab table with columns

    Common_key, Source, Value
    1, A, data_a
    1 B, d_a
    1 C, Data-A
    2 B, xxx-y
    2 C, Xxx_Y
    2 D, XXX-y

    SELECT *
    FROM XRefTab A
    , XRefTab B
    , SourceB_tab
    WHERE A.Value = 'data_a'
    AND A.Common_key = B.Common_key
    AND B.Source = 'B'
    AND B.VALUE = SourceB_tab.col-name

    I hope you get the idea. And to repeat, they may be other, better methods.

  3. #3
    Join Date
    May 2009
    Posts
    5
    To give you and example of size.

    There may be 200-1000 data items each collected from maybe 20 sources.

    And across the 20 sources, the data item could be spelt the same or 7-8 different ways.

    I came up with this last night, it was using the update query.

    First off I had the table, :
    [tbl_data]
    ID, SOURCE, DATA_NAME, DATA_VALUE
    1, SOURCEA, DATA_A, 1.4455
    2, SOURCEB, D_A, 1.4455
    3, SOURCEC, DATAA, 1.4455
    4, SOURCEA, DATA_B, 1.4455
    5, SOURCEB, D_B, 1.4455
    6, SOURCEC, DATAB, 1.4455

    Then I created the next table:
    [tbl_names]
    ID, GENERIC_NAME, SOURCEA_NAME, SOURCEB_NAME, SOURCEC_NAME
    1, DATA-A, DATA_A, D_A, DATAA
    2, DATA-B, DATA_B, D_B, DATAB

    Next I made an update query as follows;

    UPDATE tbl_data INNER JOIN tbl_names ON tbl_data.DATA_NAME = tbl_names.SOURCEA_NAME SET tbl_data.DATA_NAME = tbl_names.GENERIC_NAME
    WHERE (((tbl_data.SOURCE)="SOURCEA"));


    I made the above query for each source. And it seemed to work.


    I'm fairly new to Access and database so would appreciate your thoughts.

Posting Permissions

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