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.
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.