Count from one Recordset (variable number) and update or insert

[MrsMope]

Hello All,
OK this is a little tough to explain, so let me know if you need more information.

I have a table with the following info: (tblOtherInformation)

ASSGN_TRANS_NBR	SOC_SEC_NBR	LINE_DESC_CD	OTHER_INFO_TEXT	fldPlanSponsor	fldClass	fldLineDescription
428934	         394866113	   D    	                  004#001#A	        001	004	A          
428934	         394866113	   V                         007#001#C	        001	007	C
428934	         394866113	   S    	                  003#001#B	        001	003	B
427358	         278801953	   H                         005#001#A	        001	005	A

Note there are multiple transactions with the same transaction number

I need to update another table (tblStaging) with the information from the last three columns (Sponsor, Class, ELIG)
However, I will not know how many records there are to update.

For example the above listed transactions I would want the following to be updated in the tblStaging

tblStaging:
SSN       Plan3   ELIG3 Plan4 ELIG4 PLan5 ELIG5 Plan6 ELIG6
3xx3[blue]005001  A [/blue][red]004001 A  007001 C 003001 B
[/red]

The blue would be an existing record, the red is what I want to insert.

Also, there may not be a record in the tblStaging, in that case I need to insert a record.

I don't know the best way to go about this, I was thinking of something like:

Create a recordset with the information from tblOtherINformation, do a count of the SSN, for each record with the SSN, check tblStaging for an existing record, if exists update plan records. ( there can be up to 9 plans, so my thought was to take 9 - count of SSN and start updating with that plan number). If doesn't exist, insert record.

 

[PHV]

there can be up to 9 plans
Plan3, Plan4, ..., Plan9 ?
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rubbernilly]

Open a recordset with a grouping on the SSN from the tblOtherInformation table. Your recordcount will tell you how many records you have to process.

Then check that SSN in the tblStaging by opening a recordset there. If it exists, rs.MoveFirst and then rs.Edit; if it does not, rs.AddNew.

I think that is exactly what you were saying at the end, but I'm not sure.

However, I agree with PHV. You should really look at converting those 9 (x2) fields into a related table:

SSN
PlanNum
PlanID
Elig