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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.Transa
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.Transa
@fyed>
Of course, you know what I meant :>) Your correction and politeness are accepted.
Of course, you know what I meant :>) Your correction and politeness are accepted.
Did you mean:
t2.customer# = t2Customer#
OR
T1.customer# = T2.Customer#