combine duplicate values into one field

[ferrisj]

I have a porrly formatted table from combining too many data sources.

It looks like this

Name Address Source
__________ ___________ ________
Josh 123 Main St EV
Josh 123 Main St PV
Josh 123 Main St DH

I of course do not want to display the duplicates and would like to write a query that displays

Name Address Source
__________ ___________ ________
Josh 123 Main St EV, PV, DH


is this doable?

 

[lespaul]

check out FAQ701-4233

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[ferrisj]

I am getting an erorr in this line:

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

 

[Golom]

... and the error would be ???

 

[ferrisj]

sorry.


my SQL is:

SELECT Last, First, Address, [City, [Phone Bank List].State, Zip, Facility, Phone, Concatenate("SELECT SOURCE FROM [Phone Bank List] WHERE id =" & [id] as sources)
FROM [Phone Bank List];

It was not the code. I was not running it properly now it says there s a synatx error and that an oeprator is missing.

 

[lespaul]

unless it's just a typo, you've got an extra [:

SELECT Last, First, Address, [City, [Phone Bank List].State, Zip, Facility, Phone, Concatenate("SELECT SOURCE FROM [Phone Bank List] WHERE id =" & [id] as sources)
FROM [Phone Bank List];

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Golom]

SELECT [red][[/red]Last[red]][/red], [red][[/red]First[red]][/red], Address, [City[red]][/red], [Phone Bank List].[red][[/red]State[red]][/red], Zip, Facility, Phone, 
Concatenate("SELECT SOURCE [red]as sources [/red]FROM [Phone Bank List] WHERE id =" & [id] )
FROM [Phone Bank List];

"Last" and "First" are the names of aggregate functions.
You were missing a "]" after [City
"State" is a reserved word
Your alias was in the wrong place.

You might also need single quotes around [ID] if it is a text field.

 

[ferrisj]

if i didnt have an id field, could i substitute someting like

[Last]&[First]&[Phone]

 

[ferrisj]

when i try:

SELECT [Phone Bank List].Last, [Phone Bank List].First, [Phone Bank List].Address, [Phone Bank List].City, [Phone Bank List].State, [Phone Bank List].Zip, [Phone Bank List].Facility, [Phone Bank List].Phone, Concatenate("SELECT SOURCE as sources FROM [Phone Bank List] WHERE id =" & '[id]') AS Expr1, [Last] & [First] & [Phone] AS id
FROM [Phone Bank List];

I get "No Value specified for desired parameters

 

[SkipVought]

1. You disregarded the advice that golom gave you regarding first, last etc.

2. You did not terminate the Concatenate function with a PARENTHESES.

Skip,

_[red][/red]

 

[ferrisj]

apologies:

SELECT [Last], [First], Address, City, State, Zip, Facility, Phone, Concatenate("SELECT SOURCE FROM [Phone Bank List] WHERE id =" & [id]) AS SOURCES, [Last] & [First] & [Phone] AS id
FROM [Phone Bank List];

I am still getting an error of "No value given for one of morerequired parameters)

 

[Golom]

Is [ID] a field that exists in the table already? I infer that it must be since the query that you're sending to the Concatenate function references it.

If it is then attempting to alias a derived field with the same name will cause a circular reference.

That message usually means that you have referenced a field that doesn't exist in the base table so SQL assumes that it must be a parameter.

 

[ferrisj]

it does. I got it to work,

but it is showing the same value for each record in my sources, which I know is not correct. this value is concatenating, but it is a huge string of many many values, when a record would have at the most 5 values

SELECT DISTINCT [Phone Bank List].Last, [Phone Bank List].First, [Phone Bank List].Address, [Phone Bank List].City, [Phone Bank List].State, [Phone Bank List].Zip, [Phone Bank List].Facility, [Phone Bank List].Phone, Concatenate("SELECT SOURCE as sources FROM [Phone Bank List] WHERE id =" & '[id]') AS SOURCES, [Phone Bank List].id
FROM [Phone Bank List];

 

[Golom]

SELECT DISTINCT [Phone Bank List].Last, [Phone Bank List].First, [Phone Bank List].Address, [Phone Bank List].City, [Phone Bank List].State, [Phone Bank List].Zip, [Phone Bank List].Facility, [Phone Bank List].Phone, Concatenate("SELECT SOURCE as sources FROM [Phone Bank List] WHERE id =[red]'[/red]" & [id][red] & "'"[/red]) AS SOURCES, [Phone Bank List].id
FROM [Phone Bank List];