Access as data warehouse (data transformation)

  • Thread starter Thread starter Piotr
  • Start date Start date
P

Piotr

Hi,
Im using access for data warehousing, my data are imported from
different databases from txt format.
The problem which I can not resolve is hot to make some data
transormation.
Perfect solution would be to have in ine table, something like
deffinitions like:

ID1 ID2 Company name
01 2546 Comapny1
02 4587 Company2
03 .....

then in table with data imported I wish to do:
find rekords where id = id2 then change id to id1, it wouldnt be
difficult if it wouldnt be neccessary to make something like loop.
Maybe you have some more experience with data transformation in
access, maybe my way is not optimal.

regards
Peter
 
Piotr said:
Hi,
Im using access for data warehousing, my data are imported from
different databases from txt format.
The problem which I can not resolve is hot to make some data
transormation.
Perfect solution would be to have in ine table, something like
deffinitions like:

ID1 ID2 Company name
01 2546 Comapny1
02 4587 Company2
03 .....

then in table with data imported I wish to do:
find rekords where id = id2 then change id to id1, it wouldnt be
difficult if it wouldnt be neccessary to make something like loop.
Maybe you have some more experience with data transformation in
access, maybe my way is not optimal.

regards
Peter
Click to expand...

Suppost your tables are Companies (containing the ID1, ID2, and [Company
Name] fields described above), and Imports (containing the field ID and
presumably other data). You want to change ID to ID1 where it currently
matches ID2, correct? An update query along these lines ought to do it:

UPDATE
Imports INNER JOIN Companies
ON Imports.ID = Companies.ID2
SET Imports.ID = Companies.ID1;
 
Piotr said:
Hi,
Im using access for data warehousing, my data are imported from
different databases from txt format.
The problem which I can not resolve is hot to make some data
transormation.
Perfect solution would be to have in ine table, something like
deffinitions like:

ID1 ID2 Company name
01 2546 Comapny1
02 4587 Company2
03 .....

then in table with data imported I wish to do:
find rekords where id = id2 then change id to id1, it wouldnt be
difficult if it wouldnt be neccessary to make something like loop.
Maybe you have some more experience with data transformation in
access, maybe my way is not optimal.

regards
Peter
Click to expand...
 
Back
Top