Comparing fields between two databases

[asasak]

I want to compare two databases, the current database against last months data. It needs to be flexible enough so I can select which fields to compare on and be able to select multiple fields at a time. What would be a recommended approach. I tried using the built-in compare query in Access, but it only lets me do one field at a time. Any help would be appreciated.
Thanks

 

[JoaoTL]

2 Databases or 2 Tables?? Best Regards

---
JoaoTL
mail@jtl.co.pt
My MS Access Site:

http://www.jtl.co.pt

 

[asasak]

Actually two tables. The structure of both are identical.

 

[JoaoTL]

I need more information:

You have Relations in thous 2 Tables?? or there is no possible relation to do??

If you have a relation like One To One or One to Many with a Field ID you can Join this 2 Tables in a SELECT Query...

If you have no relations u have to post the criteria that you whant to compare in order to help you..OK? Best Regards

---
JoaoTL
mail@jtl.co.pt
My MS Access Site:

http://www.jtl.co.pt

 

[asasak]

Good point about the relationship...I didn't set up a relation between the two yet(and logically I would need to do that first). I have an Autonumber field in each table, but that would not necessarily be the same in each table to use in a relationship. Would I have to relate all fields used in a compare?? If only one is required, then I would use the part number field to relate. I hope I am making sense of this.

What I had envisioned was a select box listing all the fields in the table where I can click and select which fields to compare on and then spit out the results on a pre-determined report I create. The part I get hung up on is how to write the query that will let me compare more than one field. Maybe an SQL will solve this but I haven't started there yet.

Appreciate your interest in my problem.

Thanks

 

[JoaoTL]

Ok...

1. It is only necessary one field to build the relation.

2. You can make this relation in the Query and forget the relations in the Tables for now..

3. Build a select query and join the PartNumber, then you going to select all field that you would need to compare.

4. The output will be the partnumber with all fields that you selected and will join All fields when the PartNumber is the Same Number or string...

5. If you want you can send me by Email part of the MDB with those 2 Tables and the Select query...

If you need more assistence please replay...
Best Regards

---
JoaoTL
mail@jtl.co.pt
My MS Access Site:

http://www.jtl.co.pt

 

[asasak]

JoaoTL,

Sorry I didn't get back sooner....I sent a sampling of the database to your email address so you can see what I am working with. Would like to be able to compare multiple fields at once.

Thanks in advance

 

[JoaoTL]

Ok i have your MDB

And the Part Number Field for h1ah64_master, and h3ah64_master is?

Best Regards

---
JoaoTL
mail@jtl.co.pt
My MS Access Site:

http://www.jtl.co.pt

 

[JoaoTL]

I think i get the PartNumbers Fields...copy this stadements to a Select Query in SQL View....then say something..

SELECT h1ah64_master.pn1, h3ah64_master.mfg_pn
FROM h1ah64_master INNER JOIN h3ah64_master ON h1ah64_master.pn1 = h3ah64_master.mfg_pn;
Best Regards

---
JoaoTL
mail@jtl.co.pt
My MS Access Site:

http://www.jtl.co.pt