Need a query to identify duplicate customer orders (1 Viewer)

Ingeneeus · Nov 17, 2015

My company deals with institutional purchasing departments, rather than individual customers. Because there are multiple persons ordering under a single CustomerID, we frequently receive more than one order for a specific item from the same institution. This results in us shipping more of that item to the institution than they actually want, which in turn results in our having to swallow a Return.

We would like to avoid this, for obvious reasons. Our Access-based Orders system imports the orders from a Web-based shopping cart, so we can't effectively catch these duplications manually.

I'm approaching the solution with the idea that we could catch the duplications if we were able to check each day's import against previous orders.

I think I've got the FIRST part of this. I've created a query -- I imaginatively named it TodaysOrders -- that we can run after each day's import of orders.
The fields it checks -- in this order -- are:
OrderDate (today), SKU, ProductName, SourceOrderNumber (from our shopping cart), and CustomerID.

Works like a charm. The results look like the attached screencap. This, unfortunately is where I hit the wall.
I think what I need is another query which looks at the CustomerID and SKU results from TodaysOrders and compares them to earlier orders.

This query -- let's imaginatively call it CompareOldOrders -- would need to look at two tables in the database: Orders (which stores OrderDate, CustomerID and OrderNumber) and Order Details (which stores SKU). OrderNumber is common to both tables.

So, what I have in mind is that CompareOldOrders will find the CustomerIDs in the results from TodaysOrders, look at what SKUs are assigned to each CustomerID. It would then find the corresponding CustomerID in the Orders table and look at all of the orders placed under that CustomerID. For each OrderNumber associated with the CustomerID, it would look at the OrderDetails table and compare the SKUs in the order to those in the results of TodaysOrders.

Ideally, I would like to put this into a small module which throws up a MessageBox if CompareOldOrders finds a match ("Customer 277 has previously ordered Item 891458756843 in Order Number 26524")

Does this make any sense at all to anyone? I may be going about this all wrong; I'm open for other suggestions that get me the same results.

Uncle Gizmo · Nov 17, 2015

I don't think you should aim to compare the "SKUs" maybe do that later. I think all you need to do is count them and if there is more than 0, then you know that there was a recent order. I think it would be a good idea if you provided a small sample dB with just the tables in question and possibly the queries you have created so far. This might provide a breeding ground for inspiration!

gemma-the-husky · Nov 17, 2015

it is tricky. you would;nt expect to have to do the customer's admin for them.

Anyway, as Tony suggests, you can test whether an item has been ordered within the last 7 days and ask

Code:

 test last order date
 if date-lastorderdate <=7 then
     if msgbox("This item was last ordered on: " & lastorderate  & " on order: " & lastordernumber & vbcrlf & _
     "Are you sure this order is correct",vbquestion_vbyesno)=vbno then
          cancel order line
    end if
 end if

you can get as fancy as you want. display the last order; show all matching orders; hold the order and confirm; send an email to the customer.

Ingeneeus · Nov 17, 2015

Uncle Gizmo said:
I don't think you should aim to compare the "SKUs" maybe do that later. I think all you need to do is count them and if there is more than 0, then you know that there was a recent order. I think it would be a good idea if you provided a small sample dB with just the tables in question and possibly the queries you have created so far. This might provide a breeding ground for inspiration!

Hi, Uncle -- thanks for responding.

I'm afraid I may be a little obtuse; is it your suggestion that I count the instances of SKUs in the OrderDetails table? Wouldn't I still need to compare (i.e. match up) the SKUs in that table to the results from the TodaysOrders query? If I'm just looking at the OrderDetails table, I won't be able to tell which SKU I'm looking to count.

Could you perhaps clarify your suggestion? Sorry if I sound a bit dense on this.

Unfortunately, I am unable -- well, unwilling, really -- to post up the tables in question as you suggested, as the Orders table has customer data in it and I really don't feel comfortable putting that out there.

Ingeneeus · Nov 18, 2015

