Concatenation Help - faq701-4233 1

[darall]

I am trying to use the FAQ701-4233 and am having problems.

I have a query that I am relating two tables:

tblEngagement_ClarifyData_Case
strCaseNum (Key)
strCaseTitle
dtmCaseOpen
dtmCaseClose
strCaseOwner

tblEngagement_ClarifyData_Subcase
strSubcaseNum (Key)
strCaseNumber
dtmSubcaseOpen
dtmSubcaseClose
strOwenrFN
strOwenrLN

The tables are related as follows:
tblEngagement_ClarifyData_Case.strCaseNum = tblEngagement_ClarifyData_Subcase.strCaseNumber


When I run the query I receive Run-time error '3061': Too few parameters. Expected 2.

The SQL I am using in the query is as follows:
=====================================

SELECT tblEngagement_ClarifyData_Case.CaseNum, tblEngagement_ClarifyData_Case.CaseTitle, Concatenate("SELECT tblEngagement_ClarifyData_Case.CaseNum, tblEngagement_ClarifyData_Case.CaseTitle,[ownerLN] FROM tblEngagement_ClarifyData_Subcase WHERE tblEngagement_ClarifyData_Case.CaseNum = """ & [tblEngagement_ClarifyData_Subcase].[CaseNumber] & """") AS SubcaseOwners
FROM tblEngagement_ClarifyData_Case INNER JOIN tblEngagement_ClarifyData_Subcase ON tblEngagement_ClarifyData_Case.CaseNum = tblEngagement_ClarifyData_Subcase.CaseNumber;

=====================================

Can anyone help?

 

[Golom]

The query that you are sending to the "Concatenate" function is not complete. It references [blue]tblEngagement_ClarifyData_Case[/blue] but, at the time that it runs inside the function, that table is undefined because it does not appear in the FROM clause of the query being sent to "Concatenate".

Try something like

SELECT C.CaseNum, C.CaseTitle, 
Concatenate(
"SELECT tblEngagement_ClarifyData_Case.CaseNum, tblEngagement_ClarifyData_Case.CaseTitle, [ownerLN] 
FROM tblEngagement_ClarifyData_Case INNER JOIN tblEngagement_ClarifyData_Subcase 
ON tblEngagement_ClarifyData_Case.CaseNum = tblEngagement_ClarifyData_Subcase.CaseNumber
WHERE tblEngagement_ClarifyData_Case.CaseNum = '" & S.[CaseNumber] & "'"
) AS SubcaseOwners

FROM tblEngagement_ClarifyData_Case C INNER JOIN tblEngagement_ClarifyData_Subcase S 
  ON C.CaseNum = S.CaseNumber;

 

[darall]

Thank you,Thank you, Thank you!

That was it, so simple, but I just couldn't figure it out! It's been driving me nuts for days.

 

[darall]

OK - one more question:

Which part of the SQL statement determines what is displayed in the concatenation?

Right now my result is displaying the case number rather than the subcase number. I need it to display the list of subcase owners from the tblEngagement_ClarifyData_Subcase. There are actually two fields that would need to pull together; OwnerFN and Owner LN, something like:

[tblEngagement_ClarifyData_Subcase].[OwnerFN]&" "&[tblEngagement_ClarifyData_Subcase].[owernLN]

I just can't figure out where to put it.

Here's what I have in the field cell of the query:

SubcaseOwners: Concatenate("SELECT tblEngagement_ClarifyData_Case.CaseNum, tblEngagement_ClarifyData_Case.CaseTitle, [ownerLN] 
FROM tblEngagement_ClarifyData_Case INNER JOIN tblEngagement_ClarifyData_Subcase 
ON tblEngagement_ClarifyData_Case.CaseNum = tblEngagement_ClarifyData_Subcase.CaseNumber
WHERE tblEngagement_ClarifyData_Case.CaseNum = '" & [tblEngagement_ClarifyData_Subcase].[CaseNumber] & "'")

On a side note is there a good book for teaching yourself SQL for Access?

Thanks for the help,
Denae

 

[Golom]

The three fields following SELECT in the SQL being passed to "Concatenate" will be returned by "Concatenate". Specifically
[tt]
tblEngagement_ClarifyData_Case.CaseNum,

tblEngagement_ClarifyData_Case.CaseTitle,

[ownerLN]
[/tt]
Given that the JOINS being used in the main query and those being used in the query sent to "Concatenate" are identical, you probably don't need the concatenate call. Just

SELECT C.CaseNum, C.CaseTitle, 
([tblEngagement_ClarifyData_Subcase].[OwnerFN]& " "&[tblEngagement_ClarifyData_Subcase].[OwnerLN]) AS SubcaseOwners

FROM tblEngagement_ClarifyData_Case C INNER JOIN tblEngagement_ClarifyData_Subcase S 
  ON C.CaseNum = S.CaseNumber;

should work.

On the other hand, if you want to seel ALL values of those two fields for a given value of [CaseNum] then

SELECT C.CaseNum, C.CaseTitle, 

Concatenate
(
"SELECT [OwnerFN], [OwnerLN] 

FROM tblEngagement_ClarifyData_Subcase 

WHERE tblEngagement_ClarifyData_Subcase.CaseNum = '" & C.[CaseNumber] & "'"
) AS SubcaseOwners

FROM tblEngagement_ClarifyData_Case C INNER JOIN tblEngagement_ClarifyData_Subcase S 
  ON C.CaseNum = S.CaseNumber;

 

[Golom]

Don't know about books but here are three articles on MS/JET SQL that you might look at

Fundamental
Intermediate
Advanced

 

[darall]

Thanks again for the help!