concatenate while grouping

[Ian Elliott]

Thanks for any help.
I am trying to concatenate a field from record(s) into a field, while
grouping them. For example, the query (with no concatenation, but with
grouping) is like:
SELECT Table!name, Table!date
FROM Table
GROUP BY Table!name, Table!date

I would like to make a query that along with grouping by name and date,
makes another field, that is a concatenation of all fields called Policy.

I am trying to do this with Duane Hookum's concatenation function, but can't
figure it out, I think because his function doesn't need a grouping to do it.
But I have to use a grouping I think.

Anyway, the table is like:
Name,Date,Policy
ACUL,2007,123
ACUL,2007,345
ACUL,2006,888
BEUL,2006,999

I would like the query to make:
ACUL,2007,123 345
ACUL,2006,888
BEUL,2006,999

Is this possible?
Thanks for any help.

 

[Marshall Barton]

I am trying to concatenate a field from record(s) into a field, while
grouping them. For example, the query (with no concatenation, but with
grouping) is like:
SELECT Table!name, Table!date
FROM Table
GROUP BY Table!name, Table!date

I would like to make a query that along with grouping by name and date,
makes another field, that is a concatenation of all fields called Policy.

I am trying to do this with Duane Hookum's concatenation function, but can't
figure it out, I think because his function doesn't need a grouping to do it.
But I have to use a grouping I think.

Anyway, the table is like:
Name,Date,Policy
ACUL,2007,123
ACUL,2007,345
ACUL,2006,888
BEUL,2006,999

I would like the query to make:
ACUL,2007,123 345
ACUL,2006,888
BEUL,2006,999

SELECT Table!name, Table!date,
Concatenate("SELECT Policy FROM Table
WHERE Name=""" & Name & """ And Date=" &
Format(Date, "\#yyyy-m-d\#"), " ")
FROM Table
GROUP BY Table!name, Table!date

 

[John W. Vinson]

Anyway, the table is like:
Name,Date,Policy
ACUL,2007,123
ACUL,2007,345
ACUL,2006,888
BEUL,2006,999

I would like the query to make:
ACUL,2007,123 345
ACUL,2006,888
BEUL,2006,999

Is this possible?

Yes:

www.mvps.org/access/modules/mdl0004.htm

John W. Vinson [MVP]

 

[Ian Elliott]

Thanks Marshall that did it!!!

SELECT Table!name, Table!date,
Concatenate("SELECT Policy FROM Table
WHERE Name=""" & Name & """ And Date=" &
Format(Date, "\#yyyy-m-d\#"), " ")
FROM Table
GROUP BY Table!name, Table!date

 

[Ian Elliott]

Thanks John, I used the advice from the message before yours, but thanks!

 

[KaCee]

I am trying to use Duane Hookam's Concatenate Function. The data I am
concatenating comes from a query which looks like:

TrainType,NumberCars,TrainNumber
S, 2, 41
S, 3, 42
S, 2, 1
U, 2, 45
etc.

I am attempting to use the function in a query which is:
SELECT TrainType, NumberCars, Concatenate("SELECT TrainNumber FROM
Query1
WHERE TrainType=""" & TrainType & """ And NumberCars=" & NumberCars,
"; ")
FROM Query1
GROUP BY TrainType, NumberCars;

When I run the query, I get a run-time error which says "No value
given for one or more required parameters".

What am I missing?

 

[Marshall Barton]

I am trying to use Duane Hookam's Concatenate Function. The data I am
concatenating comes from a query which looks like:

TrainType,NumberCars,TrainNumber
S, 2, 41
S, 3, 42
S, 2, 1
U, 2, 45
etc.

I am attempting to use the function in a query which is:
SELECT TrainType, NumberCars, Concatenate("SELECT TrainNumber FROM
Query1
WHERE TrainType=""" & TrainType & """ And NumberCars=" & NumberCars,
"; ")
FROM Query1
GROUP BY TrainType, NumberCars;

That looks good to me.

Are you sure that the error is not coming from Query1?

There may be a different problem if any of the Query1
records has a Null in either of the two fields.

 

[John Spencer]

SELECT TrainType
, NumberCars
, Concatenate("SELECT TrainNumber FROM Query1 WHERE TrainType=""" &
TrainType & """ And NumberCars=" & NumberCars, "; ")
FROM Query1
GROUP BY TrainType, NumberCars;


Is NumberCars a text field or a number field? If it is a text field
that contains number characters then you would need

SELECT TrainType
, NumberCars
, Concatenate("SELECT TrainNumber FROM Query1 WHERE TrainType=""" &
TrainType & """ And NumberCars=""" & NumberCars & """", "; ")
FROM Query1
GROUP BY TrainType, NumberCars;

Other than that I don't see a problem unless you have a misspelling on a
field name or the name of the query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[KaCee]

I can run Query1 without any error. Query1 does asks for input for a
specific date though. That wouldn't cause a problem would it?

 

[Duane Hookom]

Try hard coding some dates in Query1. IMHO, parameter queries are never a
good idea. Criteria should come from values entered into controls on forms.

 

[KaCee]

Thank you all for your help! I appreciate it!! The query is working
now.