concatenating multiple child records into a single value 2

[betachristopher]

I have read faq701-4233

I created the new module, but am having more problems. Is it because I'm trying to concatenate a field that is already concatenating two fields from two tables?

original code:

SELECT Panel.abbreviation, Panel.name, CPT.code & " (x" & Panel_CPT_Map.cptCount & ")" AS cpt_code
FROM (CPT INNER JOIN Panel_CPT_Map ON CPT.cPTKey = Panel_CPT_Map.cptKey) INNER JOIN Panel ON Panel_CPT_Map.panelKey = Panel.panelKey
GROUP BY Panel.abbreviation, Panel.name, CPT.code & " (x" & Panel_CPT_Map.cptCount & ")"
ORDER BY Panel.abbreviation;

example of results:
abbrv name cpt_code
CFDNA CF DNA 83891 (x1)
CFDNA CF DNA 83892 (x1)
CFDNA CF DNA 83896 (x40)
CFDNA CF DNA 83900 (x1)
CFDNA CF DNA 83901 (x17)
CFDNA CF DNA 83903 (x1)
CFDNA CF DNA 83908 (x1)
CFDNA CF DNA 83912 (x1)

desired results:
abbrv name cpt_code
CFDNA CF DNA 83891 (x1), 83892 (x1), 83896 (x40), 83900 (x1), 83901 (x17), 83903 (x1), 83908 (x1), 83912 (x1)

modified code:

SELECT Panel.abbreviation, Panel.name, Concatenate("SELECT code & " (x" & cptCount & ")" FROM CPT, Panel_CPT_Map, Panel WHERE name = " & [name] & " ") AS cpt_code
FROM (CPT INNER JOIN Panel_CPT_Map ON CPT.cPTKey = Panel_CPT_Map.cptKey) INNER JOIN Panel ON Panel_CPT_Map.panelKey = Panel.panelKey
GROUP BY Panel.abbreviation, Panel.name, CPT.code & " (x" & Panel_CPT_Map.cptCount & ")"
ORDER BY Panel.abbreviation;

when I run the new query, I get an error message stating Syntax error (missing operator) in query expression 'Concatenate("SELECT code & " (x" & cptCount & ")" FROM CPT, Panel_CPT_Map, Panel WHERE name = " & [name] & " ")' and then it highlights the following section of my code: " & cptCount & "

Can anyone help me correct my syntax?

 

[PHV]

Concatenate("SELECT code & ' (x' & cptCount & ')' FROM (CPT INNER JOIN Panel_CPT_Map ON CPT.cPTKey = Panel_CPT_Map.cptKey) INNER JOIN Panel ON Panel_CPT_Map.panelKey = Panel.panelKey WHERE name = '" & [name] & "'") AS cpt_code

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

 

[betachristopher]

