Updating records after removing duplicates

[ianbrown76]

Hi

I have a table in my database that contains Orders, I recieve a list of Cancellations [of customers] that I have to then use to update the list of Orders to mark that certain customer products have been cancelled.

In order to find just the Orders that have to be marked as cancelled on the Orders table I import the list of customers into a table and run a query.

As one customer may have placed more than one order and the list I recieve only contains the Customer, I get 'duplicates' in the subsequent query and cannot run an 'update' query on all the records that occur as I couldn't be sure which order I need to cancel.

I have tried to do a count from my first query on the number of times a customer occurs and then link it back to a query which is same as the first but only where the customer occurs once, unfortunately I get an error telling me that the query is not updateable.

I've attempted to create a subquery [in SQL] to remedy the problem but I've not come accross the EXISTS word before, when the query runs it does not appear to do what I'd like, this is the SQL that I've created inorder to display only customers from my 'cancellations list' who occur only once on the 'orders' table:-

SELECT [Canc list].[ACCOUNT NUMBER]
FROM [canc list]
WHERE Exists
(SELECT ([canc list].[ACCOUNT NUMBER]), Count([canc list].[ACCOUNT NUMBER])
FROM [Orders] RIGHT JOIN [canc list] ON [orders].[Account Number] = [canc list].[ACCOUNT NUMBER]
WHERE ([orders].[Not Converted/Cancelled]) In ('c','j')
GROUP BY [canc list].[ACCOUNT NUMBER]
HAVING count ([canc list].[account number]) = 1)
ORDER BY [canc list].[ACCOUNT NUMBER];

I could copy counts etc into more tables but as I won't be updating the orders myself I was hoping for some kind of user friendly method for my colleagues to use.

Does anybody have any suggestions as to how to get around the problem?

Many Thanks in Advance
Ian

 

[tracy1234]

Hi Ian - Can you clarify a few points? You have a table or tables with customers and their orders in your database, right? Then you have a list of customers who have cancelled some or all of their orders, right?

How will you identify which order is to be cancelled unless you have this information in your cancellation list? Can't you include an order number in the cancellation list?

If you don't have an order number, how will you identify which order needs to be cancelled?

-Tracy

 

[ianbrown76]

Hi

I have a table of 'orders', which contains customer number, dates, prices, product numbers etc.

I am supplied a list of customers who have cancelled with various other information from another party, unfortunately the only field indentical on this list with the 'orders' table is the customer number, the dates are different because one contains date of orders the other despatches, descriptions are different etc.

As hundreds of orders are cancelled every month, to save time I insert the customer numbers into a table. In a query I then match it to the orders table so that only the 'orders' of the customers are displayed. I'd like to then update a number of fields where the orders need to be cancelled.

In some instances a customer may have ordered more than one product and so I thought I could remove the customer where more than one row appears and then 'update' all the customers in the orders table with an update query. My colleagues can then manually look into the customer duplicate orders and work out which order to cancell, which will have saved a great deal of time compared to current practices.

When I do the following I find that the query is not updateable:-
Table1 = Orders
Table2 = Cancellations list

Query1 = Table1 & Table2 [displaying some duplicates]

Query2 = Counts the records per customer number in Query1

Query3 = Query1 & Query2 matching on Customer number where count of customers = 1

 

[ianbrown76]

I've got around the problem by using a subquery.

Thanks for your interest Tracy