Need multiple values on one row

[cp2599]

I need a report that displays up to five serial numbers on one row for
each customer. I have a one to many relationship set up between the
two tables (customer and serial number), but I'm not sure how to write
the query to get all serial numbers on the same row as the customer.

 

[GeoffG]

I don't know if this is the best way of achieving the
result, but it works:

1. Create a report, grouped by Customer.
2. You need a Customer Group Header and Footer.
3. Set the Group Header height to zero. (You don't need to
print the Group Header; you only want to use its Format
event.)
4. Put a bound Textbox in the Detail Section for Serial
Number.
5. Set the Visible property of the Detail Section to No (to
make the Detail Section invisible). (You don't want to
print the Detail Section; you only need its Format event to
get the serial number for each customer.)
6. Put unbound Textboxes in the Group Footer for Customer
and Serial Numbers.
7. In the code module behind the report, create variables
in the General Declarations Section (at the top) for
Customer and SerialNos.
8. Use the Format event of the Group Header to capture the
Customer's name and store it in the Customer variable.
9. Use the Format event of the Detail Section to capture
the serial numbers, one at a time; concatenate each of them
to the SerialNos string variable.
10. Use the Format event of the Group Footer to copy the
variables to the unbound Textboxes in the Group Footer and
then set the variables to zero-length strings, ready to
receive fresh data for the next customer.


Geoff

 

[GeoffG]

I don't know if this is the best way of achieving the
result, but it works:

1. Create a report, grouped by Customer.
2. You need a Customer Group Header and Footer.
3. Set the Group Header height to zero. (You don't need to
print the Group Header; you only want to use its Format
event.)
4. Put a bound Textbox in the Detail Section for Serial
Number.
5. Set the Visible property of the Detail Section to No (to
make the Detail Section invisible). (You don't want to
print the Detail Section; you only need its Format event to
get the serial number for each customer.)
6. Put unbound Textboxes in the Group Footer for Customer
and Serial Numbers.
7. In the code module behind the report, create variables
in the General Declarations Section (at the top) for
Customer and SerialNos.
8. Use the Format event of the Group Header to capture the
Customer's name and store it in the Customer variable.
9. Use the Format event of the Detail Section to capture
the serial numbers, one at a time; concatenate each of them
to the SerialNos string variable.
10. Use the Format event of the Group Footer to copy the
variables to the unbound Textboxes in the Group Footer and
then set the variables to zero-length strings, ready to
receive fresh data for the next customer.


Geoff

 

[GeoffG]

Second thoughts:

1. The previous solution assumes that you're using a query
as the RecordSource for the report that uses both tables
(containing customer and serial-number data) and that the
query returns multiple records for each customer (i.e. one
record for each serial number).

2. It's occurred to me that you don't need to capture the
customer in the Format Event of the customer GroupHeader.
In fact, you may not need a customer GroupHeader; but you do
need a customer GroupFooter. You could place a bound
TextBox for the customer in the customer GroupFooter. This
will work because, as you're grouping on customer, the
GroupFooter has access to the last row of data for each
customer, which is why a bound TextBox for the customer
could be used instead.


Why use a Group Header?

I suggested using the GroupHeader Format Event because you
can initialize variables in the GroupHeader.

For example if you want the serial numbers to look like this
on the report:

(Serial1, Serial2, Serial3, Serial4, Serial5)

then you'd follow these steps:

1. In the GroupHeader Format event procedure, initialize
the variable holding the serial numbers, like this:

mstrSerialNos = "("

2. Concatenate one serial number and a comma in the Detail
Section's Format event procedure, like this:

mstrSerialNos = mstrSerialNos _
& Me.txtSerialNo & ", "

where Me.txtSerialNo is the TextBox in the Detail Section
bound to the SerialNo field.

3. Remove the last comma and space and add the last bracket
at the beginning of the GroupFooter Format event, like this:

mstrSerialNos = Left(mstrSerialNos, _
Len(mstrSerialNos) - 2) & ")"

4. Place the data in the unbound TextBox in the
GroupFooter, like this:

Me.txtSerials = mstrSerialNos

5. Re-initialize variables back to zero-length strings at
the end of the GroupFooter, like this:

mstrSerialNos = ""

This isn't strictly necessary if you're initializing the
variables in the GroupHeader.


Geoff.

 

[GeoffG]

Second thoughts:

1. The previous solution assumes that you're using a query
as the RecordSource for the report that uses both tables
(containing customer and serial-number data) and that the
query returns multiple records for each customer (i.e. one
record for each serial number).

2. It's occurred to me that you don't need to capture the
customer in the Format Event of the customer GroupHeader.
In fact, you may not need a customer GroupHeader; but you do
need a customer GroupFooter. You could place a bound
TextBox for the customer in the customer GroupFooter. This
will work because, as you're grouping on customer, the
GroupFooter has access to the last row of data for each
customer, which is why a bound TextBox for the customer
could be used instead.


Why use a Group Header?

I suggested using the GroupHeader Format Event because you
can initialize variables in the GroupHeader.

For example if you want the serial numbers to look like this
on the report:

(Serial1, Serial2, Serial3, Serial4, Serial5)

then you'd follow these steps:

1. In the GroupHeader Format event procedure, initialize
the variable holding the serial numbers, like this:

mstrSerialNos = "("

2. Concatenate one serial number and a comma in the Detail
Section's Format event procedure, like this:

mstrSerialNos = mstrSerialNos _
& Me.txtSerialNo & ", "

where Me.txtSerialNo is the TextBox in the Detail Section
bound to the SerialNo field.

3. Remove the last comma and space and add the last bracket
at the beginning of the GroupFooter Format event, like this:

mstrSerialNos = Left(mstrSerialNos, _
Len(mstrSerialNos) - 2) & ")"

4. Place the data in the unbound TextBox in the
GroupFooter, like this:

Me.txtSerials = mstrSerialNos

5. Re-initialize variables back to zero-length strings at
the end of the GroupFooter, like this:

mstrSerialNos = ""

This isn't strictly necessary if you're initializing the
variables in the GroupHeader.


Geoff.