Link to home
Start Free TrialLog in
Avatar of mobdog
mobdogFlag for United States of America

asked on

Building Query in Access 2010 to find sequential transactions

     1. Have table of 10,000,000 Customers [ customer name, customer #, transaction date, salesperson ]
      2. Have table of 10,000 customer invoices - relevant fields are: [transaction#, transaction_type, transaction date, customer_name, salesperson ]
      3. Have table of 1,500 customer return invoices - fields[transaction#, transaction_type, transaction date, customer_name, salesperson]
      4. Definition of transaction# - every record has a unique ascending transaction# and every transaction# is created in sequential order and defined by the [transaction_type]
      I'm looking Query those tables for the following info:
      5. Find all transactions where [customer_name ] had a [transaction#] within 10 numbers of another [transaction#] of the same customer.

I am also looking to find sequential transactions with the same customers. i.e. all situations where a customer transaction# is within 1 of another.
Avatar of dqmq
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Dale Fye

Did you mean:

t2.customer# = t2Customer#


T1.customer# = T2.Customer#

or you could try:

SELECT T1.CustomerID, T1.Transaction#, T2.Transaction#
FROM tblCustomerTransactions as T1
INNER JOIN tblCustomerTransactions as T2
ON T1.CustomerID = T2.CustomerID
AND T1.Transaction# < T2.Transaction#
WHERE (T2.Transaction#-T1.Transaction#) <= [Enter acceptable transaction gap]

Of course, you know what I meant :>)  Your correction and politeness are accepted.