Trace records through a table

I have a table of checks. The PK on the table is the CheckNum field. The
table also has a column ReissuedCheckNum to indicate if the CheckNum was
reissued with a new CheckNum. I'd like to be able to trace a history of the
checks through the table and then tag them with an ID, as in the following
example. There is no limit as to the number of times a check can be
reissued, so I assume the process would need to loop through the table. I'm
not familiar with VB, so any help would be appreciated.

CheckNum ReissuedCheckNum ID
500 501 1
501 503 1
502 505 2

503 1
504
505 2

You can use an Update Query to update the ID value, without using VBA

You'll need to do a self inner join to be able to link the CheckNum to the
Reissue num. Add the same table to the query twice, then link the two fields.

Steve Clark
FMS, Inc.

"Mike4171" wrote:

I have a table of checks. The PK on the table is the CheckNum field. The
table also has a column ReissuedCheckNum to indicate if the CheckNum was
reissued with a new CheckNum. I'd like to be able to trace a history of the
checks through the table and then tag them with an ID, as in the following
example. There is no limit as to the number of times a check can be
reissued, so I assume the process would need to loop through the table. I'm
not familiar with VB, so any help would be appreciated.

CheckNum ReissuedCheckNum ID
500 501 1
501 503 1
502 505 2

503 1
504
505 2

Thanks. Will this update all of the records with the same ID? I can see how
check 500 and then 501 would get tagged correctly, but I would think that 503
would not get tagged with ID 1. If I'm not making myself clear please let me
know and I can explain with more detail.

"S.Clark [Access MVP]" wrote:

You can use an Update Query to update the ID value, without using VBA

You'll need to do a self inner join to be able to link the CheckNum to the
Reissue num. Add the same table to the query twice, then link the two fields.

Steve Clark
FMS, Inc.

"Mike4171" wrote:

I have a table of checks. The PK on the table is the CheckNum field. The
table also has a column ReissuedCheckNum to indicate if the CheckNum was
reissued with a new CheckNum. I'd like to be able to trace a history of the
checks through the table and then tag them with an ID, as in the following
example. There is no limit as to the number of times a check can be
reissued, so I assume the process would need to loop through the table. I'm
not familiar with VB, so any help would be appreciated.

CheckNum ReissuedCheckNum ID
500 501 1
501 503 1
502 505 2

503 1
504
505 2

On Feb 20, 2:47 pm, Mike4171
wrote:
Thanks. Will this update all of the records with the same ID? I can see how
check 500 and then 501 would get tagged correctly, but I would think that 503
would not get tagged with ID 1. If I'm not making myself clear please let me
know and I can explain with more detail.


Mike4171,

Some thoughts (but no guarrentee)
If you can assum that the check numbers are ALWAYS going to be
ascending (or maybe add date to the process as a higher order sort so
that you are looking at the check file in the sequence in which they
were issued.)

Pass 1
Update query Assign a unique ID to every check that has a re-issue
check.

Pass 2
Update query that is left join
First check table with criteria that it has a reissue check number
The first table is linked to the second check table matching the
reissue check number (from the left table) to the primary check number
of the right table.
Update the ID of the second table with the ID from the first table.

I think this will push the number up the chain, Obviously the end
result will be that some IDs will be skipped because they have been
overridden by an earlier ID.

Ron

Ron,

Thanks, I think this works perfectly. I've tested it numerous times under
different conditions and it always seems to work. I think I've handled the
condition of having the check number is always in ascending order by placing
the PK on that field, so it is always ordered by the check number. Please
let me know if you believe that isn't the case. Also, could you give me a
brief explanation of how the second query works? I've looked at it but I
can't explain to myself what it is doing. Thanks again for the help.

Mike

"Ron2006" wrote:

On Feb 20, 2:47 pm, Mike4171
wrote:
Thanks. Will this update all of the records with the same ID? I can see how
check 500 and then 501 would get tagged correctly, but I would think that 503
would not get tagged with ID 1. If I'm not making myself clear please let me
know and I can explain with more detail.


Mike4171,

Some thoughts (but no guarrentee)
If you can assum that the check numbers are ALWAYS going to be
ascending (or maybe add date to the process as a higher order sort so
that you are looking at the check file in the sequence in which they
were issued.)

Pass 1
Update query Assign a unique ID to every check that has a re-issue
check.

Pass 2
Update query that is left join
First check table with criteria that it has a reissue check number
The first table is linked to the second check table matching the
reissue check number (from the left table) to the primary check number
of the right table.
Update the ID of the second table with the ID from the first table.

I think this will push the number up the chain, Obviously the end
result will be that some IDs will be skipped because they have been
overridden by an earlier ID.

Ron

I will see if I can make sense of my brainstorm.
Obviously, the first query/pass simply assigns an ID to any records
that have been reissued.

After the first pass the table looks like this

CheckNum ReissuedCheckNum ID
500 501 1 Record A
501 503 2 Record B
502 505 3 Record
C
503 Record D
504 Record E
505 Record F

Essentially, what the second pass does is turn the table upside down.
On the second pass the records come in the following sequence

The left table will only select 3 records.
CheckNum ReissuedCheckNum ID
500 501 1 Record A
501 503 2 Record B
502 505 3 Record
C

But when we join the second occurance to it (if it was a display
instead of an update it would look like this
CheckNum ReissuedCheckNum ID
500 501 1 Rec A ---- 501 503 2 Rec B

-----------------------------------------
501 503 2 Rec B ---- 503 Rec D
-------------------------------

When we update the first two we replace ID 2 with ID 1
Then we go to the next pair and it is Record B again with a new ID of
1 now
so we are then updateing Record D with the NEW rec ID of 1
The key is that we see the same record 2 times once on the receiving
side of the update
and once on the supplying side of the update.


Hope this clairified the logic.

Ron

Thanks, this clears it up for me.

Mike

"Ron2006" wrote:

I will see if I can make sense of my brainstorm.
Obviously, the first query/pass simply assigns an ID to any records
that have been reissued.

After the first pass the table looks like this

CheckNum ReissuedCheckNum ID
500 501 1 Record A
501 503 2 Record B
502 505 3 Record
C
503 Record D
504 Record E
505 Record F

Essentially, what the second pass does is turn the table upside down.
On the second pass the records come in the following sequence

The left table will only select 3 records.
CheckNum ReissuedCheckNum ID
500 501 1 Record A
501 503 2 Record B
502 505 3 Record
C

But when we join the second occurance to it (if it was a display
instead of an update it would look like this
CheckNum ReissuedCheckNum ID
500 501 1 Rec A ---- 501 503 2 Rec B

-----------------------------------------
501 503 2 Rec B ---- 503 Rec D
-------------------------------

When we update the first two we replace ID 2 with ID 1
Then we go to the next pair and it is Record B again with a new ID of
1 now
so we are then updateing Record D with the NEW rec ID of 1
The key is that we see the same record 2 times once on the receiving
side of the update
and once on the supplying side of the update.


Hope this clairified the logic.

Ron