T
twopandas
I am a novice so I would appreciate very detailed advice. I am trying group
content when reporting. For example, "ID Number" has multiple related "Trace
ID Number"(s). When I query, the result is as follows:
ID Number / Trace ID Number
1 / ABC
1 / EFG
1 / HIJ
2 / ABC
2 / XYZ
Instead, I would like the results to appear as follows (such that all trace
id numbers appear in the same 'cell' and on the same row as the ID number:
ID Number / Trace ID Number Number
1 / ABC EFG HIJ
2 / ABC XYZ
I have tried to follow the logic of another thread where the following
example was given, as follows.
--------
Answer
Try:
TRANSFORM First(Concatenate("Select CompanyAbbreviation FROM MatrixTestQuery
WHERE [City]=""" & [City] & """ AND ProductLineName=""" & [ProductLineName]
&
""" ORDER BY CompanyAbbreviation", Chr(13) & Chr(10))) AS Company
SELECT MatrixTestQuery.City
FROM MatrixTestQuery
GROUP BY MatrixTestQuery.City
PIVOT MatrixTestQuery.ProductLineName;
--
Duane Hookom
MS Access MVP
--------
Unfortunately, I am not knowledgeable in MS Access (or databases, for that
matter) to know how to translate the above example into my own scenario. I
have tried to build an expression in the Query Table (Design View), but I
keep getting operator errors.
Can anyone help?
Thanks!
content when reporting. For example, "ID Number" has multiple related "Trace
ID Number"(s). When I query, the result is as follows:
ID Number / Trace ID Number
1 / ABC
1 / EFG
1 / HIJ
2 / ABC
2 / XYZ
Instead, I would like the results to appear as follows (such that all trace
id numbers appear in the same 'cell' and on the same row as the ID number:
ID Number / Trace ID Number Number
1 / ABC EFG HIJ
2 / ABC XYZ
I have tried to follow the logic of another thread where the following
example was given, as follows.
--------
Answer
Try:
TRANSFORM First(Concatenate("Select CompanyAbbreviation FROM MatrixTestQuery
WHERE [City]=""" & [City] & """ AND ProductLineName=""" & [ProductLineName]
&
""" ORDER BY CompanyAbbreviation", Chr(13) & Chr(10))) AS Company
SELECT MatrixTestQuery.City
FROM MatrixTestQuery
GROUP BY MatrixTestQuery.City
PIVOT MatrixTestQuery.ProductLineName;
--
Duane Hookom
MS Access MVP
--------
Unfortunately, I am not knowledgeable in MS Access (or databases, for that
matter) to know how to translate the above example into my own scenario. I
have tried to build an expression in the Query Table (Design View), but I
keep getting operator errors.
Can anyone help?
Thanks!