Did you try open the Northwind sample and use the code? The statement I
provided was the entire SQL view of a query, not just a field/column
expression.
If you have multiple tables rather than a single table, you should combine
them first in a union query. There should be no reason to have multiple
tables.
--
Duane Hookom
Microsoft Access MVP
:
Hi, thank you for your access 2007 help. However I am still having
difficulties. I typed in the expression as you wrote it, in the Field section
of the query:
SELECT Categories.*, Concatenate("SELECT Product FROM GrpByCompMolyb300
WHERE Product =" & [Product]) AS AllProducts FROM Products;
But it is saying "The syntax of the subquery in this expression is
incorrect. Check the subquery's syntax and enclose the subquery in
parenthess."
Also I'm not sure if you understand that each category has it's own table,
which is actually a query based on another table. So one category table looks
like this:
Product
A
B
C
D
and another category has its own table:
Product
E
F
G
H
So I am trying to make another table/query that would take one category
table, or multiple if it can, and it will look like this:
Product Product
A, B, C, D E, F, G, H
So then all the products from one category would be listed in a single cell
separated by commas, because this is ultimately what I want it to look like
in the final report. I am sorry it's so convoluted...I hope this makes sense.
:
Assuming you want all of the products for each category, this is how the
query would look in the NorthWind sample MDB:
SELECT Categories.*, Concatenate("SELECT ProductName FROM Products WHERE
CategoryID =" & [categoryID]) AS AllProducts
FROM Categories;
--
Duane Hookom
Microsoft Access MVP
:
Thanks for your help with my Access question. I am trying to concatenate
records into one single cell. I tried the expression you suggested, and the
query still has an error message that says Syntax Error or something. I put
in the expression exactly as you wrote it, with the correct name of my table:
SELECT Distinct
Concatenate("SELECT Product FROM MyTable WHERE Product is Not Null")
FROM [MyTable]
So this is what I am trying to do:
I have a query that lists about 10 products, that's all--just one single
field. The query gets these products from another table where I put in
certain criteria so it only gives me certain products. I'll name them as
follows:
Product
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
So now what I am ultimately doing is making a report, and in the report I
want to list this group of products in one cell, and not a vertical list, so
that it doesn't take up as much space. So I have a query with the expression
above, MyTable being the name of the query. I thought that I should do an
intermediate step, by creating another query based on that query that would
concatenate all of those into one cell so that it looks like this:
Product Category A: 1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009,
1010
And I will have a few of these for different product categories., so that it
would look like this:
Product Category A: 1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009,
1010
Product Category B: XXXX, XXXX, XXXX, XXXX, XXXX, .........
Product Category C: XXXX, XXXX, XXXX, XXXX, XXXX, XXXX,.....
It seems pretty simple--all I'm doing is taking a list of products from one
table and putting them in one box. Do you have any suggestions?
Thanks a lot for your assistance on this,
ryan
:
No, it means that you don't pass any criteria into the function to limit the
records returned or you might pass criteria of [Product] is not null.
So you would have a query that looked like
SELECT Distinct
Concatenate("SELECT Product FROM SomeTable WHERE Product is Not Null")
FROM [SomeTable]
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
HardWorkDude wrote:
But does that mean I need to create a one-to-many relationship and primary
keys? I don't have any of that
:
HardWorkDude wrote:
I am wondering how can I create a query or report that concatenates all
records under one field from a particular table so that they all show up in
the query or report, in the same cell, separated by commas? For example,
let's say I had one simple query that was based on another query or table,
that only had one field with a list of unique records:
Product:
A
B
C
D
E
How can I extract all these products, for a report if I can, so that it
lists all of these products like this: Product: A, B, C, D, E ?
Create a function to do that. You can download a good one
from:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16
