Concantinate a Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a Cable_Num that is associated with multiple Raceway_Num and want to
be able to output on a report all the Raceway_Num Associated with Cable_Num.

Cable_Num Type Series .... Raceway_Num

Right now it looks like

P-151X MV-105 C-C-100 C-C-105P19393 C-C-104

I want it to look like
P-151X MV-105 C-C-100. C-C-105P19393, C-C-104

All the outputs on the report are in boxes. I am Populating Raceway_Num on
a sub report and putting that in the main Report.

The way I get the output is.
With this query
SELECT tblCable.Cable_Num, tblCable_Raceway.Cable_Num,
tblCable_Raceway.Raceway_Num, tblCable_Raceway.Order
FROM tblCable LEFT JOIN tblCable_Raceway ON tblCable.Cable_Num =
tblCable_Raceway.Cable_Num
ORDER BY tblCable_Raceway.Cable_Num, tblCable_Raceway.Order;

Then i just put Raceway_Num on the sub report and format the columns.
I think Concatinate is the answer I am just having trouble trying to apply
Duane's
function to my situation. Any suggestions would be appreciated.
 
If all the values you are attempting to concatenate are in the same row then
you don't need the Concatenate() function. You only need to use an
expression like:
[Type] & ". " & [Series] & ". " & [RaceWay_Num]
 
Ok the one I am having problems with is just the Raceway_Num
I need to get
RaceWay_Num
that currently prints in colums and rows
to a list seperated by commas.
[Type] & ". " & [Series] & ". " & [RaceWay_Num]
the first two entries are just that 1 entrie the Raceway_Num is a list of
entries related to that one row.

Duane Hookom said:
If all the values you are attempting to concatenate are in the same row then
you don't need the Concatenate() function. You only need to use an
expression like:
[Type] & ". " & [Series] & ". " & [RaceWay_Num]


--
Duane Hookom
MS Access MVP

StarJunor said:
I have a Cable_Num that is associated with multiple Raceway_Num and want to
be able to output on a report all the Raceway_Num Associated with
Cable_Num.

Cable_Num Type Series .... Raceway_Num

Right now it looks like

P-151X MV-105 C-C-100 C-C-105P19393 C-C-104

I want it to look like
P-151X MV-105 C-C-100. C-C-105P19393, C-C-104

All the outputs on the report are in boxes. I am Populating Raceway_Num
on
a sub report and putting that in the main Report.

The way I get the output is.
With this query
SELECT tblCable.Cable_Num, tblCable_Raceway.Cable_Num,
tblCable_Raceway.Raceway_Num, tblCable_Raceway.Order
FROM tblCable LEFT JOIN tblCable_Raceway ON tblCable.Cable_Num =
tblCable_Raceway.Cable_Num
ORDER BY tblCable_Raceway.Cable_Num, tblCable_Raceway.Order;

Then i just put Raceway_Num on the sub report and format the columns.
I think Concatinate is the answer I am just having trouble trying to apply
Duane's
function to my situation. Any suggestions would be appreciated.
Click to expand...
Click to expand...
 
It would then help if you would provide the table structures and data types.
Is Raceway_Num the only field to be displayed from tblCable_Raceway or
should other fields be include?

--
Duane Hookom
MS Access MVP

StarJunor said:
Ok the one I am having problems with is just the Raceway_Num
I need to get
RaceWay_Num
that currently prints in colums and rows
to a list seperated by commas.
[Type] & ". " & [Series] & ". " & [RaceWay_Num]
the first two entries are just that 1 entrie the Raceway_Num is a list of
entries related to that one row.

Duane Hookom said:
If all the values you are attempting to concatenate are in the same row
then
you don't need the Concatenate() function. You only need to use an
expression like:
[Type] & ". " & [Series] & ". " & [RaceWay_Num]


--
Duane Hookom
MS Access MVP

StarJunor said:
I have a Cable_Num that is associated with multiple Raceway_Num and want
to
be able to output on a report all the Raceway_Num Associated with
Cable_Num.

Cable_Num Type Series .... Raceway_Num

Right now it looks like

P-151X MV-105 C-C-100 C-C-105P19393 C-C-104

I want it to look like
P-151X MV-105 C-C-100. C-C-105P19393, C-C-104

All the outputs on the report are in boxes. I am Populating
Raceway_Num
on
a sub report and putting that in the main Report.

The way I get the output is.
With this query
SELECT tblCable.Cable_Num, tblCable_Raceway.Cable_Num,
tblCable_Raceway.Raceway_Num, tblCable_Raceway.Order
FROM tblCable LEFT JOIN tblCable_Raceway ON tblCable.Cable_Num =
tblCable_Raceway.Cable_Num
ORDER BY tblCable_Raceway.Cable_Num, tblCable_Raceway.Order;

Then i just put Raceway_Num on the sub report and format the columns.
I think Concatinate is the answer I am just having trouble trying to
apply
Duane's
function to my situation. Any suggestions would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Maybe I am making this to complicated. I would like to display the results
of a query "qryCable Schedule Sub-Report" that returns "Raceway_Num". This
query can return anywhere from 1 to 25 (Raceway_Num) text entries.
I would like them to return with a comma and a space between each entry but
not after the last entry.

example

