Building Query in Access 2010 to find sequential transactions

mobdog
mobdog used Ask the Experts™
on
     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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
The given table layouts don't seem to jive.  10,000,000 customers?  Customer table has a transaction date column?  

I would expect a table like CustomerTransaction (Transaction#, TransactionDate, Customer#).


Select * from CustomerTransaction T1
   where exists
        (select * from CustomerTransaction T2
           where t2.customer# = t2.customer#
              and (t1.transaction# - t2.transaction#) between -10 and 10
              and t1.transaction# <> t2.tranasction#
       )

Select * from CustomerTransaction T1
   where exists
        (select * from CustomerTransaction T2
           where t2.customer# = t2.customer#
              and (t1.transaction# - t2.transaction#) between -1 and 1
              and t1.transaction# <> t2.tranasction#
       )

           

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
dqmq,

Did you mean:

t2.customer# = t2Customer#

OR

T1.customer# = T2.Customer#

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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]

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial