Query Incremental counter

  • Thread starter Thread starter Craig
  • Start date Start date
C

Craig

Well, it's not exactly an incremental counter but here's
what I'm looking for.

I'm using Excel 2002. I have a query with 2
fields: "Account" and "Transaction Type". I'd like to
add a calculated field that returns a '1' or '2' based on
when a particular "Transaction Type" ('Opening Bal')
occurs. Then, repeat that value until the next break
in 'Account'. For example:

Account Trans Type Expr1
1111 Credit 1
1111 Debit 1
1111 Wire Trans 1
1111 Opening Bal 2
1111 Debit 2
1111 Credit 2
1111 Debit 2
1111 Wire Trans 2
2222 Debit 1
2222 Credit 1
2222 Wire Trans 1
2222 Opening Bal 2
2222 Debit 2
2222 Credit 2
..
..
..

Hopefully, you can see where I'm going with this.
Everytime 'Opening Bal' occurs I would like the value '2'
to continue until the next break in "Account".

Thanks in advance for any help.
 
Craig said:
Well, it's not exactly an incremental counter but here's
what I'm looking for.

I'm using Excel 2002. I have a query with 2
fields: "Account" and "Transaction Type". I'd like to
add a calculated field that returns a '1' or '2' based on
when a particular "Transaction Type" ('Opening Bal')
occurs. Then, repeat that value until the next break
in 'Account'. For example:

Account Trans Type Expr1
1111 Credit 1
1111 Debit 1
1111 Wire Trans 1
1111 Opening Bal 2
1111 Debit 2
1111 Credit 2
1111 Debit 2
1111 Wire Trans 2
2222 Debit 1
2222 Credit 1
2222 Wire Trans 1
2222 Opening Bal 2
2222 Debit 2
2222 Credit 2
.
.
Click to expand...
Hi Craig,

I believe you will need to have a Transaction Date
because in a query there is no order to the results
until you provide an ORDER BY clause
(versus Excel where transactions are locked into
"this row, then next row, then next row...always).

So...if the order of transactions occurs in the order
you have given for a each account because they
occurred in time that way (and you have recorded
those dates in a TransactionDate column)...
(I hope the above made sense)

for example:

Account Trans Type TransDate
1111 Credit 1/1/2004
1111 Debit 1/5/2004
1111 Wire Trans 1/10/2004
1111 Opening Bal 2/5/2004
1111 Debit 2/6/2004
1111 Credit 2/9/2004
1111 Debit 2/10/2004
1111 Wire Trans 2/20/2004

then your Expr1 would return "1"
if its TransDate occurred before
the TransDate of this account's Trans Type
of "Opening Bal"

else

Expr1 = 2

There are many directions to go from here...

Does every Account have a Trans Type of
"Opening Bal"?

Can an Account have more than one "row"
with Trans Type of "Opening Bal"?

If answer to second question is no..

You could create a preliminary query that
returns each "Account" and the TransDate
of "Opening Bal"

qryOBDate:

SELECT
Account,
TransDate As OBTransDate
FROM
yourtable
WHERE
[Trans Type] = 'Opening Bal';

Then join original table to this query
and test to get 1 or 2.

something like:

SELECT
Account,
[Trans Type],
SWITCH([TransDate]>=[OBTransDate],2,TRUE,1) AS Expr1
FROM
yourtable
LEFT JOIN
qryOBDate
ON
yourtable.Account = qryOBDate.Account
ORDER BY
Account, TransDate;

Again...many ways but "depends"...

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
Gary:

Thanks - that does make more sense. Each record does have
a trans date and clear date. I'm going to try your example
query logic and go from there.

Thanks again...
-----Original Message-----

Craig said:
Well, it's not exactly an incremental counter but here's
what I'm looking for.

I'm using Excel 2002. I have a query with 2
fields: "Account" and "Transaction Type". I'd like to
add a calculated field that returns a '1' or '2' based on
when a particular "Transaction Type" ('Opening Bal')
occurs. Then, repeat that value until the next break
in 'Account'. For example:

Account Trans Type Expr1
1111 Credit 1
1111 Debit 1
1111 Wire Trans 1
1111 Opening Bal 2
1111 Debit 2
1111 Credit 2
1111 Debit 2
1111 Wire Trans 2
2222 Debit 1
2222 Credit 1
2222 Wire Trans 1
2222 Opening Bal 2
2222 Debit 2
2222 Credit 2
.
.
Click to expand...
Hi Craig,

I believe you will need to have a Transaction Date
because in a query there is no order to the results
until you provide an ORDER BY clause
(versus Excel where transactions are locked into
"this row, then next row, then next row...always).

So...if the order of transactions occurs in the order
you have given for a each account because they
occurred in time that way (and you have recorded
those dates in a TransactionDate column)...
(I hope the above made sense)

for example:

Account Trans Type TransDate
1111 Credit 1/1/2004
1111 Debit 1/5/2004
1111 Wire Trans 1/10/2004
1111 Opening Bal 2/5/2004
1111 Debit 2/6/2004
1111 Credit 2/9/2004
1111 Debit 2/10/2004
1111 Wire Trans 2/20/2004

then your Expr1 would return "1"
if its TransDate occurred before
the TransDate of this account's Trans Type
of "Opening Bal"

else

Expr1 = 2

There are many directions to go from here...

Does every Account have a Trans Type of
"Opening Bal"?

Can an Account have more than one "row"
with Trans Type of "Opening Bal"?

If answer to second question is no..

You could create a preliminary query that
returns each "Account" and the TransDate
of "Opening Bal"

qryOBDate:

SELECT
Account,
TransDate As OBTransDate
FROM
yourtable
WHERE
[Trans Type] = 'Opening Bal';

Then join original table to this query
and test to get 1 or 2.

something like:

SELECT
Account,
[Trans Type],
SWITCH([TransDate]>=[OBTransDate],2,TRUE,1) AS Expr1
FROM
yourtable
LEFT JOIN
qryOBDate
ON
yourtable.Account = qryOBDate.Account
ORDER BY
Account, TransDate;

Again...many ways but "depends"...

Please respond back if I have misunderstood.

Good luck,

Gary Walter



.
Click to expand...
 
Back
Top