Comparing 2 databases

[Helen]

I have 2 databases, both based on part numbers. I need to compare the two and find if the two databases have the same part & the same letters & the same values. They are also very mixed and some parts are different in both databases, therefore they cannot all match, but I need to match up what I can, so I can then compare the databases to see if they are the same.

I am not too sure if this makes any sense, but here is an example listing:

Database 1 Database 2
Part Let Val Part Let Val
72822 aa 22 72824 cc 44
72823 bb 60 72825 dd 45
72824 cc 44 72826 ee 100
72830 ii 98 72827 ff 64
72826 ee 100 72828 gg 59
72827 ff 64 72829 hh 72
72828 gg 59 72830 ii 98

- therefore I need a formula to search for the same enteries in both databases and return the values for example, or a variance
- for example 72830 ii 98 are the same in both databases.

I hope I have explained this clearly.
Thanks.

[Tom Morales]

Helen -
Some points of clarification:

By "database" are we talking about an Excel spreadsheet with this data on it? If I understand you, you have six columns of data.
On a given row, the two sets of three columns each identify a unique part. (ie, 72822 aa 22 is a unique part that you're trying to compare with the data somewhere in the next three columns.)

Is it possible that there is more than one match for a given data set?

Tom

[ This Message was edited by: Tom Morales on 2002-02-22 11:36 ]

[Helen]

Yes I do mean data in Excel, I shouldn't really call it a database I suppose.

Yes they are unique items in each database, and I'm not too sure if there might be more than one match, but not very likely so we'll go with not.

Is this possible to do somehow with a formula? I have no clue on VBA by the way.

Thanks

[Aladin Akyurek]

On 2002-02-22 08:53, Helen wrote:
I have 2 databases, both based on part numbers. I need to compare the two and find if the two databases have the same part & the same letters & the same values. They are also very mixed and some parts are different in both databases, therefore they cannot all match, but I need to match up what I can, so I can then compare the databases to see if they are the same.

I am not too sure if this makes any sense, but here is an example listing:

Database 1 Database 2
Part Let Val Part Let Val
72822 aa 22 72824 cc 44
72823 bb 60 72825 dd 45
72824 cc 44 72826 ee 100
72830 ii 98 72827 ff 64
72826 ee 100 72828 gg 59
72827 ff 64 72829 hh 72
72828 gg 59 72830 ii 98

- therefore I need a formula to search for the same enteries in both databases and return the values for example, or a variance
- for example 72830 ii 98 are the same in both databases.

I hope I have explained this clearly.
Thanks.

Helen,

I guess you want to merge them into a single "database". If so, define a table PARTS in Access:

PARTS(PartNo,Let,Val),

with PartNo as primary key.

Choose Number as Data Type for Part No, Text for Let, and Integer for Val.

After you have defined the PARTS table,

Activate File|Get External Data|Import.

You should then be able select your .xls file, follow the steps of the procedure of Import. Pay attention to the fact that you import your 2 Excel "databases" into an existing table, that is, PARTS.

After the import, you'll have PARTS filled up non-duplicate records, which you can now export from Access to Excel.

Aladin