Matching data from two tables

Daine

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.

Lysander

Quote:

Originally Posted by Daine

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]

Daine

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.

Lysander

Quote:

Originally Posted by Daine

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

 
SELECT InvoiceKey, amount, Count(InvoiceKey) AS CountOfInvoiceKey INTO tmpInvoiceToDelete

FROM tblInvoice

GROUP BY InvoiceKey, amount

HAVING Count(tblInvoice.InvoiceKey)=2;

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.

Matching data from two tables

re: Matching data from two tables

re: Matching data from two tables

re: Matching data from two tables

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about