Merging records within a table

  • Thread starter Thread starter kmfrnkn
  • Start date Start date
K

kmfrnkn

I have one table with mutiple entries. The key is email address but the
column site type has a different line for each type of occurrence.

(e-mail address removed) End Cap
(e-mail address removed) Stand Alone

Is there a query I can use to merge these records. I'm tired of cutting and
pasting.
 
this question could be easy or hard depending on what you mean.....

your example is 2 records. are they all 2?...what if some are 2, 3, 4, 5
records.... then your merge would be resulting in a single record with how
many fields??

and what are these fields named?

right now you have an ID (email) and a value. this is at least normalized
and you can find all the values for that ID very quickly.... so it could be
hard if you really want to merge these into common records depending on how
your data actually exists now...

it all kind of depends on what you really are trying to accomplish...
 
Thanks - that's what I'm looking to do exactly.

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe you're talking about displaying the data like this:

(e-mail address removed) End Cap, Stand Alone

This is usually done in the display layer of the application (a report),
and not in the query. There are methods to do this in the query, but
they require a VBA function. If you're using SQL Server as your back
end you can create a stored procedure like this:

PROCEDURE MergeList( <your parameters, if required> )
AS

DECLARE @colors VARCHAR(1024)

SELECT
@colors = COALESCE(@colors + ',', '') + Color --As Colours
FROM
Colors

And call it from the front-end.

The above example table would be like this:

Color
=====
red
white
blue
purple
black

The stored procedure's result would look like this:

red, white, blue, purple, black

If using a VBA routine, the query would look like this:

SELECT DISTINCT email_address, SiteTypeCommaList(email_address)
FROM table_name
WHERE <your criteria>

The VBA routine would look like this:

Public Function SiteTypeCommaList(emailAddress as String) As String

' We'll need to query the table for each email address' site types.
CONST SQL = "SELECT site_type FROM table_name WHERE email_address="

Dim db As DAO.Database, rs As DAO.Recordset
Dim Sites as string

' Open a recordset to get the site_types from the table for the
' indicated email address.
Set db = CurrentDB
Set rs = db.OpenRecordset(SQL & "'" & emailAddress & "'")

' Make the comma delimited list of site types
While Not rs.EOF
Sites = Sites & "," & rs!site_type
rs.MoveNext
Loop

' Remove the leading comma
If Len(Sites)>0 Then Sites = Mid$(Sites,2)

' Return the data to the query
SiteTypeCommaList = Sites

End Function

Be sure to substitute your table's name and the correct column names in
the query & the function.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSL2zwIechKqOuFEgEQJpoQCg/JBX5888IDjqWf/Yui5ywTlDZfkAoLYc
8aGOayEVxqzTogwqNbsuWEbl
=Ps+1
-----END PGP SIGNATURE-----
Click to expand...
 
Back
Top