OK, had a thought that MIGHT make this simpler.
If I eliminate the TodaysOrders query from the equation altogether, maybe we can simplify this.

I'll recap so you don't have to wade through my initial post again. At the heart of this, I need a query to tell me if a given item has been previously ordered by a given customer. The problem is that the SKUs for our items are in one table -- OrderDetails -- and the CustomerIDs are in another table -- Orders. The two tables have a field in common -- OrderNumber.

To find out which customer ordered what item, we need to look at both tables. A simple query which uses OrderNumber and CustomerID from Orders and SKU and ProductName from OrderDetails yields who bought what. I can add OrderDate from Orders to limit my results to, say, the past 90 days to keep this from getting too big (screencap attached).

What I can't figure out is how to use this result to look for duplicate orders from the same customer. Everything I've read about Find Duplicates queries seems to indicate that they will only work within one table. Since my CustomerID and SKU fields are in two different tables, I think I'm out of luck.

I need some way to look at both of those fields and let me know that CustomerID 277 has ordered SKU 855114005270 before.

OrderNumber..CustomerID………….SKU………………………….Product ………………………………………OrderDate
26209…………………277…………………….855114005270………..Love in the Time of Civil War…….10/23/2015
26298…………………196…………………….887936951893………..Years of Living Dangerously………10/27/2015-
26298…………………277…………………….855114005270………..Love in the Time of Civil War…….10/27/2015

The above is copied from the aforementioned query result, quite a few rows in between omitted; sorry for the odd formatting -- I was hoping that this would accommodate a tabular format, but no such luck.

Can this be done? If so, can you offer some specific advice as to how to go about it?

Uncle Gizmo · Nov 18, 2015

Unwilling? You've already posted around 25 records!

And of those 25 there were only around 8 customers so you only need a customer table with 8 customers in. All you need to do is change the name to mister Smith mister Jones mister Brown Mr Green Mr Happy!

Uncle Gizmo · Nov 18, 2015

I think I've got it!!!

I can't see a way of doing it with a query, not just by looking at the question on paper. I suspect there probably is a way but I'm just not seeing it.

However I think you could use two record set loops, an inner and outer, both based on the query you have shown "items ordered by day" one version restricted to the days orders and the other spanning the period you want to check.

The outer loop would run through today's orders, and the inner loop would run through the last 7 days of orders (excluding today) or whatever number of days suits your operation.

The outer loop would get each customer number and the corresponding SKU, and check them against the same Fields from the inner loop, (customer number and corresponding SKU) if there's a match show a message box similar to the one you describe.

Uncle Gizmo · Nov 18, 2015

If you had posted a sample database as requested I might have been able to knock something up for you but I'm not willing to create the tables myself from scratch.

I posted a similar example a few days ago using a recordset loop however it doesn't have an inner loop, it has a For Next loop instead. I think you will get the gist of it from the example.

http://www.access-programmers.co.uk/forums/showthread.php?t=282440

Uncle Gizmo · Nov 18, 2015

If you were to post a sample database I would be interested in knocking up an example of an inner and outer loop to put on my website as I don't have an inner/outer example yet. However I won't be able to do anything until tomorrow night because I am off to bed now!

Galaxiom · Nov 18, 2015

Avoid the problem. Change your Terms and Conditions to include a fee for returns. Your customers will soon learn to be more intelligent with their ordering.

Ingeneeus · Nov 18, 2015

Uncle Gizmo said:
Unwilling? You've already posted around 25 records!

And of those 25 there were only around 8 customers so you only need a customer table with 8 customers in. All you need to do is change the name to mister Smith mister Jones mister Brown Mr Green Mr Happy!

Thanks, Uncle; I see your point. I will try to put up a sample DB here later today. Oddly enough, we had a small disaster with the very Orders table in question yesterday. Took most of the day to get us back to where we needed to be

Ingeneeus · Nov 19, 2015

Uncle Gizmo said:
If you were to post a sample database I would be interested in knocking up an example of an inner and outer loop to put on my website as I don't have an inner/outer example yet. However I won't be able to do anything until tomorrow night because I am off to bed now!

