Concatenate Single Field with Multiple Talbles??? 3

[Elvis72]

I have looked at every suggested reference on this site and I still cannot seem to wrap my head around this.

I have the following:

TblExperience

ExperienceID - Number
Experience - Text

TblResumeExperience

ResumeExpID - Number
ExperienceID - Number
WorkerID - Number

TblResume

WorkerID - Number
Last Name - Text
First Name - Text

I need the result to look like this:

Last Name First Name Experience

Bunny Bugs Gardener, Comedian

Everything I find seems to be referencing one table
or I just don't think I get it??

Your help is sooooo GREATLY appreciated!~

 

[lespaul]

check out FAQ701-4233

Leslie

Have you met Hardy Heron?

 

[Elvis72]

I already tried it, but its not the same.

The TblExperience has the Experience ID and the Experience Text field

But the TblResumeExperience has the many records for each Experience and Worker ID

So, I need something that will tell it to look at the TblExperience for the Experience text field and link that to the TblResumeExperience records by Experience ID and merge them together for each Worker ID.

The examples I see only look at fields that are already on that one table.

In the example if they had people that had a list of last names and the second table had the records of people with multiple last names and you wanted to concatenate that for a list of last names then it would be helpful?

 

[lespaul]

So you still have the module in your database? You would need to do something like this:

