Splitting One Record into Two? (1 Viewer)

[BLT1976]

Again, something that stumps me but should be easy for many of you. I have a Health Benefits Dbase where we are importing data from three seperate systems into Access. The information we are getting from benefits specifies either "PPO Basic" or "PPO Plus" for a number of the plans available. On each of these, I need to split them into "BCBS PPO Basic" and "UHC PPO Basic", taking ONE record with 5 pricing options and turning it into TWO records with 5 pricing options.

I have been trying to use an append query to add the records. Currently, my attempt is as follows:

If ([HU and HUF 2003 Test Rates]![Plan Type] ="PPO Basic") Then
["BCBS PPO Basic"]
["UHC PPO Basic"]
End If

Any suggestions?

BLT1976

 

[Pat Hartman]

You need two queries. One to update"PPO Basic" to "BCBS PPO Basic" . The second will be an append query that selects all rows with "BCBS PPO Basic" and appends the rows, with "UHC PPO Basic" substituted as the plan type.

 

[BLT1976]

Making progress..but one question

Pat-

Thanks for the help on this. I was able to create the BCBS addition to the records through the Update Query. In creating my Append Query to add the UHC plans, I have run into a problem. I have selected all the rows and they will append to the same database. Under the "Plan Type" column where I have the "BCBS PPO Basic" term, I have placed "BCBS PPO Basic" as the Criteria. However, I cannot figure out how to substitute the term "UHC PPO Basic" in the new appended records. Thanks for the help.

Brandon

 

[BLT1976]

Figured it out!

Pat-

Sorry about the bother, but I figured it out. I am SO THANKFUL for this forum and all the help it has given me. Kudos and keep up the good work!

Brandon

 

[Pat Hartman]

You will need the plan field to appear twice in the QBE grid. Once to select the old value and the second time to append the new value.

Field: PlanID
Selected checkbox - unchecked
Criteria:"BCBS PPO Basic"
Append To: leave empty

Field: "UHC PPO Basic"
Selected checkbox - checked
Criteria: leave empty
Append To: PlanID

 

[BLT1976]

Thanks

Had tried another way, but your way is much better. Thanks!

Brandon