Extraction, Transform, Load (ETL)

[LynnEsther]

There are multiple Access databases within my organization. Two of them are 1) a budget application, and 2) a project management application. Each was built and is maintained by a different department. The same people, however, use both applications. There are enough differences in the business processes to justify keeping the databases seperate. However there are certain data in the project management application that can be pushed to the budget application so that the user doesn't have to do duplicate entry. What are some best practices for "pushing" the data. Creating a flat file and dumping it from one database to another? Creating a linked table? I've been tasked with coming up with a way to do this, but I'm not sure where to begin. Any insights, lessons learned would be very much appreciated. Thanks in advance.

 

[Call_Me_Sam]

Lynn,

I would agree with Pat about this. When i first joined my current company, they set up databases simply for the sake of setting them up with no accounting for duplication or redundancy. Within months i had demonstrated the commanality of the data, and their sources, and redesigned the system so that when new databases are being designed their roots are linked back to a central back end database.

Our systems is based around magazine data (sales, supply etc) so having one table with unique title fields meant everyone was talking the same language, and benefited from the system being constantly up to date instead of relying on some'one importing a title list and ensuring consistancy.

As long as the tables are linked using the original names, the seperate systems should work fine.