SELECT WorkerID, LastName, FirstName, 
Concatenate("SELECT Experience 
FROM tblExperience E
INNER JOIN TblResumeExperience R ON E.ExperienceID = R.ExperienceID WHERE WorkerID " & [WorkerID]) As Experience FROM TblResume

Leslie

Have you met Hardy Heron?

 

[Elvis72]

Yes still have the module in the database.

Here is what I put into the query from your above:

(SELECT WorkerID, LastName, FirstName, Concatenate("SELECT Experience FROM tblExperience INNER JOIN TblResumeExperience R ON E.ExperienceID = R.ExperienceID WHERE WorkerID " & [WorkerID]) As Experience FROM TblResumes)

The only table I have in the query at the moment is the TblResumeExperience.

I get this message:

You ahve written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field.

 

[lespaul]

The only table I have in the query at the moment is the TblResumeExperience.

I don't understand what this means?

What do you mean by this:

Here is what I put into the query from your above:

you should have put my SQL into a new query.

Leslie

 

[Golom]

(SELECT WorkerID, LastName, FirstName, Concatenate("SELECT Experience FROM tblExperience INNER JOIN TblResumeExperience R ON E.ExperienceID = R.ExperienceID WHERE WorkerID [b][red] = [/red][/b]" & [WorkerID]) As Experience FROM TblResumes)

 

[Elvis72]

OK, no matter where I put the statement I still get that same error message?

Whether I put it into its own query or place it in the one I was working with?

 

[Elvis72]

Golom -

Still get the same error?

 

[Golom]

Missing an Alias

(SELECT WorkerID, LastName, FirstName, Concatenate("SELECT Experience FROM tblExperience [red]E[/red] INNER JOIN TblResumeExperience R ON E.ExperienceID = R.ExperienceID WHERE WorkerID = " & [WorkerID]) As Experience FROM TblResumes)

You do have "Concatenate" as a Public Function in a module somewhere don't you?

 

[Elvis72]

Even with the Alias I am still getting the message?

I have modmodule below:

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'(This SQL statement assumes FamID is numeric)
'===================================
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'===================================
'
'If the FamID is a string then the SQL would be
'===================================
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =""" & [FamID] & """") as FirstNames
'FROM tblFamily
'===================================

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

 

[lespaul]

what is the actual SQL of the query you are running?

 

[Golom]

Very Strange!
I set up the following tables
[tt]
TblResumes
WorkerID LastName FirstName
1 Bunny Bugs

TblResumeExperience
ResumeExpID ExperienceID WorkerID
1 1 1
1 2 1

tblExperience
ExperienceID Experience
1 Gardener
2 Comedian
3 Programmer
[/tt]
and then ran this SQL

SELECT WorkerID, FirstName, LastName, 

Concatenate("SELECT Experience FROM tblExperience E  INNER JOIN TblResumeExperience R ON E.ExperienceID = R.ExperienceID WHERE WorkerID = " & [WorkerID]) AS Experience

FROM TblResumes;

and I got this result (which is identical with either DAO or ADO)
[tt]
WorkerID FirstName LastName Experience
1 Bugs Bunny Gardener, Comedian
[/tt]

What is different in your situation?

 

[Elvis72]

Well, I have several:

Query 1

SELECT TblResumes.WorkerID, TblResumes.[First Name], TblResumes.[Last Name], (SELECT WorkerID, LastName, FirstName, Concatenate("SELECT Experience FROM tblExperience E INNER JOIN TblResumeExperience R ON E.ExperienceID = R.ExperienceID WHERE WorkerID = " & [WorkerID]) As Experience FROM TblResumes) AS Expr1
FROM TblResumes;


Query 2:

SELECT (SELECT WorkerID, LastName, FirstName, Concatenate("SELECT Experience FROM tblExperience E INNER JOIN TblResumeExperience R ON E.ExperienceID = R.ExperienceID WHERE WorkerID = " & [WorkerID]) As Experience FROM TblResumes) AS Expr1;


And Lastly Query 3

SELECT TblResumes.WorkerID, TblResumes.[First Name], TblResumes.[Last Name], TblResumes.Country, TblResumes.[Career Title], TblResumes.Nationality, TblResumes.[Resume Link], (SELECT WorkerID, LastName, FirstName, Concatenate("SELECT Experience FROM tblExperience E INNER JOIN TblResumeExperience R ON E.ExperienceID = R.ExperienceID WHERE WorkerID = " & [WorkerID]) As Experience FROM TblResumes) AS Expr1
FROM TblResumes;


So, there they are....I'm just so not putting 2 + 2 together at all.

 

[Elvis72]

The only difference that I can see is on Last Name and First Name I have a space and on Experience ID there is a space.

Other than that it is setup exactly like your above tables.

 

[Golom]

OK. Each of your queries has the form

(Some SQL) As Expr1

The parser assumes when it sees "As Expr1" that you are assigning a field alias. Since "Some SQL" can return more than one value it is telling you that

- It believes "Some SQL" to be a sub-query and ...
- it cannot assign multiple values that "Some SQL" returns to a single field.

Query 1

SELECT WorkerID, 
       LastName, 
       FirstName, 
       Concatenate("SELECT Experience FROM tblExperience E INNER JOIN TblResumeExperience R ON E.ExperienceID = R.ExperienceID WHERE WorkerID = " & [WorkerID]) As Experience 
FROM TblResumes


Query 2: (Same as Query 1)

SELECT WorkerID, 
       LastName, 
       FirstName, 
       Concatenate("SELECT Experience FROM tblExperience E INNER JOIN TblResumeExperience R ON E.ExperienceID = R.ExperienceID WHERE WorkerID = " & [WorkerID]) As Experience 
FROM TblResumes


And Lastly Query 3

SELECT WorkerID, 
       [First Name], 
       [Last Name], 
       Country, 
       [Career Title], 
       Nationality, 
       [Resume Link], 
       Concatenate("SELECT Experience FROM tblExperience E INNER JOIN TblResumeExperience R ON E.ExperienceID = R.ExperienceID WHERE WorkerID = " & [WorkerID]) As Experience 
FROM TblResumes

I note that you are referencing different fields in Query 3 than in Querys 1 & 2. Are there really fields with the names "LastName" and [Last[COLOR=black yellow] [/color]Name] in TblResumes?

 

[PHV]

SELECT WorkerID, [First Name], [Last Name], Country, [Career Title], Nationality, [Resume Link]
, Concatenate("SELECT Experience FROM tblExperience E INNER JOIN TblResumeExperience R ON E.ExperienceID=R.ExperienceID WHERE WorkerID=" & WorkerID) As Experience
FROM TblResumes;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Elvis72]

Good Gracious PHV has done it!~

Thank you both for all your help!~

It is GREATLY appreciated!~