Concatenate confusion using tow tables

[dbar10]

I had good success with all my other subforms using this function (after some great help from you guys) but now I have hit another block. This is my query:

SELECT [POCAllergies table].POCID, Concatenate("SELECT Allergies FROM Allergies Listing WHERE [POCAllergies table].AllergiesID = " & [Allergies Listing].AllergiesID) AS Allergies, [POCAllergies table].AllergiesID, [Allergies Listing].Alergies
FROM [POCAllergies table] INNER JOIN [Allergies Listing] ON [POCAllergies table].AllergiesID = [Allergies Listing].AllergiesID
GROUP BY [POCAllergies table].POCID, [POCAllergies table].AllergiesID, [Allergies Listing].Alergies
HAVING ((([POCAllergies table].POCID)=[Forms]![POC Form]![POCID]));

I am getting an error when I try to run that I don't understand:

"You tried to execute a query that does not include the secified expression "Concatenate("SELECT Allergies FROM Allergies Listing WHERE [POCAllergies table].AllergiesID = " & [Allergies Listing].AllergiesID)as part of an aggregate function."

Can you please help?

 

[dhookom]

You missed []s around a table/query name amd have the wrong table inside the quotes.

Concatenate("SELECT Allergies FROM [Allergies Listing] WHERE [Allergies Listing].AllergiesID = " & AllergiesID) AS Allergies,

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

thanks dhookum. I made the corrections but this is one of the places I ran into trouble. I go thte error:

"The specified field 'AllergiesID' could refer to more than one table listed in the FROM clause of your SQL statement"

I don't see what it's talking about. Below is the corrected query:

SELECT [POCAllergies table].POCID, Concatenate("SELECT Allergies FROM [Allergies Listing] WHERE [Allergies Listing].AllergiesID = " & AllergiesID) AS Allergies, [POCAllergies table].AllergiesID, [Allergies Listing].Alergies
FROM [POCAllergies table] INNER JOIN [Allergies Listing] ON [POCAllergies table].AllergiesID = [Allergies Listing].AllergiesID
GROUP BY [POCAllergies table].POCID, [POCAllergies table].AllergiesID, [Allergies Listing].Alergies
HAVING ((([POCAllergies table].POCID)=[Forms]![POC Form]![POCID]));

Can you explain? Thanks

 

[dhookom]

SELECT [POCAllergies table].POCID, Concatenate("SELECT Allergies FROM [Allergies Listing] WHERE [Allergies Listing].AllergiesID = " & [Allergies Listing].AllergiesID) AS Allergies, [POCAllergies table].AllergiesID, [Allergies Listing].Alergies
FROM [POCAllergies table] INNER JOIN [Allergies Listing] ON [POCAllergies table].AllergiesID = [Allergies Listing].AllergiesID
GROUP BY [POCAllergies table].POCID, [POCAllergies table].AllergiesID, [Allergies Listing].Alergies
HAVING ((([POCAllergies table].POCID)=[Forms]![POC Form]![POCID]));

However. I don't know why you have the [Allergies Listing] table in the outer query. Does this work for you?

SELECT [POCAllergies table].POCID, Concatenate("SELECT Allergies FROM [Allergies Listing] WHERE [Allergies Listing].AllergiesID = " & AllergiesID) AS Allergies, [POCAllergies table].AllergiesID
FROM [POCAllergies table] 
WHERE POCID=[Forms]![POC Form]![POCID];

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

Thanks for your help dhookum. I am still having trouble.
I loaded your first code above and get the following error:
"You tried to execute a query that does not include the specified expression 'Concatenate("SELECT Allergies FROM [Allergies Lisiting] WHERE [Allerfies Lisitng].AllergiesID = " &[Allergies Lisitng],AllergiesID)' as part of an aggregate function."

When I loaded the second example, I received this error:
"Run-time error '3061':
Too few parameters. Expected 1."

 

[dhookom]

I expect your linking field in the function might be text rather than numeric. Try:

