Create a query for non matching records

[GOVMATE]

Hello,

I am trying to create a query in Access 2010 for records that don't match based on the following criteria. I have two tables with identical ID's and I need to do a comparison on the amount field between both tables and only return the records from one table displaying all fields plus the amount fields that do not equal. Any help would be appreciate. I tried this in the wizard unsuccessfully.

 

[vbaInet]

Sounds like a normalisation problem Gov. What's going on with the two tables?

And we need more information about their similarities (or perhaps differences). Field names, sample records etc.

 

[GOVMATE]

The two tables both have some financial data. I have both ID's fields that match and they are formatted as text. There is a currency field in both tables that I want to compare. The ones that don't match, I would like them to go into a separate table. I hope this helps. Thanks again!

 

[vbaInet]

Still doesn't sound like the design is perfect.
Do you really need it in a separate table?
There's still not enough information to give any reasonable advice. I asked to see a list of relevant field names and some sample records as well.

 

[GOVMATE]

Ok here are the two tables that I'm comparing. I don't necessarily need a new created, but I query that shows shows the records that do not match would be great.

In the first table the BU PO ID matches field2(PO ID) in the second table formatted as text. I want to compare the PO_Amount in the first table with the Field6 in second table. Both are formatted as currency and should match for the most part, but I know for sure that there are differences. I appreciate your help on this again. If you could show me how to write the SQL code that would be greatly appreciated.

 

[GOVMATE]

Ah, I tried to post two screen prints, but this site would not accept them.

 

[vbaInet]

Try zipping them.

 

[GOVMATE]

table 2:

 

[GOVMATE]

I guess I need to try another route. I do not see an option to upload a zip file. I see an option for inserting a link and and image.

 

[vbaInet]

Click the Go Advanced button and you'll see a clip icon for uploading images and files.

 

[GOVMATE]

Ok, thanks for your help. I have attached screen prints of the tables.

 

[vbaInet]

I'm hoping that Field1 to 6 are not the actual field names in your table?
They don't mean anything to me and I would have difficulty trying to match them.

Is it data that you imported?

 

[GOVMATE]

Yes, this is imported data. I understand that the field names have no meaning to you, but I know for sure that the underlying data formats between both table are the same for the fields that I'm trying to compare. For example the PO ID fields in both tables that are indexed are formatted alpha and the other two fields that I'd like to compare contain monetary values formatted as currency. Does this information help?

 

[vbaInet]

Ok. So the fields you want to join against (as you mentioned in post #5) are:

BU PO ID = field2(PO ID)
PO_Amount = Field6

What you need to do is:
1. Create a new query and drop both tables into it
2. Drag the BU PO ID field and drop it on Field2 in the second table
3. Do the same for PO_Amount, i.e. to Field6.
4. View your query in datasheet view.

That's all there is to it.