Need a Dynamic Record Counter Function for Query Records!!!!

A

AJ

I have two tables, one with Transaction Data and the other table has Client
Names.

I need a Dynamic Counter Function that is based on a series of Select
Queries (which are used to refine the data...and sort it...from the
Transaction table by time-period, amount, etc.).

If the data from the last Select Query looks like this:

ClientID Date Amount TrxID (unique)
4001 2/18/09 56.00 6119
4024 1/11/09 70.00 7272
4024 5/20/09 200.00 6834
4024 8/08/09 65.00 7864
4026 10/1/09 82.00 7911
4031 6/18/09 50.00 7013
4031 7/10/09 60.00 7249
... ... ... ....

I would like the resulting Data after the Dynamic Record Counter to look
like this:

ClientID Date Amount TrxID (unique) Counter
4001 2/18/09 56.00 6119 1
4024 1/11/09 70.00 7272 1
4024 5/20/09 200.00 6834 2
4024 8/08/09 65.00 7864 3
4026 10/1/09 82.00 7911 1
4031 6/18/09 50.00 7013 1
4031 7/10/09 60.00 7249 2
... ... ... ....
...

I don't think that I can create a Make Table Query first, because I need to
run this query multiple times for different time periods, so if I Make Table
each time, the dbase will be polluted with redundant data. To give you and
idea of the scope of records, there are 40K+ total records in the Transaction
Table and approximately 17K records that meet the current selection criteria
in the Select Query where I want to apply the Dynamic Counter.

Please Help!!! I have been trying everything I can find, but can't seem to
get this to work.
 
V

vanderghast

SELECT a.clientID, a.date, LAST(a.amount), LAST(a.trxID), COUNT(*)
FROM mQuery AS a INNER JOIN mQuery AS b
ON a.clientID=b.clientID AND a.date >= b.date
GROUP BY a.clientID, a.date


should do, assuming your actual query is called mQuery. The proposed query
used two references to it, aliased as a and b, like two fingers, a and b,
running on a list of items (records) on ONE list (mQuery). For each position
of the finger 'a', given by a.client, a.date, finger 'b' runs over all the
possible records
ON (which) a.clientID=b.clientID AND a.date >= b.date
and then, you mentally COUNT the number of records you can so reach.





Vanderghast, Access MVP
 
A

AJ

Thank You, Vanderghast!

Your Example looks like it is working, however I had to make one small
change to your Example to add a Group By on a.TrxID (because there are a few
instances where the same client had multiple transactions on the same date).

Here is what I used:

SELECT a.ClientID, a.DATE, LAST(a.AMOUNT) AS CurrAMOUNT, LAST(a.TrxID) AS
CurrTrxID, COUNT(*) AS [COUNTER]
FROM mQuery AS a INNER JOIN mQuery AS b ON (a.DATE>=b.DATE) AND
(a.ClientID=b.ClientID)
GROUP BY a.ClientID, a.DATE, a.AMOUNT, a.TrxID;

The only issue I have is whether there is a way to add ne or two fields to
your example so that I show show the Previous TrxID and possibly the Previous
AMOUNT on each line...so that I can verify that your example is cycling
through the data correctly?

Thank you for all of your help!

AJ
 
V

vanderghast

It would be easier to make a new query based on that one. Assume the actual
one is saved under the name q1,


SELECT q1.*, q1_1.*
FROM q1 LEFT JOIN q1 AS q1_1
ON q1.counter = q1_1.counter + 1
AND q1.clientID = q1_1.clientID



should to the job.

Note that for the first date, for a given client, columns from q1_1.* will
be all filled with nulls, since there is no 'previous' date... to the first
one. Indeed, since

q1.counter = q1_1.counter + 1


as in, say

5 = 4 + 1

then, for q1.counter =5, that row is now matched with q1_1.counter = 4, the
'previous' row, for the same clientID.




Vanderghast, Access MVP



AJ said:
Thank You, Vanderghast!

Your Example looks like it is working, however I had to make one small
change to your Example to add a Group By on a.TrxID (because there are a
few
instances where the same client had multiple transactions on the same
date).

Here is what I used:

SELECT a.ClientID, a.DATE, LAST(a.AMOUNT) AS CurrAMOUNT, LAST(a.TrxID) AS
CurrTrxID, COUNT(*) AS [COUNTER]
FROM mQuery AS a INNER JOIN mQuery AS b ON (a.DATE>=b.DATE) AND
(a.ClientID=b.ClientID)
GROUP BY a.ClientID, a.DATE, a.AMOUNT, a.TrxID;

The only issue I have is whether there is a way to add ne or two fields to
your example so that I show show the Previous TrxID and possibly the
Previous
AMOUNT on each line...so that I can verify that your example is cycling
through the data correctly?

Thank you for all of your help!

AJ

vanderghast said:
SELECT a.clientID, a.date, LAST(a.amount), LAST(a.trxID), COUNT(*)
FROM mQuery AS a INNER JOIN mQuery AS b
ON a.clientID=b.clientID AND a.date >= b.date
GROUP BY a.clientID, a.date


should do, assuming your actual query is called mQuery. The proposed
query
used two references to it, aliased as a and b, like two fingers, a and
b,
running on a list of items (records) on ONE list (mQuery). For each
position
of the finger 'a', given by a.client, a.date, finger 'b' runs over all
the
possible records
ON (which) a.clientID=b.clientID AND a.date >= b.date
and then, you mentally COUNT the number of records you can so reach.





Vanderghast, Access MVP
Click to expand...
Click to expand...
 
V

vanderghast

..... and since you have ties, you have to change the ON clause, from

ON (a.ClientID=b.ClientID) AND ( a.DATE>=b.DATE)



to



ON (a.ClientID=b.ClientID) AND
(( a.DATE>b.DATE) OR
( a.Date = b.date AND a.trxID >=b.trxID))




so that you will have no tie-counter number: 1, 2, 3, 3, 5 ... as
example; the would be ties (clientID, date) are broken by involving
trxID



Vanderghast, Access MVP
 
Top