I seem to be having some issues with posting a sanitized sample DB as you suggested. When I export my Orders table to a sample DB, any changes I make (for example changing a customer's name or address), those changes affect my original table as well. I don't know why that's happening, but I really don't want to mess anything up in my "live" data!

I'm just using the built in export feature in Access 2010, but I don't think this should be happening. I even selected export only definitions, not data.

Ingeneeus · Nov 19, 2015

Galaxiom said:
Avoid the problem. Change your Terms and Conditions to include a fee for returns. Your customers will soon learn to be more intelligent with their ordering.

Ah, if only!

Uncle Gizmo · Nov 19, 2015

Are you using linked tables?

Ingeneeus · Nov 19, 2015

Uncle Gizmo said:
Are you using linked tables?

Yes, Orders is a linked table. Maybe I should just create a small table from scratch and copy a bunch of records into it. That seems like it might be a viable way to go.

I'll try to squeeze that in today.

Ingeneeus · Nov 19, 2015

Uncle Gizmo said:
Are you using linked tables?

OK, I scraped together enough time to create some sample tables in a database. These are pretty abbreviated tables, but I think I've provided enough of a sample to work with.

Any additional insight (and examples!) you can provide would be most appreciated!

Thanks again

Uncle Gizmo · Nov 20, 2015

Please find attached sample database that uses an inner and an outer loop to check the two sets of records for matches.

I don't “know” your data, and I was tempted to write some simple sample data to make it easier to see if the test was working properly. However I thought it might be easier for you to do the testing with the existing data. Please check you are getting the results back you expect.

At the moment the code is dumping the test data into the test table. If you look in the code behind the form you will see a remmed out line which, if you unrem it will show a message box showing you the SKU associated with a customer ID for each record where there is a match between the outer and inner loops. It looks to me like it's returning too many records. Please check you are getting the results back you expect.

Please note the default date is set to 02/11/15. You will see in the test results for 02/11/15 the top row dated 23/10/2015 contains an SKU 019962171215. If you look down the “list of selected date” you will find the same SKU but it's date is the 02/11/2015.

If you change it to 23/10/15 you will see an empty list, “No Match”.

If you change it to 27/10/15 another list of past matches is returned. You will see that the test results top row dated 23/10/2015 contains an SKU 855114005270. If you look down the list of selected dates you will find the same SKU but it's date is the 27/10/2015.

As I mentioned above to get the message box you mentioned in your post you will need to activate the code below.

'Unrem This >>>>
'This Message box reports the hits
'If (strOutSKU = strInnSKU) And (strOutCustID = strInnCustID) Then MsgBox "SKU >>> " & strInnSKU & " > " & strOutCustID

Uncle Gizmo · Nov 23, 2015

Note:- there's a typo in the code

Code:

                                    strInnProduct = rsOuterToday!Product
                                    strInnSourceOrdNo = rsOuterToday!SourceOrderNumber

'Should be:-
                                    strInnProduct = rsInner60Day!Product
                                    strInnSourceOrdNo = rsInner60Day!SourceOrderNumber

Ingeneeus · Nov 24, 2015

Uncle Gizmo said:
Please find attached sample database that uses an inner and an outer loop to check the two sets of records for matches.

I don't “know” your data, and I was tempted to write some simple sample data to make it easier to see if the test was working properly. However I thought it might be easier for you to do the testing with the existing data. Please check you are getting the results back you expect.

At the moment the code is dumping the test data into the test table. If you look in the code behind the form you will see a remmed out line which, if you unrem it will show a message box showing you the SKU associated with a customer ID for each record where there is a match between the outer and inner loops. It looks to me like it's returning too many records. Please check you are getting the results back you expect.

Please note the default date is set to 02/11/15. You will see in the test results for 02/11/15 the top row dated 23/10/2015 contains an SKU 019962171215. If you look down the “list of selected date” you will find the same SKU but it's date is the 02/11/2015.

If you change it to 23/10/15 you will see an empty list, “No Match”.

If you change it to 27/10/15 another list of past matches is returned. You will see that the test results top row dated 23/10/2015 contains an SKU 855114005270. If you look down the list of selected dates you will find the same SKU but it's date is the 27/10/2015.

As I mentioned above to get the message box you mentioned in your post you will need to activate the code below.

'Unrem This >>>>
'This Message box reports the hits
'If (strOutSKU = strInnSKU) And (strOutCustID = strInnCustID) Then MsgBox "SKU >>> " & strInnSKU & " > " & strOutCustID

Hi, Uncle -- thank you for your efforts on this. Sorry for the delayed reply; things got busy at the office on Friday and my primary responsibilities must take precedent. I've downloaded your sample DB and hope to take closer look at it tomorrow. I'm keeping my fingers crossed that it won't be TOO far over my head!

Ingeneeus · Nov 26, 2015

Uncle Gizmo said:
Please find attached sample database that uses an inner and an outer loop to check the two sets of records for matches.

I don't “know” your data, and I was tempted to write some simple sample data to make it easier to see if the test was working properly. However I thought it might be easier for you to do the testing with the existing data. Please check you are getting the results back you expect.

At the moment the code is dumping the test data into the test table. If you look in the code behind the form you will see a remmed out line which, if you unrem it will show a message box showing you the SKU associated with a customer ID for each record where there is a match between the outer and inner loops. It looks to me like it's returning too many records. Please check you are getting the results back you expect.

Please note the default date is set to 02/11/15. You will see in the test results for 02/11/15 the top row dated 23/10/2015 contains an SKU 019962171215. If you look down the “list of selected date” you will find the same SKU but it's date is the 02/11/2015.

If you change it to 23/10/15 you will see an empty list, “No Match”.

If you change it to 27/10/15 another list of past matches is returned. You will see that the test results top row dated 23/10/2015 contains an SKU 855114005270. If you look down the list of selected dates you will find the same SKU but it's date is the 27/10/2015.

As I mentioned above to get the message box you mentioned in your post you will need to activate the code below.

'Unrem This >>>>
'This Message box reports the hits
'If (strOutSKU = strInnSKU) And (strOutCustID = strInnCustID) Then MsgBox "SKU >>> " & strInnSKU & " > " & strOutCustID

Hi again, Uncle -- I don't know if you are still following this thread, but I hope so. I was finally able to take the time to take a look at your solution. I am officially intimidated

.

That said, I have a general idea how it's doing what it's doing. I couldn't replicate it with a gun to my head, of course, but at least I think I understand the underlying principles.

The reason that it's returning too many results is that strInnSKU is including the SKUs "Sales tax" and "Shipping" in with the actual product code SKUs. It needs some kind of Not Like line to make it ignore those two items.
I tried adding the line:

Code:

If strInnSKU <> "*Sales Tax*" Then If (strOutSKU = strInnSKU) And (strOutCustID = strInnCustID) Then

before

Code:

If (strOutSKU = strInnSKU) And (strOutCustID = strInnCustID) Then MsgBox "Customer #

but I get a Compile error: Loop without Do error box. I thought I might need to put an End If following the MsgBox line, which lets the code run, but then it still includes the "Sales Tax" SKUs.

I'm not sure this is the right place to be excluding those SKUs, but I also can't figure out where I should be doing it.

I'm off until next Monday, but will be watching my e-mail in case the forum notifies my that there is a response.

Thank you again!

Need a query to identify duplicate customer orders (1 Viewer)

Registered User.

Attachments

Nifty Access Guy

Super Moderator

Registered User.

Registered User.

Attachments

Nifty Access Guy

Nifty Access Guy

Nifty Access Guy

Nifty Access Guy

Super Moderator

Registered User.

Registered User.

Registered User.

Nifty Access Guy

Registered User.

Registered User.

Attachments

Nifty Access Guy

Attachments

Nifty Access Guy

Registered User.

Registered User.

Similar threads

Users who are viewing this thread