Concatenation

[JKarchner]

I am trying to combine records into one field with each one being separated
by a comma. I used a generic form for doing this but i cannot get it to
work. I get a syntax error, and when i close the dialog box, the Product_ID
is highlighted. My code is as following, can anyone help me?

SELECT Suppliers.Supplier, Reports.Report, Concatenate(“SELECT Product_ID
FROM SuppliersXREFProducts WHERE Supplier_ID =†& [Supplier_ID] & †AND
Report_ID = “ & [Report_ID] & ) AS ProductsSupplied
FROM Suppliers, Reports;

 

[Duane Hookom]

You have an extra "&" in your function. It is also critical to understand
the difference between text and numeric fields. The following assumes
Supplier_ID and Report_ID are both numeric fields in the table/query
SuppliersXREFProducts.

SELECT Suppliers.Supplier, Reports.Report, Concatenate("SELECT Product_ID
FROM SuppliersXREFProducts WHERE Supplier_ID =" & [Supplier_ID] & " AND
Report_ID = " & [Report_ID] ) AS ProductsSupplied
FROM Suppliers, Reports;

Do you realize you are creating a cartesian query since there are no joins
between Suppliers and Reports?

 

[JKarchner]

I understand that it implies they are numeric fields, both Supplier_ID and
Report_ID are numeric fields. I just want the data to appear such that a
record consists of a supplier_ID, a report_ID, and a concatenation of all the
product_IDs that are supplied by the supplier_ID for each report_ID. i have
a SupplierXREFProducts table that is a linking table, such that the 3 fields
are all foreign keys: supplier_ID goes to the Suppliers table, the report_ID
goes to the Reports table, and the Product_ID goes to the Prodcuts table. So
for this table each record consists of a supplier, a record, and one product.
Now for each supplier / record combo there are many products. And that is
what i am trying to get. The point for other tables is simply to gather the
naming for the reports and the suppliers.

I tried removing the extra quotation mark like you said and i am still
receiving the same error.

You have an extra "&" in your function. It is also critical to understand
the difference between text and numeric fields. The following assumes
Supplier_ID and Report_ID are both numeric fields in the table/query
SuppliersXREFProducts.

SELECT Suppliers.Supplier, Reports.Report, Concatenate("SELECT Product_ID
FROM SuppliersXREFProducts WHERE Supplier_ID =" & [Supplier_ID] & " AND
Report_ID = " & [Report_ID] ) AS ProductsSupplied
FROM Suppliers, Reports;

Do you realize you are creating a cartesian query since there are no joins
between Suppliers and Reports?

--
Duane Hookom
MS Access MVP

I am trying to combine records into one field with each one being separated
by a comma. I used a generic form for doing this but i cannot get it to
work. I get a syntax error, and when i close the dialog box, the
Product_ID
is highlighted. My code is as following, can anyone help me?

SELECT Suppliers.Supplier, Reports.Report, Concatenate("SELECT Product_ID
FROM SuppliersXREFProducts WHERE Supplier_ID =" & [Supplier_ID] & " AND
Report_ID = " & [Report_ID] & ) AS ProductsSupplied
FROM Suppliers, Reports;

 

[Duane Hookom]

I stated "You have an extra "&" in your function"
You stated "I tried removing the extra quotation mark"

Can you try the function in the immediate/debug window (press ctrl+G)
? Concatenate("SELECT Product_ID FROM SuppliersXREFProducts WHERE
Supplier_ID =xx AND Report_ID = nn" )
Where xx equals a legitimate Supplier_ID and nn equals a legitimate
Report_ID.


--
Duane Hookom
MS Access MVP

I understand that it implies they are numeric fields, both Supplier_ID and
Report_ID are numeric fields. I just want the data to appear such that a
record consists of a supplier_ID, a report_ID, and a concatenation of all
the
product_IDs that are supplied by the supplier_ID for each report_ID. i
have
a SupplierXREFProducts table that is a linking table, such that the 3
fields
are all foreign keys: supplier_ID goes to the Suppliers table, the
report_ID
goes to the Reports table, and the Product_ID goes to the Prodcuts table.
So
for this table each record consists of a supplier, a record, and one
product.
Now for each supplier / record combo there are many products. And that is
what i am trying to get. The point for other tables is simply to gather
the
naming for the reports and the suppliers.

I tried removing the extra quotation mark like you said and i am still
receiving the same error.

You have an extra "&" in your function. It is also critical to understand
the difference between text and numeric fields. The following assumes
Supplier_ID and Report_ID are both numeric fields in the table/query
SuppliersXREFProducts.

SELECT Suppliers.Supplier, Reports.Report, Concatenate("SELECT Product_ID
FROM SuppliersXREFProducts WHERE Supplier_ID =" & [Supplier_ID] & " AND
Report_ID = " & [Report_ID] ) AS ProductsSupplied
FROM Suppliers, Reports;

Do you realize you are creating a cartesian query since there are no
joins
between Suppliers and Reports?

--
Duane Hookom
MS Access MVP

I am trying to combine records into one field with each one being
separated
by a comma. I used a generic form for doing this but i cannot get it
to
work. I get a syntax error, and when i close the dialog box, the
Product_ID
is highlighted. My code is as following, can anyone help me?

SELECT Suppliers.Supplier, Reports.Report, Concatenate("SELECT
Product_ID
FROM SuppliersXREFProducts WHERE Supplier_ID =" & [Supplier_ID] & " AND
Report_ID = " & [Report_ID] & ) AS ProductsSupplied
FROM Suppliers, Reports;

 

[JKarchner]

I am unfamiliar with how to go about doing this. Can you please explain it
with more detail.

I stated "You have an extra "&" in your function"
You stated "I tried removing the extra quotation mark"

Can you try the function in the immediate/debug window (press ctrl+G)
? Concatenate("SELECT Product_ID FROM SuppliersXREFProducts WHERE
Supplier_ID =xx AND Report_ID = nn" )
Where xx equals a legitimate Supplier_ID and nn equals a legitimate
Report_ID.


--
Duane Hookom
MS Access MVP

I understand that it implies they are numeric fields, both Supplier_ID and
Report_ID are numeric fields. I just want the data to appear such that a
record consists of a supplier_ID, a report_ID, and a concatenation of all
the
product_IDs that are supplied by the supplier_ID for each report_ID. i
have
a SupplierXREFProducts table that is a linking table, such that the 3
fields
are all foreign keys: supplier_ID goes to the Suppliers table, the
report_ID
goes to the Reports table, and the Product_ID goes to the Prodcuts table.
So
for this table each record consists of a supplier, a record, and one
product.
Now for each supplier / record combo there are many products. And that is
what i am trying to get. The point for other tables is simply to gather
the
naming for the reports and the suppliers.

I tried removing the extra quotation mark like you said and i am still
receiving the same error.

You have an extra "&" in your function. It is also critical to understand
the difference between text and numeric fields. The following assumes
Supplier_ID and Report_ID are both numeric fields in the table/query
SuppliersXREFProducts.

SELECT Suppliers.Supplier, Reports.Report, Concatenate("SELECT Product_ID
FROM SuppliersXREFProducts WHERE Supplier_ID =" & [Supplier_ID] & " AND
Report_ID = " & [Report_ID] ) AS ProductsSupplied
FROM Suppliers, Reports;

Do you realize you are creating a cartesian query since there are no
joins
between Suppliers and Reports?

--
Duane Hookom
MS Access MVP


I am trying to combine records into one field with each one being
separated
by a comma. I used a generic form for doing this but i cannot get it
to
work. I get a syntax error, and when i close the dialog box, the
Product_ID
is highlighted. My code is as following, can anyone help me?

SELECT Suppliers.Supplier, Reports.Report, Concatenate("SELECT
Product_ID
FROM SuppliersXREFProducts WHERE Supplier_ID =" & [Supplier_ID] & " AND
Report_ID = " & [Report_ID] & ) AS ProductsSupplied
FROM Suppliers, Reports;

 

[Duane Hookom]

Did you press ctrl+G? Did you enter the expression
?...
Keep in mind the news group will wrap messages so

? Concatenate("SELECT Product_ID FROM SuppliersXREFProducts WHERE
Supplier_ID =xx AND Report_ID = nn" )

should all be on one line.

--
Duane Hookom
MS Access MVP

I am unfamiliar with how to go about doing this. Can you please explain it
with more detail.

I stated "You have an extra "&" in your function"
You stated "I tried removing the extra quotation mark"

Can you try the function in the immediate/debug window (press ctrl+G)
? Concatenate("SELECT Product_ID FROM SuppliersXREFProducts WHERE
Supplier_ID =xx AND Report_ID = nn" )
Where xx equals a legitimate Supplier_ID and nn equals a legitimate
Report_ID.


--
Duane Hookom
MS Access MVP

I understand that it implies they are numeric fields, both Supplier_ID
and
Report_ID are numeric fields. I just want the data to appear such that
a
record consists of a supplier_ID, a report_ID, and a concatenation of
all
the
product_IDs that are supplied by the supplier_ID for each report_ID. i
have
a SupplierXREFProducts table that is a linking table, such that the 3
fields
are all foreign keys: supplier_ID goes to the Suppliers table, the
report_ID
goes to the Reports table, and the Product_ID goes to the Prodcuts
table.
So
for this table each record consists of a supplier, a record, and one
product.
Now for each supplier / record combo there are many products. And that
is
what i am trying to get. The point for other tables is simply to
gather
the
naming for the reports and the suppliers.

I tried removing the extra quotation mark like you said and i am still
receiving the same error.

:

You have an extra "&" in your function. It is also critical to
understand
the difference between text and numeric fields. The following assumes
Supplier_ID and Report_ID are both numeric fields in the table/query
SuppliersXREFProducts.

SELECT Suppliers.Supplier, Reports.Report, Concatenate("SELECT
Product_ID
FROM SuppliersXREFProducts WHERE Supplier_ID =" & [Supplier_ID] & "
AND
Report_ID = " & [Report_ID] ) AS ProductsSupplied
FROM Suppliers, Reports;

Do you realize you are creating a cartesian query since there are no
joins
between Suppliers and Reports?

--
Duane Hookom
MS Access MVP


I am trying to combine records into one field with each one being
separated
by a comma. I used a generic form for doing this but i cannot get
it
to
work. I get a syntax error, and when i close the dialog box, the
Product_ID
is highlighted. My code is as following, can anyone help me?

SELECT Suppliers.Supplier, Reports.Report, Concatenate("SELECT
Product_ID
FROM SuppliersXREFProducts WHERE Supplier_ID =" & [Supplier_ID] & "
AND
Report_ID = " & [Report_ID] & ) AS ProductsSupplied
FROM Suppliers, Reports;

 

[JKarchner]

I pressed ctrl+G and the screen popped up. Where do i enter the code at?
The only place to type is the Immediate field. If this is where i am
supposed to enter it at, what is the next step?

Sorry for making this difficult on you.

Did you press ctrl+G? Did you enter the expression
?...
Keep in mind the news group will wrap messages so

? Concatenate("SELECT Product_ID FROM SuppliersXREFProducts WHERE
Supplier_ID =xx AND Report_ID = nn" )

should all be on one line.

--
Duane Hookom
MS Access MVP

I am unfamiliar with how to go about doing this. Can you please explain it
with more detail.

I stated "You have an extra "&" in your function"
You stated "I tried removing the extra quotation mark"

Can you try the function in the immediate/debug window (press ctrl+G)
? Concatenate("SELECT Product_ID FROM SuppliersXREFProducts WHERE
Supplier_ID =xx AND Report_ID = nn" )
Where xx equals a legitimate Supplier_ID and nn equals a legitimate
Report_ID.


--
Duane Hookom
MS Access MVP


I understand that it implies they are numeric fields, both Supplier_ID
and
Report_ID are numeric fields. I just want the data to appear such that
a
record consists of a supplier_ID, a report_ID, and a concatenation of
all
the
product_IDs that are supplied by the supplier_ID for each report_ID. i
have
a SupplierXREFProducts table that is a linking table, such that the 3
fields
are all foreign keys: supplier_ID goes to the Suppliers table, the
report_ID
goes to the Reports table, and the Product_ID goes to the Prodcuts
table.
So
for this table each record consists of a supplier, a record, and one
product.
Now for each supplier / record combo there are many products. And that
is
what i am trying to get. The point for other tables is simply to
gather
the
naming for the reports and the suppliers.

I tried removing the extra quotation mark like you said and i am still
receiving the same error.

:

You have an extra "&" in your function. It is also critical to
understand
the difference between text and numeric fields. The following assumes
Supplier_ID and Report_ID are both numeric fields in the table/query
SuppliersXREFProducts.

SELECT Suppliers.Supplier, Reports.Report, Concatenate("SELECT
Product_ID
FROM SuppliersXREFProducts WHERE Supplier_ID =" & [Supplier_ID] & "
AND
Report_ID = " & [Report_ID] ) AS ProductsSupplied
FROM Suppliers, Reports;

Do you realize you are creating a cartesian query since there are no
joins
between Suppliers and Reports?

--
Duane Hookom
MS Access MVP


I am trying to combine records into one field with each one being
separated
by a comma. I used a generic form for doing this but i cannot get
it
to
work. I get a syntax error, and when i close the dialog box, the
Product_ID
is highlighted. My code is as following, can anyone help me?

SELECT Suppliers.Supplier, Reports.Report, Concatenate("SELECT
Product_ID
FROM SuppliersXREFProducts WHERE Supplier_ID =" & [Supplier_ID] & "
AND
Report_ID = " & [Report_ID] & ) AS ProductsSupplied
FROM Suppliers, Reports;

 

[Douglas J. Steele]

Enter it in the Immediate field (ensuring you put the ? in front and type
all on one line), then hit Enter. The result of the function should appear
directly below that line.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I pressed ctrl+G and the screen popped up. Where do i enter the code at?
The only place to type is the Immediate field. If this is where i am
supposed to enter it at, what is the next step?

Sorry for making this difficult on you.

Did you press ctrl+G? Did you enter the expression
?...
Keep in mind the news group will wrap messages so

? Concatenate("SELECT Product_ID FROM SuppliersXREFProducts WHERE
Supplier_ID =xx AND Report_ID = nn" )

should all be on one line.

--
Duane Hookom
MS Access MVP

I am unfamiliar with how to go about doing this. Can you please explain
it
with more detail.

:

I stated "You have an extra "&" in your function"
You stated "I tried removing the extra quotation mark"

Can you try the function in the immediate/debug window (press ctrl+G)
? Concatenate("SELECT Product_ID FROM SuppliersXREFProducts WHERE
Supplier_ID =xx AND Report_ID = nn" )
Where xx equals a legitimate Supplier_ID and nn equals a legitimate
Report_ID.


--
Duane Hookom
MS Access MVP


I understand that it implies they are numeric fields, both
Supplier_ID
and
Report_ID are numeric fields. I just want the data to appear such
that
a
record consists of a supplier_ID, a report_ID, and a concatenation
of
all
the
product_IDs that are supplied by the supplier_ID for each report_ID.
i
have
a SupplierXREFProducts table that is a linking table, such that the
3
fields
are all foreign keys: supplier_ID goes to the Suppliers table, the
report_ID
goes to the Reports table, and the Product_ID goes to the Prodcuts
table.
So
for this table each record consists of a supplier, a record, and one
product.
Now for each supplier / record combo there are many products. And
that
is
what i am trying to get. The point for other tables is simply to
gather
the
naming for the reports and the suppliers.

I tried removing the extra quotation mark like you said and i am
still
receiving the same error.

:

You have an extra "&" in your function. It is also critical to
understand
the difference between text and numeric fields. The following
assumes
Supplier_ID and Report_ID are both numeric fields in the
table/query
SuppliersXREFProducts.

SELECT Suppliers.Supplier, Reports.Report, Concatenate("SELECT
Product_ID
FROM SuppliersXREFProducts WHERE Supplier_ID =" & [Supplier_ID] & "
AND
Report_ID = " & [Report_ID] ) AS ProductsSupplied
FROM Suppliers, Reports;

Do you realize you are creating a cartesian query since there are
no
joins
between Suppliers and Reports?

--
Duane Hookom
MS Access MVP


I am trying to combine records into one field with each one being
separated
by a comma. I used a generic form for doing this but i cannot
get
it
to
work. I get a syntax error, and when i close the dialog box, the
Product_ID
is highlighted. My code is as following, can anyone help me?

SELECT Suppliers.Supplier, Reports.Report, Concatenate("SELECT
Product_ID
FROM SuppliersXREFProducts WHERE Supplier_ID =" & [Supplier_ID] &
"
AND
Report_ID = " & [Report_ID] & ) AS ProductsSupplied
FROM Suppliers, Reports;