combine multiple recs into one with repeating fields

  • Thread starter Thread starter K Essmiller
  • Start date Start date
K

K Essmiller

I have a file with beneficiary names for life insurance. The input file has
multiple names for the member. The file format I need to load into our system
has one record per member, but has repeating fields such as name1, name2,
name3, pct1, pct2, pct3. I don't know how to do this in Access and I'm not
great with VBA code.
 
Try this --
SELECT ccount, name1 AS Benificary, pct1 AS Percent
FROM YourTable
UNION ALL SELECT ccount, name2 AS Benificary, pct2 AS Percent
FROM YourTable
WHERE name2 Is Not Null AND pct2 Is Not Null
UNION ALL SELECT ccount, name3 AS Benificary, pct3 AS Percent
FROM YourTable
WHERE name3 Is Not Null AND pct3 Is Not Null
....
UNION ALL SELECT ccount, nameX AS Benificary, pctX AS Percent
FROM YourTable
WHERE nameX Is Not Null AND pctX Is Not Null;
 
I have a file with beneficiary names for life insurance. The input file has
multiple names for the member. The file format I need to load into our system
has one record per member, but has repeating fields such as name1, name2,
name3, pct1, pct2, pct3. I don't know how to do this in Access and I'm not
great with VBA code.
Click to expand...

If you want to take an arbitrary number of records with different names and
generate a single record with all the names separated by commas, you'll need
some VBA. There's exactly this code available at

http://www.mvps.org/modules/mdl0004.htm

Copy and paste the code at this site into a new Module, and follow the
instructions in the comments at the site.
 
Back
Top