query to update Trans #

[Ted]

Hi all,

I have a Claims system where there are multiple transactions per claim. Each
of these transactions has a Transaction ID. Each Transaction now needs a
Transaction # per claim. Going forward this is not a problem but is there an
easy way to go back and update the TransactionNumber field? I have a
DateTime
Stamp (DateEntered) to let me know the order of the transactions. So for
example
i could have 100 transactions which will have Transaction IDs of 1-100 but
now they need a Transaction # per Claim. If a Claim has 10 Transactions they
should be numbered 1-10 and if another claim has 8 transactions they should
be numbered 1-8.

any help or direction would be very much appreciated
TIA
Ted

 

[Jeff Boyce]

Ted

What is the relationship between TransactionID and Transaction#?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ted]

There is no real relationship bt the two. Transaction ID is the primary key.
Its an autonumber field. The TransactionNumber field is a field that will
number the transactions per claim. I was wondering if there was a query that
i could run that could update the Transaction Number field...number the
transactions by Date Entered or Transaction ID sorted Ascending. It has to
group by Claim number tho.

Thanks Jeff

 

[Jeff Boyce]

Ted

As you may know, Access Autonumber fields are NOT guaranteed to be
sequential, so I can see why you wouldn't want to try to make your
TransactionNumber field match it.

Are you trying to get a TransactionNumber so you can count how many you
have? If so, remember that you can do a query to get the count.

Are you trying to sort a set of records? If so, you already have a
date/time field, right?

What use are you planning to make of this (new) TransactionNumber, if you
only had it done?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ted]

I'm not using the Transaction Number to sort the records. The transaction id
is merely an autonumber primary key field that i added. Its not linked
anywhere and doesn't relate to what i need to accomplish w the Transaction
Number field.

Its a requirement for a company that I have to report data to. If I send
them 8 transactions for one claim they want to be able to see then numbered
in order that they happened. Just having them in order isn't enough. They
want to be able to see that the 7th transaction is numbered 7. If I send
them 12 transactions for another claim they want to be able to see them
numbered 1-12 and that the 7th transaction of that claim is also numbered 7.

Ted

 

[Jeff Boyce]

Ted

Thanks for the clarification.

If this were mine, and if there weren't too many (you get to decide how much
is too much), I'd do them "manually", since the "automated" way would take
(me) some time to build.

If there were a lot, or if this was going to be a recurring task, I'd
probably approach it with a coding solution, something that would:

* retrieve a recordset of all the transactions without transaction number
* ... sorted by Claim and by ActionDateTime
* go to the next record (first time through this will be record #1)
* check to see if the Claim number has changed
* if it has (and for the first record, it has), give the record
TransactionNumber=1
* if it hasn't, add 1 to the previous TransactionNumber
* rinse and repeat (back up to step#3)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

It seems to me that you could use a RANKING query to establish the number.

SELECT Claim, ActionDate
, ( SELECT Count(*)
FROM YourTable as X
WHERE X.Claim = YourTable.Claim AND
X.ActionDate <= YourTable.ActionDate) as RecNumber
FROM YourTable


An alternative MIGHT be the following

SELECT YourTable.Claim
, YourTable.ActionDate
, Count(X.ActionDate) as RecNumber
FROM YourTable INNER JOIN YourTable as X
ON YourTable.Claim = X.Claim AND
YourTable.ActionDate <= X.ActionDate
GROUP BY YourTable.Claim, YourTable.ActionDate

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..