Deleting Records based on matching criteria

[maw230]

Ok, i have 2 queries. 1 pulls sales data; the other pulls Returns data. What i need to do is find a way to write a query that will delete records that match on several different criteria.

what i would like to do is write a Union query that combines sales and returns, that will show the sale and the return in the same record based on matching account numbers, names, item numbers, etc.

as of now, it shows them on separate lines so i have ot go through and manually compare these records to determine which ones to delete. however, i did get an error while running the Union query which i wasnt familiar with. it was something like "function rejected by user exit program ______ by _____" is that something where i would need to contact IS to allow access?? i have no idea there.

im not sure if im being clear enough, but right now im trying to be fairly broad with my question.

in summary, what is the best way to combine 2 queries with similar data (so the records show up on the same line)?

 

[wilpeter]

How many fields exactly have to match from the two tables? Is there a primary key in each table?

 

[maw230]

there are approx 6 fields that need to match up. the tables do not all have PK's.

 

[wilpeter]

So, 2 queries; unknown tables, possibly with PKs; approx 6 fields as criteria matches; either Union or Delete Query will do. Can you reveal the field names that are common to the output of the two queries that need to match? Maybe I can approximate an answer.

 

[maw230]

the fields that need to match will be:

CustNum, CustName, Item, Line,
OverridePrice = Original Price, Qty Sold = Qty Returned.

the first 4 must be the same to identify the common record, then the Override price from the Returns query must equal the Original price from the Sales query. The same goes for the quantities.

i guess first i would like to be able to Union these where the 2 prices and the 2 Qty's appear on the same record. then i could run a Delete query from that where those fields are equal.. or so i hope.

if you need anymore info let me know, as i can see that it's a rather vague request..

 

[wilpeter]

I'm surprised that you have both CustNum and CustName to match...are they not unique. Text fields are trickier to guarantee. Is Line TEXT or Number? Does the attachment do the resulting query fields justice--or is the Override and Original both called "Price" in the query?

 

[maw230]

as far as CustName and CustNum, im not positive whether or not they are unique. since there are thousands of them it's easier to assume there could be multiple CustNames for one CustNum..

as for the dates, they are simply restricted to a one month period so we dont go by each transaction date.

pardon my rookieness, but do you need the salesid and returnid as i dont believe my queries currently have a unique identifier?... i suppose i could add one. or it might be the invoice number actually...

Item is Text. Line is Text.

Other than that it all looks good.

 

[wilpeter]

Let's assume you can adapt from this example. I made Item numeric but you can change, and No, the Sales & Returns ID would be in a table, but you would need something to recognize which record I think. Dates are optional but might exclude a legit return. Imagine this Query1 is from two Queries rather than tables.

 

[wilpeter]

The results of this query would give you the records that need to be deleted from both Sales and Returns sources. I would likely use a separate query that uses this query and the table from which you want to delete the records...joined appropriately...test to make sure only the same records show in that query's result, then change it to a delete query.

 

[maw230]

thanks wil.
i tried something similar but it would not work because the records were appearing on separate lines. but, maybe if i do it this way it will work properly.
i will give it a shot and go from there.
thanks again!