Db help

[danreed87]

Hi,



I am wondering if anyone can help me. I use access to track a team's attendance to training and display different reports about the training they attended. When I first set up the database I set up a Lookup Column where I "tick" who attended. With their name I put their team number i.e 01 Dan Reed. The team numbers have since changed (55 Dan Reed). If I change the lookup column data this just adds multiple choices (01 Dan Reed and 55 Dan Reed). I tried to set up conditional formatting so that if "01 Dan Reed" showed up on a report it would change it to and display the new number "55 Dan Reed". I though this would be the easiest way instead of going back and changing 6 years worth of look up column data however it didn't work. I am looking at changing the past data so that any "01 Dan Reed" will now show as "55 Dan Reed". Does anyone have any idea how I could achieve this? I am a total newbie to programming but I am looking for something like - IF "01 Dan Reed" = "55 Dan Reed". I know this may not be correct but should explain what I am looking to do. Any question please ask .

Cheers

Dan

[June7]

Should be a simple edit in lookup table but I am not understanding data structure. If you want to provide db for analysis, follow instructions at bottom of my post.

[NTC]

Depending on your data structure: what is often done is adding a 3rd new column yes/no to your table for 'Archive'; you check that for their old number. Then in your lookup you modify that query so that it has this column and the criteria is <>-1 so that it does not display and Archive rows.

This allows the old data to stay in a database in case you do want to report on items from the past.