"Raceway_Num", "Raceway_Num", "Raceway_Num", "Raceway_Num"

12jkdjii, 54xs85, numb343, last123numberoflist

I have tried this, but it doesn't put the entries next to each other(instead
it uses the columns and rows), and leaves a comma at the end of the list.
[Raceway_Num] & ". "
This is in a sub report that links up with the other entries in a seperate
report.

So yes Raceway_Num is the only field being returned form tblCable_Raceway.

Duane Hookom said:
It would then help if you would provide the table structures and data types.
Is Raceway_Num the only field to be displayed from tblCable_Raceway or
should other fields be include?

--
Duane Hookom
MS Access MVP

StarJunor said:
Ok the one I am having problems with is just the Raceway_Num
I need to get
RaceWay_Num
that currently prints in colums and rows
to a list seperated by commas.
[Type] & ". " & [Series] & ". " & [RaceWay_Num]
the first two entries are just that 1 entrie the Raceway_Num is a list of
entries related to that one row.

Duane Hookom said:
If all the values you are attempting to concatenate are in the same row
then
you don't need the Concatenate() function. You only need to use an
expression like:
[Type] & ". " & [Series] & ". " & [RaceWay_Num]


--
Duane Hookom
MS Access MVP

I have a Cable_Num that is associated with multiple Raceway_Num and want
to
be able to output on a report all the Raceway_Num Associated with
Cable_Num.

Cable_Num Type Series .... Raceway_Num

Right now it looks like

P-151X MV-105 C-C-100 C-C-105P19393 C-C-104

I want it to look like
P-151X MV-105 C-C-100. C-C-105P19393, C-C-104

All the outputs on the report are in boxes. I am Populating
Raceway_Num
on
a sub report and putting that in the main Report.

The way I get the output is.
With this query
SELECT tblCable.Cable_Num, tblCable_Raceway.Cable_Num,
tblCable_Raceway.Raceway_Num, tblCable_Raceway.Order
FROM tblCable LEFT JOIN tblCable_Raceway ON tblCable.Cable_Num =
tblCable_Raceway.Cable_Num
ORDER BY tblCable_Raceway.Cable_Num, tblCable_Raceway.Order;

Then i just put Raceway_Num on the sub report and format the columns.
I think Concatinate is the answer I am just having trouble trying to
apply
Duane's
function to my situation. Any suggestions would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
If I understand correctly,
=Concatenate("SELECT Raceway_Num FROM [qryCable Schedule Sub-Report]")
This expression will concatenate every Raceway_Num value from the query with
commas between.

--
Duane Hookom
MS Access MVP

StarJunor said:
Maybe I am making this to complicated. I would like to display the
results
of a query "qryCable Schedule Sub-Report" that returns "Raceway_Num".
This
query can return anywhere from 1 to 25 (Raceway_Num) text entries.
I would like them to return with a comma and a space between each entry
but
not after the last entry.

example

"Raceway_Num", "Raceway_Num", "Raceway_Num", "Raceway_Num"

12jkdjii, 54xs85, numb343, last123numberoflist

I have tried this, but it doesn't put the entries next to each
other(instead
it uses the columns and rows), and leaves a comma at the end of the list.
[Raceway_Num] & ". "
This is in a sub report that links up with the other entries in a seperate
report.

So yes Raceway_Num is the only field being returned form tblCable_Raceway.

Duane Hookom said:
It would then help if you would provide the table structures and data
types.
Is Raceway_Num the only field to be displayed from tblCable_Raceway or
should other fields be include?

--
Duane Hookom
MS Access MVP

StarJunor said:
Ok the one I am having problems with is just the Raceway_Num
I need to get
RaceWay_Num
that currently prints in colums and rows
to a list seperated by commas.
[Type] & ". " & [Series] & ". " & [RaceWay_Num]
the first two entries are just that 1 entrie the Raceway_Num is a list
of
entries related to that one row.

:

If all the values you are attempting to concatenate are in the same
row
then
you don't need the Concatenate() function. You only need to use an
expression like:
[Type] & ". " & [Series] & ". " & [RaceWay_Num]


--
Duane Hookom
MS Access MVP

I have a Cable_Num that is associated with multiple Raceway_Num and
want
to
be able to output on a report all the Raceway_Num Associated with
Cable_Num.

Cable_Num Type Series .... Raceway_Num

Right now it looks like

P-151X MV-105 C-C-100 C-C-105P19393 C-C-104

I want it to look like
P-151X MV-105 C-C-100. C-C-105P19393, C-C-104

All the outputs on the report are in boxes. I am Populating
Raceway_Num
on
a sub report and putting that in the main Report.

The way I get the output is.
With this query
SELECT tblCable.Cable_Num, tblCable_Raceway.Cable_Num,
tblCable_Raceway.Raceway_Num, tblCable_Raceway.Order
FROM tblCable LEFT JOIN tblCable_Raceway ON tblCable.Cable_Num =
tblCable_Raceway.Cable_Num
ORDER BY tblCable_Raceway.Cable_Num, tblCable_Raceway.Order;

Then i just put Raceway_Num on the sub report and format the
columns.
I think Concatinate is the answer I am just having trouble trying to
apply
Duane's
function to my situation. Any suggestions would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top