comparison database

[lemonap618]

Hello All,
I hope this message finds everyone well.
I admittedly know very little about Access so I have come here for help.

Problem: I need to compare two Excel files by invoice number and invoice sum. I need the output to show the invoices that are contained in one file but not the other, and the invoices where the invoice sum does not match. One caveat is, the first file is at an invoice level while the second is at a line item level. So to accurately compare, I need Access to aggregate the sum of the line item file and then compare to the invoice file.

I hope I'm not being super confusing. Any and all help is much appreciated!!!!

 

[Joe4]

It would be beneficial to see what your data looks like, to see how it is structured.

I need the output to show the invoices that are contained in one file but not the other, and the invoices where the invoice sum does not match.

Access actually has a built-in Unmatched Query Wizard to walk you through this process.
However, you may have to create a query first to get your data in workable format, before using this. That really depends on what your data looks like.

I admittedly know very little about Access so I have come here for help.

If you have no knowledge of how to create queries in Access, it is going to be very difficult to help you. I recommend taking a few minutes to read up on them first.
Here are some links to get you started:
https://support.office.com/en-us/ar...ct-query-de8b1c8d-14e9-4b25-8e22-70888d54de59
https://www.youtube.com/watch?v=tpIPWIDcMTo

There are many more you can find with Google or YouTube searches.

Start there, and see how you do. If you run into trouble and need help, post small sample of your data so we can see what it looks like and fully understand what you mean when you say:

One caveat is, the first file is at an invoice level while the second is at a line item level. So to accurately compare, I need Access to aggregate the sum of the line item file and then compare to the invoice file.

You may need to do an Aggregate (Totals) Query first to roll up those records into a single record per invoice.
(You can search "Aggregate (Totals) Query in Access" too).

 

[lemonap618]

Hi Joe,
Thank you so much for your response.
Below is a snapshot of my two Excel workbooks.

I have loaded the data in Access, defined my primary key (invoice), and created a relationship between the two workbooks.
I believe I can create a query on my own that shows invoices that are in the first file, but not in the second; however, I also need to see which invoices are contained in both workbooks but do not have the same invoice amount. Can you help guide me through that?

All help is much appreciated!

 

[james_lankford]

upload your images to
Imgur: The most awesome images on the Internet

but be sure to crop out any personal info that may show in the images before you upload them

then post links to your images

 

[Joe4]

For the other query, just do a simple query joining the two Tables together on your Invoice Number field.
Then, place Criteria where the amount of the Invoice from the second is difference from the first.

Let's say that your Tables (or Queries) are named Table1 and Table2, and the amount field is named "Amount" in each table.
Then, in your query, under the Table1 Amount field, add this on the Criteria line:

<> [Table2]![Amount]

Here is a link on adding Criteria to queries: https://support.office.com/en-us/ar...criteria-3197228c-8684-4552-ac03-aba746fb29d8

 

[lemonap618]

My images of my data have been updated to Imgur: lemonap618's albums - Imgur
Thanks!

 

[lemonap618]

Hi Joe,
thank you so much for your time.
I have followed your steps and have joined the two tables together based on the invoice number field....thank you!
Two quick requests - can you show me a quick screen shot of where to add the Code in Access you presented above? Also, could you show me a screenshot as to where you initiate an aggregate query as one of my tables is at the line item level?
This would help me out tremendously!

Thanks for your time!

 

[Joe4]

Two quick requests - can you show me a quick screen shot of where to add the Code in Access you presented above?

The link I provided in that reply has screen shots showing exactly where/how to add the Criteria to the Query.

Also, could you show me a screenshot as to where you initiate an aggregate query as one of my tables is at the line item level?

Just go to the Query section. An Aggregate Query is a type of query.

Here is a link on Aggregate Queries: https://support.office.com/en-us/ar...uery-b84cdfd8-07ba-49a7-b067-e1024ccfcca8#bm3
They walk you how to set one up under "Count all records in a query". Just choose the "Sum" function instead of the "Count" function to do what you want.

 

[lemonap618]

Thank you so much, Joe!

One quick question - I tried to load the following data set in Access and received this error.
Do I need a primary key?

My images of my data have been updated to Imgur: lemonap618's albums - Imgur

 

[Joe4]

What was the exact error message?
It won't hurt to put an Autonumber field at the end of your table, and let this be the primary key.