Concatenate Single Field 1

[Elvis72]

At the moment I have the following:

TblPlantType
PlantTypeID
PlantType

TblPlantTypeList
PlantTypeID
ProjectTypeID
JobProposalNo

TblProjectInformation
ProjectID
TitleID
WorkerID
JobProposalNo
Requisition No
Mobilization Date
Demobilization Date

TblResumes
WorkerID
First Name
Last Name
Address
City
State
Zip Code

Right now I have the following Find As You Type Form:

WorkerID First Name Last Name Country Nationality Career Title Resume Link Experience

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

All other fields coem from TblResume.

I need to make a field for PlantType like Experience, but they are tied to the JobProposalNo, which I am having a hard time linking the Plant Type to the JobProposalNo and WorkerID without making 11k records?

Is it even possible?

 

[PHV]

Something like this ?
PlantType: Concatenate("SELECT PlantType FROM TblPlantType P INNER JOIN TblPlantTypeList L ON P.PlantTypeID=L.PlantTypeID WHERE JobProposalNo=" & JobProposalNo)

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

 

[Elvis72]

PHV -

I am getting this error:

No value given for one or more required parameters

And when I debug it highlights:

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

The form as it stands now is this:

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

Somehow I need to link WorkerID with PlantType with JobProposalNo.

If I add JobProposalNo to the above query it gives me a record for every person for every JobProposaNo.

 

[PHV]

PlantType: Concatenate("SELECT PlantType FROM (TblPlantType P INNER JOIN TblPlantTypeList L ON P.PlantTypeID=L.PlantTypeID) INNER JOIN TblProjectInformation I ON L.JobProposalNo=I.JobProposalNo WHERE WorkerID=" & WorkerID)

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

 

[Elvis72]

Doing Happy Dance!~

Ended up with:

PlantType: Concatenate("SELECT PlantType FROM (TblPlantType P INNER JOIN TblPlantTypeList L ON P.PlantID=L.PlantID) INNER JOIN TblProjectInformation I ON L.JobProposalNo=I.JobProposalNo WHERE WorkerID=" & [WorkerID])

And it works amazingly!~

Thanks soooo very much!~

 

[Elvis72]

One Question....

Would it be possible to not show duplicates?

Right now it is showing this:

Last Name First Name Country Experience Plant Type

Smith Joe USA Startup, Ethylene,
Operations, Ethylene,
Training Olefins

Because they will be assigned to more than one project but they might be working on the same Plant Type.

?

 

[PHV]

Have a look at the DISTINCT predicate:
PlantType: Concatenate("SELECT DISTINCT PlantType FROM ...

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

 

[Elvis72]

I was just looking at that on another post!~

Thanks soooo very much!~