Replacing Entire Records with a series of fixed new records

[Antonio]

Hi there,




I have a dataset that needs to have its records translated into new, fixed records, but I am unable to do this (I looked up Append and Update records but neither seemed to solve what I am attempting).

Here is a sample of data I have:

ContactID	SolicitCode
1	everydayHero Opt Out
2	Email opt in
3	Email opt out
4	everydayHero Opt Out

The 'everydayHero Opt Out' SolicitCode needs to be replaced with 4 SolicitCode codes:
Email opt out
Postal opt out
Telephone opt out
SMS opt out

So the resulting dataset needs to look like:

ContactID	SolicitCode
1	Email opt out
1	Postal opt out
1	Telephone opt out
1	SMS opt out
2	Email opt in
3	Email opt out
4	Email opt out
4	Postal opt out
4	Telephone opt out
4	SMS opt out

I imagine this is fairly easy to do, any ideas anyone?


Antonio

[isladogs]

There seems to be no pattern between the 4 sets of data shown so a series of update queries probably won't do the job
Also, knowing your dataset from previous thread) I know this is just a tiny part of the whole thing.

You will need to do one or more delete queries to remove records you don't want & several append queries to add new records you do want
Neither is difficult once you have defined the criteria for doing each step

[moke123]

I would open a recordset selecting all records where SolicitCode = everydayHero Opt Out then
loop through the recordset adding the 4 new records along with the contactID. Then delete all the records with everydayHero.
Assuming contactID is a foreign key in the table.

[Antonio]

Actually, I have decided to do this in Excel directly because, at least for me, its quicker.

Thanks for your input again Colin.

As already mentioned, when I get to the stage of 'grouping' data together, I will let you know how I get on with the other issue I posted.

Who marks an issue as resolved on this forum? It is the administrator or me?


Antonio

[isladogs]

You should mark it solved. Click on the Thread Tools dropdown