Prev: access97, citrix, slow form refresh
Next: Opening a Form
From: emanning on 10 Mar 2010 16:07
Using A2007. I've got an accdb that links reasons given for treatment
refusal to a list of patients. Currently the reasons are numbered 1
thru 70. These reasons are stored in a lookup table with a PK
autonumber, called ReasonID, and the reason description. There is a
one-to-many relationship to another table that stores patient ID and
ReasonsID.

The user wants me to replace the current reasons table with a new
table. The "new" reasons are numbered 1 thru 19 with new
descriptions. For example, Reason #1 is now Reason #15, Reason #15 is
now Reason #28. Reason #18 is now #12. How would I update the related
table with the new ReasonID? At first I thought a simple find-and-
replace would work. But if I replace all of reason #1 with Reason
#15, then replace reason #15 with reason #28 then I've actually
replaced all Reason #1 with Reason #28, haven't I?

I thought about working backwards in the list. Replace Reason 15 with
28, then replace 1 with 15. But that won't work because I have to
replace 18 with 12, then 12 with 15. Then I've replaced all 12's with
15's.

Hopefully I'm missing something obvious here. Any help or advice
would be appreciated.
From: emanning on 10 Mar 2010 16:37
On Mar 10, 3:07 pm, emanning <emann...(a)kumc.edu> wrote:
> Using A2007.  I've got an accdb that links reasons given for treatment
> refusal to a list of patients.  Currently the reasons are numbered 1
> thru 70.  These reasons are stored in a lookup table with a PK
> autonumber, called ReasonID, and the reason description.  There is a
> one-to-many relationship to another table that stores patient ID and
> ReasonsID.
>
> The user wants me to replace the current reasons table with a new
> table.  The "new" reasons are numbered 1 thru 19 with new
> descriptions.  For example, Reason #1 is now Reason #15, Reason #15 is
> now Reason #28. Reason #18 is now #12.  How would I update the related
> table with the new ReasonID?  At first I thought a simple find-and-
> replace would work.  But if I replace all of reason #1 with Reason
> #15, then replace reason #15 with reason #28 then I've actually
> replaced all Reason #1 with Reason #28, haven't I?
>
> I thought about working backwards in the list.  Replace Reason 15 with
> 28, then replace 1 with 15.  But that won't work because I have to
> replace 18 with 12, then 12 with 15.  Then I've replaced all 12's with
> 15's.
>
> Hopefully I'm missing something obvious here.  Any help or advice
> would be appreciated.

I think I may have answered my own question. First, create a new
column in the patient table and call it NewReasonID. Run the table
thru an update query, once for each reason, and if "old" reason = 1
then NewReasonID = 15, if "old" reason = 15 then NewReasonID = 28, and
so on. When finished, delete the old reason column and rename
NewReason to what the old reason column was named. Re-establish the
relationship with the new reasons table and I should be good to go.

Please advise if that's not the best way to handle it. Thanks.
From: Salad on 10 Mar 2010 18:39
emanning wrote:
> On Mar 10, 3:07 pm, emanning <emann...(a)kumc.edu> wrote:
>
>>Using A2007. I've got an accdb that links reasons given for treatment
>>refusal to a list of patients. Currently the reasons are numbered 1
>>thru 70. These reasons are stored in a lookup table with a PK
>>autonumber, called ReasonID, and the reason description. There is a
>>one-to-many relationship to another table that stores patient ID and
>>ReasonsID.
>>
>>The user wants me to replace the current reasons table with a new
>>table. The "new" reasons are numbered 1 thru 19 with new
>>descriptions. For example, Reason #1 is now Reason #15, Reason #15 is
>>now Reason #28. Reason #18 is now #12. How would I update the related
>>table with the new ReasonID? At first I thought a simple find-and-
>>replace would work. But if I replace all of reason #1 with Reason
>>#15, then replace reason #15 with reason #28 then I've actually
>>replaced all Reason #1 with Reason #28, haven't I?
>>
>>I thought about working backwards in the list. Replace Reason 15 with
>>28, then replace 1 with 15. But that won't work because I have to
>>replace 18 with 12, then 12 with 15. Then I've replaced all 12's with
>>15's.
>>
>>Hopefully I'm missing something obvious here. Any help or advice
>>would be appreciated.
>
>
> I think I may have answered my own question. First, create a new
> column in the patient table and call it NewReasonID. Run the table
> thru an update query, once for each reason, and if "old" reason = 1
> then NewReasonID = 15, if "old" reason = 15 then NewReasonID = 28, and
> so on. When finished, delete the old reason column and rename
> NewReason to what the old reason column was named. Re-establish the
> relationship with the new reasons table and I should be good to go.
>
> Please advise if that's not the best way to handle it. Thanks.


IF you had 70 reasons, and now 19 (don't know why you'd have a #28) I
might copy the structure over and create the 19 records/reasons in the
blank table. Rename the tables; Reason becomes ReasonOld, Reason1
becomes Reason.

In the old table ReasonOld, add a column to plug in the new values; ex:
reasons 28, 38, 55, and 66 might now be reason 1. Then run a query on
table(s) that have the reason code and update to the the the new reason
code in the new column.

Then save table ReasonOld until you are sure everything worlds right and
you can reference the old code values if needed by the staff.
 | 
Pages: 1
Prev: access97, citrix, slow form refresh
Next: Opening a Form