combine multiple recs into one with repeating fields
in [Queries]
|
Prev: Building/Storing a passthrough:How to assign connection stringand make passthrough
Next: How to combine queries????
From: K Essmiller on 21 Apr 2010 17:11 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.
From: KARL DEWEY on 22 Apr 2010 00:36 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; -- Build a little, test a little. "K Essmiller" wrote: > 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.
From: John W. Vinson on 22 Apr 2010 01:21
On Wed, 21 Apr 2010 14:11:01 -0700, K Essmiller <KEssmiller(a)discussions.microsoft.com> wrote: >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. 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. -- John W. Vinson [MVP] |