By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,837 Members | 1,766 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,837 IT Pros & Developers. It's quick & easy.

Matching data from two tables

P: 12
I would appreciate some help in a problem I've been having.

We use two packages one for accounting and one to keep track of sales made.

I have two tables imported into access from each of the programs

I need to do match two separate things:

The sales system creates dockets for each sale, each week a report is run that creates invoices, so each invoice may have several dockets.

The accounting program only has the invoice information.

For audit reasons I need to match this data up to check they match.

I want to compare the invoice number from each query (or table whichever is easier) match and identify if any don't match so I can track down why they are not in.

Second:
I can run a query to sort to data from the sales program- I need a column that adds were the invoice numbers match so I can compare to the invoice in the accounting program (dockets can be altered later, so someone could undercharge a docket, wait for the export and change it later so if it is checked it seems right). Basically checking all the docket prices that form one invoice from the sales system = the invoice from the accounting software.

So I'm checking two things- one if something is not in one table and not the other (so I can find out why), and secondly where invoices do match I want to find out if they are equal (and as one table has the invoice made up of multiple values I have problems).

Thanks for any help, it is appreciated.
May 29 '07 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 344
I would appreciate some help in a problem I've been having.

We use two packages one for accounting and one to keep track of sales made.

I have two tables imported into access from each of the programs

I need to do match two separate things:

The sales system creates dockets for each sale, each week a report is run that creates invoices, so each invoice may have several dockets.

The accounting program only has the invoice information.

For audit reasons I need to match this data up to check they match.

I want to compare the invoice number from each query (or table whichever is easier) match and identify if any don't match so I can track down why they are not in.

Second:
I can run a query to sort to data from the sales program- I need a column that adds were the invoice numbers match so I can compare to the invoice in the accounting program (dockets can be altered later, so someone could undercharge a docket, wait for the export and change it later so if it is checked it seems right). Basically checking all the docket prices that form one invoice from the sales system = the invoice from the accounting software.

So I'm checking two things- one if something is not in one table and not the other (so I can find out why), and secondly where invoices do match I want to find out if they are equal (and as one table has the invoice made up of multiple values I have problems).

Thanks for any help, it is appreciated.
I would create a query on the sales dockets, grouping them together, so that you have 1 record on sales per invoice. (If there is no logical way of grouping the dockets together, I don't see how you can match them to the invoices anyway)

You now have two recordsets, one for invoices, one for sales, with a 1-1 match and the sales total should equal the invoice value.

You can then read through both recordsets looking for matches. [/code]
May 29 '07 #2

P: 12
Thanks for your help, I eventually got it into one table.

Now I have one table with all the invoices listed (each is listed twice- once for each program) what I want to do now is delete the entires wher both the invoice number and amount match so I'm left with only the entries where the invoice numbers match and the amount don't are left. I have searched for a while and come up with nothing.

Also with this table I want to pull out the unique invoices (where they are in one program and not the other), if I recall this is not to hard and I will search for an answer, but figured I'd ask while asking the other question in case I have no luck.

EDIT: I should clarify that I want both the duplicates deleted, all the information I have found shows how to keep one- in this case I want all duplicates gone as I know they are correct if they match.
May 30 '07 #3

Expert 100+
P: 344
Thanks for your help, I eventually got it into one table.

Now I have one table with all the invoices listed (each is listed twice- once for each program) what I want to do now is delete the entires wher both the invoice number and amount match so I'm left with only the entries where the invoice numbers match and the amount don't are left. I have searched for a while and come up with nothing.

Also with this table I want to pull out the unique invoices (where they are in one program and not the other), if I recall this is not to hard and I will search for an answer, but figured I'd ask while asking the other question in case I have no luck.

EDIT: I should clarify that I want both the duplicates deleted, all the information I have found shows how to keep one- in this case I want all duplicates gone as I know they are correct if they match.
Ack, I think it would have been easier to have two seperate tables, I dont think you can delete using group queries. What you could do is run a group query that makes a temporay table, somethink like this.

Expand|Select|Wrap|Line Numbers
  1. SELECT InvoiceKey, amount, Count(InvoiceKey) AS CountOfInvoiceKey INTO tmpInvoiceToDelete
  2. FROM tblInvoice
  3. GROUP BY InvoiceKey, amount
  4. HAVING Count(tblInvoice.InvoiceKey)=2;
  5.  
  6.  
The temp table will hold the invoice key of all invoices where you have 2 keys with the same amount. You can then use this temp table to run a delete query against the main table, deleting all your duplicate invoices with the same amount.
May 30 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.