var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
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?
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
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
FROM XRefTab A
, XRefTab B
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.
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, :
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:
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
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.