SELECT [POCAllergies table].POCID, Concatenate("SELECT Allergies FROM [Allergies Listing] WHERE [Allergies Listing].AllergiesID = """ & AllergiesID & """") AS Allergies, [POCAllergies table].AllergiesID
FROM [POCAllergies table] 
WHERE POCID=[Forms]![POC Form]![POCID];

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

With that code I get the sme error as the second example above. Error 3061. I checked the tables and the key fields are numeric. Any other ideas? Thanks

 

[dhookom]

Is the key field actually POCID?

SELECT [POCAllergies table].POCID, Concatenate("SELECT Allergies FROM [Allergies Listing] WHERE [Allergies Listing].POCID = " & POCID ) AS Allergies, [POCAllergies table].AllergiesID
FROM [POCAllergies table] 
WHERE POCID=[Forms]![POC Form]![POCID];

If this doesn't work, come back with your significant table and fiedls names and data types.

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

Got the 3061 Error Expected 2.

Allergies Listing
AllergiesID autonumber primary key
Allergies Text

POCAllergies table
POCAllergiesID Autonumber Primary key
AllergiesID Number
POCID Number

I probably should have sent this to you before. Sorry.
Thanks for your patience.

 

[dhookom]

This should work

SELECT [POCAllergies table].POCID, Concatenate("SELECT Allergies FROM [Allergies Listing] WHERE [Allergies Listing].AllergiesID = " & AllergiesID) AS Allergies, [POCAllergies table].AllergiesID
FROM [POCAllergies table] 
WHERE POCID=[Forms]![POC Form]![POCID];

I think you can then change the query to a totals query and group by all of the fields.

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

Well, I tried it and again got the Error 3061 too few parameters Expected 1.

also, the debug window came up witht the highlighted line in the module:

Set rs = db.OpenRecordset(pstrSQL)

This is a real head scratcher, isn't it?

 

[dbar10]

Doesn't the Allergies Listing have to be in the query to list the Allergies? I tried adding that to your last code and the it comes up to say "The specified field 'AllergiesID' could refer to more than one table listed in the FROM clause of your SQL statement."

With just adding that table, the code looks like this:
SELECT [POCAllergies table].POCID, Concatenate("SELECT Allergies FROM [Allergies Listing] WHERE [Allergies Listing].AllergiesID = " & AllergiesID) AS Allergies, [POCAllergies table].AllergiesID
FROM [POCAllergies table] INNER JOIN [Allergies Listing] ON [POCAllergies table].AllergiesID = [Allergies Listing].AllergiesID
WHERE ((([POCAllergies table].POCID)=[Forms]![POC Form]![POCID]));

 

[dhookom]

If you get an error from my suggestion on 27 Apr 09 0:14 then you have probably spelled a field name incorrectly or AllergiesID is actually a text field. Check the table design of Allergies Listing for the field [Allergies]. Is this the real name or is it Allergy?

You don't need the Allergies Listing table in the main SQL since you only want to pull the Allergies values inside the Concatenate().

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

Thanks to your deligent help, I saw the light and changed the structure of my tables. I included the field [Allergies] on the [POCAllergies table] and changed the form and macros and it now works with the following code. Thank you very much for your help. I really appreciate it.

SELECT [POCAllergies table].POCID, Concatenate("SELECT Allergies FROM [POCAllergies table] WHERE POCID= " & [POCAllergies table].POCID) AS Allergies
FROM [POCAllergies table]
GROUP BY [POCAllergies table].POCID;

 

[dhookom]

I don't think placing the Allergies field in the POCAllergies table was the correct solution. Am I correct in assuming POCID is the primary key of a table that you haven't mentioned yet? It looks to me like [POCAllergies table] is a junction table between [Allergies Listing] and another table.

Taking another look at this, I would create a query (qselPOCIDAllergies):

SELECT [POCAllergies table].POCID, Allergies, [POCAllergies table].AllergiesID
FROM [POCAllergies table] INNER JOIN [Allergies Listing] ON [POCAllergies table].AllergiesID = [Allergies Listing].AllergiesID;

SELECT POCID, Concatenate("SELECT Allergies FROM [qselPOCIDAllergies] WHERE POCID = " & POCID) AS Allergies
FROM [qselPOCIDAllergies] 
WHERE POCID=[Forms]![POC Form]![POCID]
GROUP BY POCID;

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

Thanks DHookum. I had to go back and change everything I did this AM. But your solution works good. If you don't mind, could you explain why it is better to do this than what I did this morning? If not I understand. I really appreciate your help.

 

[dhookom]

The value you store in the field Allergies is dependent on AllergiesID. It should not be stored in more than one place in your application.

Duane
Hook'D on Access
MS Access MVP