Concatenation or not?

  • Thread starter Thread starter Mike Green
  • Start date Start date
M

Mike Green

Hi all
Here's the problem:-
I have a form with a product name and all the associated details, using
ProductID as the unique identifier. A second related table "Joiner" which
holds four fields JoinID, ProductID, ItemID, Qty. This is used to show the
quantity of each item that makes the main product. The third related table
holds details of the Product items uniquely identified with ItemID.
I have a nice form which shows all the details of the product with how many
of each item (Sub form) each product contains and a load of calculations
done within it, all fine.
I have a text box in the product table that I would like to enter a text
list of the items that it contains. I know this will have to be coded but
the problem is how do you get the results of a query which produces a single
column of the items to concatenate into a single row? I would prefer to
separate each item from the other with a comma so I would see "M10 Screw, M8
Screw, M8 Washer, 12v Motor, 12v Battery"
Please let me know if I have not made the problem clear.
Any pointers in the right direction will be appreciated.
Mike Green

Please reply to group
 
Mike Green said:
Hi all
Here's the problem:-
I have a form with a product name and all the associated details,
using ProductID as the unique identifier. A second related table
"Joiner" which holds four fields JoinID, ProductID, ItemID, Qty. This
is used to show the quantity of each item that makes the main
product. The third related table holds details of the Product items
uniquely identified with ItemID.
I have a nice form which shows all the details of the product with
how many of each item (Sub form) each product contains and a load of
calculations done within it, all fine.
I have a text box in the product table that I would like to enter a
text list of the items that it contains. I know this will have to be
coded but the problem is how do you get the results of a query which
produces a single column of the items to concatenate into a single
row? I would prefer to separate each item from the other with a comma
so I would see "M10 Screw, M8 Screw, M8 Washer, 12v Motor, 12v
Battery"
Please let me know if I have not made the problem clear.
Any pointers in the right direction will be appreciated.
Mike Green
Click to expand...

I would not store this redundant information in the table unless
extracting it on demand presents performance problems. Otherwise, you
run the risk of having discrepancies between the stored string and the
actual list stored in table Joiner. You can use the fConcatChild
function posted here ...

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

.... to return a concatenated list of the items that make up a given
product.
 
Back
Top