Thanks PHV. That almost does it. The problem now is that some of the records include a single quote in the name field. For these records, I get a runtime error. I tried to get rid of the single quote using Replace(name,"'","") and Replace(name,''',''), but neither worked.

Any ideas for this problem?

 

[PHV]

Concatenate("SELECT code & ' (x' & cptCount & ')' FROM (CPT INNER JOIN Panel_CPT_Map ON CPT.cPTKey = Panel_CPT_Map.cptKey) INNER JOIN Panel ON Panel_CPT_Map.panelKey = Panel.panelKey WHERE name = [!]""[/!]" & [name] & "[!]""[/!]") AS cpt_code

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

 

[betachristopher]

that did it! thanks for the help!

 

[betachristopher]

ok, I've started adding more data to my query, and now I've hit another wall.

SELECT Panel.abbreviation, Panel.name, Panel.orchardName AS test_num, Concatenate("SELECT code & ' (x' & cptCount & ')' FROM (CPT INNER JOIN Panel_CPT_Map ON CPT.cPTKey = Panel_CPT_Map.cptKey) INNER JOIN Panel ON Panel_CPT_Map.panelKey = Panel.panelKey WHERE Panel.name = """ & [name] & """") AS cpt_code, SpecimenCollection.containerCount, ContainerType.name AS container_type, SpecimenCollection.specimenInstructions, SpecimenType.name AS specimen_type, StorageType.name AS storage_condition
FROM (((SpecimenCollection INNER JOIN ContainerType ON SpecimenCollection.containerTypeKey = ContainerType.containerTypeKey) INNER JOIN StorageType ON SpecimenCollection.storageTypeKey = StorageType.storageTypeKey) INNER JOIN SpecimenType ON SpecimenCollection.specimenTypeKey = SpecimenType.specimenTypeKey) INNER JOIN ((CPT INNER JOIN Panel_CPT_Map ON CPT.cPTKey = Panel_CPT_Map.cptKey) INNER JOIN Panel ON Panel_CPT_Map.panelKey = Panel.panelKey) ON SpecimenCollection.panelKey = Panel.panelKey
WHERE (((Panel.isActive)=True) AND ((Panel.isOrderable)=True))
GROUP BY Panel.abbreviation, Panel.name, Panel.orchardName, Concatenate("SELECT code & ' (x' & cptCount & ')' FROM (CPT INNER JOIN Panel_CPT_Map ON CPT.cPTKey = Panel_CPT_Map.cptKey) INNER JOIN Panel ON Panel_CPT_Map.panelKey = Panel.panelKey WHERE Panel.name = """ & [name] & """"), SpecimenCollection.containerCount, ContainerType.name, SpecimenCollection.specimenInstructions, SpecimenType.name, StorageType.name
ORDER BY Panel.name;

I now get an error message stating "The specified field '[name]' could refer to more than one table listed in the FROM clause of your SQL statement."

There are a few tables that have a field called name, but all were given a different column name except for one. But to avoid confusion, I changed the column name for Panel.name to [panel] in my query below.

SELECT Panel.abbreviation, Panel.name AS panel, Panel.orchardName AS test_num, Concatenate("SELECT code & ' (x' & cptCount & ')' FROM (CPT INNER JOIN Panel_CPT_Map ON CPT.cPTKey = Panel_CPT_Map.cptKey) INNER JOIN Panel ON Panel_CPT_Map.panelKey = Panel.panelKey WHERE Panel.name = """ & [panel] & """") AS cpt_code, SpecimenCollection.containerCount, ContainerType.name AS container_type, SpecimenCollection.specimenInstructions, SpecimenType.name AS specimen_type, StorageType.name AS storage_condition
FROM (((SpecimenCollection INNER JOIN ContainerType ON SpecimenCollection.containerTypeKey = ContainerType.containerTypeKey) INNER JOIN StorageType ON SpecimenCollection.storageTypeKey = StorageType.storageTypeKey) INNER JOIN SpecimenType ON SpecimenCollection.specimenTypeKey = SpecimenType.specimenTypeKey) INNER JOIN ((CPT INNER JOIN Panel_CPT_Map ON CPT.cPTKey = Panel_CPT_Map.cptKey) INNER JOIN Panel ON Panel_CPT_Map.panelKey = Panel.panelKey) ON SpecimenCollection.panelKey = Panel.panelKey
WHERE (((Panel.isActive)=True) AND ((Panel.isOrderable)=True))
GROUP BY Panel.abbreviation, Panel.name, Panel.orchardName, Concatenate("SELECT code & ' (x' & cptCount & ')' FROM (CPT INNER JOIN Panel_CPT_Map ON CPT.cPTKey = Panel_CPT_Map.cptKey) INNER JOIN Panel ON Panel_CPT_Map.panelKey = Panel.panelKey WHERE Panel.name = """ & [panel] & """"), SpecimenCollection.containerCount, ContainerType.name, SpecimenCollection.specimenInstructions, SpecimenType.name, StorageType.name
ORDER BY Panel.name;

But now I get a parameter prompt for [panel]. What am I doing wrong?

 

[dhookom]

Name is a poor name for anything in Access since name is a property of every object. You would need to fully qualify with a table.field type syntax:

SELECT Panel.abbreviation, Panel.name AS panelname, Panel.orchardName AS test_num, Concatenate("SELECT code & ' (x' & cptCount & ')' FROM (CPT INNER JOIN Panel_CPT_Map ON CPT.cPTKey = Panel_CPT_Map.cptKey) INNER JOIN Panel ON Panel_CPT_Map.panelKey = Panel.panelKey WHERE Panel.name = """ & [panel] & """") AS cpt_code, SpecimenCollection.containerCount, ContainerType.name AS container_type, SpecimenCollection.specimenInstructions, SpecimenType.name AS specimen_type, StorageType.name AS storage_condition
FROM (((SpecimenCollection INNER JOIN ContainerType ON SpecimenCollection.containerTypeKey = ContainerType.containerTypeKey) INNER JOIN StorageType ON SpecimenCollection.storageTypeKey = StorageType.storageTypeKey) INNER JOIN SpecimenType ON SpecimenCollection.specimenTypeKey = SpecimenType.specimenTypeKey) INNER JOIN ((CPT INNER JOIN Panel_CPT_Map ON CPT.cPTKey = Panel_CPT_Map.cptKey) INNER JOIN Panel ON Panel_CPT_Map.panelKey = Panel.panelKey) ON SpecimenCollection.panelKey = Panel.panelKey
WHERE (((Panel.isActive)=True) AND ((Panel.isOrderable)=True))
GROUP BY Panel.abbreviation, Panel.name, Panel.orchardName, Concatenate("SELECT code & ' (x' & cptCount & ')' FROM (CPT INNER JOIN Panel_CPT_Map ON CPT.cPTKey = Panel_CPT_Map.cptKey) INNER JOIN Panel ON Panel_CPT_Map.panelKey = Panel.panelKey WHERE Panel.name = """ & [panel].[Name] & """"), SpecimenCollection.containerCount, ContainerType.name, SpecimenCollection.specimenInstructions, SpecimenType.name, StorageType.name
ORDER BY Panel.name;

Do yourself a favor and don't use reserved words as names in your applications.

Duane
Hook'D on Access
MS Access MVP

 

[betachristopher]

Do yourself a favor and don't use reserved words as names in your applications.

I agree. Unfortunately, it's not our program, so we can't make any changes to the tables.

Thank you for your help. I was able to get it working by using the table.field syntax like you said.