Merge data in fields

[Guest]

From a table with data in the following format:

Name Data
CB1 xxx
CB2 yyy
CB3 zzz
AT1 mm
AT7 ww

I want to create a new table in the following format:

Name Data
CB xxx, yyy, zzz
AT mm, ww

 

[John W. Vinson]

From a table with data in the following format:

Name Data
CB1 xxx
CB2 yyy
CB3 zzz
AT1 mm
AT7 ww

I want to create a new table in the following format:

Name Data
CB xxx, yyy, zzz
AT mm, ww

Why?

You're going from a properly normalized table to a new table with non-atomic,
redundant, improperly structured fields.

If you want a *recordset* in the following format, for the purpose of
generating a report, use a Query with some VBA code:

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

If you really, really want a flawed, hard to edit, hard to search table <g>,
base an Append or MakeTable query on the query using the function.

John W. Vinson [MVP]

 

[Guest]

John,

Thanks for replying.

In a table of app. 5000 records, I need to search for names whose prefixes
occur multiple times, (e.g., CB1, CB2, CB3), and then pull data from these
common records. I thought that it would be easier to merge the data from the
common records into a single record, and pull data from the merged record.

jsccorps

 

[John W. Vinson]

John,

Thanks for replying.

In a table of app. 5000 records, I need to search for names whose prefixes
occur multiple times, (e.g., CB1, CB2, CB3), and then pull data from these
common records. I thought that it would be easier to merge the data from the
common records into a single record, and pull data from the merged record.

Well... given the way SQL queries work it would be considerably harder, not
easier.

You can do a totals query grouping by the prefix and counting records; it's
not quite clear how the "prefix" is stored or what you want to "pull".

John W. Vinson [MVP]

 

[John Spencer]

Perhaps you can use the Duplicates query wizard to get what you want? Or
you can construct a query that looks like the following

SELECT *
FROM YourTable
WHERE [Name Field] in
(SELECT [Name Field]
FROM YourTable
GROUP BY [Name Field]
HAVING COUNT(*) > 1)
ORDER BY [Name Field]

That would return all records where the value of [Name field] appeared more
than one time in the table.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Once I merge the common names into a single record, I would count all of the
occurences of xxx's, yyy's, zzz's etc. (my idea was to count the number of
times the character set occurs in the merged string.) Is there a 'simple'
way to do the counts without first doing a merge?

 

[John W. Vinson]

Once I merge the common names into a single record, I would count all of the
occurences of xxx's, yyy's, zzz's etc. (my idea was to count the number of
times the character set occurs in the merged string.) Is there a 'simple'
way to do the counts without first doing a merge?

Yes; a Totals query, grouping by the prefix and Counting the primary key. I
still don't know the structure of your table so I can't write the query, but
the query design window should let you do this; the Greek Sigma icon (looks
like a sideways M) makes a query into a Totals query.

John W. Vinson [MVP]