Matching like accounts from different databases

[sue1127]

I have information from two different databases containing $$ amounts from different periods. One database has information from 1995 through 1999, the other has information from 2000 through 2001. I would like to get them combined so that for each account that exists in both data bases, the amounts would match up in the same row. There are, however, accounts that existed in the first time period that no longer exist, and vice-versa. Is there a way I could get the information into an Access database so that I could print out a report with the like accounts matched up, and the unlike accounts just printing on separate lines? (Or, if not an Access database, does anyone have any suggestions on any other method to accomplish this goal)?

Thanks very much.

Sue

 

[coachdan]

Access will allow you to import data from other types of databases. What kind of database are the 2 databases you refer to in your post?

 

[sue1127]

We have a Sybase database, and also an old legacy system. What I'm not sure about is how to combine the information from these 2 databases in a report so that accounts which exist in both databases will appear on the same row in an Excel spreadsheet(or in any other kind of report).

Thanks,

Sue

 

[pdldavis]

Hi, I think you could accomplish something like you are describing using a query, assuming that you import both of your tables into an Access db and want to keep the tables separate. Assuming both tables have something in common, eg. a SSN, construct a query and import both tables. I'd join them on 'SSN' and create an expression in a query field similar to this:

calc: IIf([tblexpense]![ssn]=[temp]![ssn],[tblexpense]![ssn] & " - " & [temp]![ssn],[temp]![ssn])

This would list out in one string everything that is equal and list [ssn] by itself if there is nothing matching.

If you are interested in combining the tables, you could run a MakeTable Query or you could Append one to the other.
You might also have a look at the other query wizards - Find Unmatched & Find Duplicates. They may be of use to you.

In Excel, you can use the Cocatenate function to accomplish something similar.

I'd need a bit more information to go much further than this. Hope that helps.