slickest way to spot two orders to the same customer?

[peskywinnets]

I run an online business & each day before packing the orders for dispatch, I have a whole heap of onscreeen orders to scour manually with my eyes...I try to identify multiple orders which are all going to the same shipping address (sometimes a customer places several orders over say the weekend)...I put these multiple order into one package (which saves postage ...which I give back to the customer).

I have all my sales data normalised in Access & spread across several tables (Invoice, Invoice Items, Customers etc.)..... here's a simple query view (I'm using dots to separate the columns in this forum)....

Invoice Number......Shipping address

1.........................10 Station Road
2.........................24 High Street
3.........................10 Station Road
4.........................4 Ocean Road

so in the above example, there are 4 orders in total but 2 of these orders are to the same shipping address but placed at different times - in this situation I wish to update a boolean field in a table called something along the lines of "Multiple Orders', so I can then easily spot them.


What would be the best approach to this? (would it need some VBA or could it be done with just queries?)

 

[jdraw]

I'd start with a select query on the table containing Invoice and Address and group by address.

Good luck.

 

[peskywinnets]

Thanks....but in the time since posting I had a brainwave & cracked on & did it all in VBA.

Essentially select all the 'unshipped' orders, order them by CustomerID, loop through the selected records & apply the following...

if the CustomerID in the present loop matches the CustomerID from the previous loop, then update a 'MultipleOrders' table record to 'true'.

It works & it didn't need as much code as I thought it would...phew.

 

[jdraw]

Good stuff, glad you have it resolved.

Query approach:

SELECT PeskyT.ShippingAddr, Count(PeskyT.InvoiceNumber) AS CountOfInvoiceNumber
FROM PeskyT
GROUP BY PeskyT.ShippingAddr;

 

[Frothingslosh]

Pesky, I would strongly suggest going with jdraw's query approach. Queries are infinitely faster than VBA loops, and performance can become an issue surprisingly quickly.