Identifying 'Groups' in A Query (1 Viewer)

[alexfwalker81]

Due to a quirk of our hateful website, multiple delivery address orders are split into separate orders, despite the fact that they are one transaction. To be able to analyse the data in access, I really need to understand transactions, rather than orders as the number of orders is misleading. Below is a simplified version of how the data is structured;

Customer Number	Order Number
1234567	345678
1234567	345679
1234567	345680
6789100	345681
6789100	345682

What I'd like to be able to do, probably in a function I suppose, is to be able to identify where the customer number changes and assign a transaction ID, like this;

Customer Number	Order Number	Transaction ID
1234567	345678	1
1234567	345679	1
1234567	345680	1
6789100	345681	2
6789100	345682	2

In a sense, I guess what I'm asking for is similar to subtotals in Excel.

I can't use the Customer Number itself, as this will appear repetitively later in the data. I must sort the data by order number, then identify the transaction when the customer number changes.

How would I be able to achieve this?

 

[namliam]

How about create a query
Using a self join using ordernumber + 1 = ordernumber
Then simply add where customernumber <> customernumber
add a dummy column with the value 1

On this query build a new query self joining the above query
On ordernumber <= ordernumber
Group by order number, sum(dummy column)

Now this query holds your transaction ID's that you are looking for.

I hope I explained it clearly enough.

BTW, I hope your column names are different from the ones you used here.

 

[alexfwalker81]

How about create a query
Using a self join using ordernumber + 1 = ordernumber
Then simply add where customernumber <> customernumber
add a dummy column with the value 1

On this query build a new query self joining the above query
On ordernumber <= ordernumber
Group by order number, sum(dummy column)

Now this query holds your transaction ID's that you are looking for.

I hope I explained it clearly enough.

BTW, I hope your column names are different from the ones you used here.

I understand what you're saying in principle, but I'm still a good distance away from being able to execute this myself. Apologies for the cheeky request, but would you be able to throw this into a DB, as I can usually reverse engineer! No worries if not.

 

[arnelgp]

if you "Transaction Date" on your table (of course you have), you can Create
an Aggregate Query (qryTransGrouped) based on [Customer Number] and [Trans Date]:


SELECT Table1.[Customer Number], Table1.[Trans Date]
FROM Table1
GROUP BY Table1.[Customer Number], Table1.[Trans Date];


Next, create a new query (qryTransactionIDs) based on qryTransGrouped to Count() each Unique [Customer Number] and [Trans Date]:

SELECT qryTransGrouped.[Customer Number],
qryTransGrouped.[Trans Date],
DCount("1","qryTransGrouped","[Customer number] & [Trans Date] <= '" & [Customer Number] & [Trans Date] & "'") AS Expr1
FROM qryTransGrouped;

create the FinalQuery (qryFinal) joining the two Queries:

SELECT Table1.[Customer Number],
Table1.[Order Number],
Table1.[Trans Date],
qryTransactionIDs.Expr1 AS [Transaction ID]
FROM Table1 LEFT JOIN qryTransactionIDs
ON (Table1.[Trans Date] = qryTransactionIDs.[Trans Date]) AND (Table1.[Customer Number] = qryTransactionIDs.[Customer Number]);

 

[alexfwalker81]

if you "Transaction Date" on your table (of course you have), you can Create
an Aggregate Query (qryTransGrouped) based on [Customer Number] and [Trans Date]:


SELECT Table1.[Customer Number], Table1.[Trans Date]
FROM Table1
GROUP BY Table1.[Customer Number], Table1.[Trans Date];


Next, create a new query (qryTransactionIDs) based on qryTransGrouped to Count() each Unique [Customer Number] and [Trans Date]:

SELECT qryTransGrouped.[Customer Number],
qryTransGrouped.[Trans Date],
DCount("1","qryTransGrouped","[Customer number] & [Trans Date] <= '" & [Customer Number] & [Trans Date] & "'") AS Expr1
FROM qryTransGrouped;

create the FinalQuery (qryFinal) joining the two Queries:

SELECT Table1.[Customer Number],
Table1.[Order Number],
Table1.[Trans Date],
qryTransactionIDs.Expr1 AS [Transaction ID]
FROM Table1 LEFT JOIN qryTransactionIDs
ON (Table1.[Trans Date] = qryTransactionIDs.[Trans Date]) AND (Table1.[Customer Number] = qryTransactionIDs.[Customer Number]);

Superb, thank you!

 

[namliam]

SELECT qryTransGrouped.[Customer Number],
qryTransGrouped.[Trans Date],
DCount("1","qryTransGrouped","[Customer number] & [Trans Date] <= '" & [Customer Number] & [Trans Date] & "'") AS Expr1
FROM qryTransGrouped;

I wouldnt use the DCOUNT function, Dcounts and anyother D functions are very much a bad thing that you dont want to use.

Also concatinating a number and a date field on average is a bad idea, made worse by not explicitly formatting the fields to strings.
If it works it works, but implicit conversions (no format) have a tendancy to cause unexpected results.

My sql would look something like:

SELECT tg1.[Customer Number], tg1.[Trans Date], Count(*) AS Expr1
FROM qryTransGrouped AS tg1
INNER JOIN qryTransGrouped AS tg2 ON tg1.[Customer Number] = tg2.[Customer Number]
                                                            and tg1.[Trans Date] <= tg2.[Trans Date]
GROUP BY tg1.[Customer Number], tg1.[Trans Date];

A little more "access friendly"

SELECT tg1.[Customer Number], tg1.[Trans Date], Count(*) AS Expr1
FROM qryTransGrouped AS tg1
INNER JOIN qryTransGrouped AS tg2 ON tg1.[Customer Number] = tg2.[Customer Number]
Where tg1.[Trans Date] <= tg2.[Trans Date]
GROUP BY tg1.[Customer Number], tg1.[Trans Date];

Superb, thank you!

I hope you got it working, regardless