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

  • Thread starter Thread starter AJ
  • Start date Start date
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.
 
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
 
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
 
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...
 
..... 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
 
Back
Top