How to put 2 or more fields inside 1 field for not repeating the line?

[Gstreichan]

Hi,

I work on construction site and have two different tables. One which shows the material identification code and the quantity required on the jobsite. Another table with the shipment numbers. Many times the material comes in more than one shipment, which has different numbers. The first table has only one line to each identification code and the total qty required. When I link to the second table it repeats the line because there is more than 1 shipment. I would like to know if it is possible to have all the shipments in the same field, so it will avoid repeating the lines;

Example:

1st table

Ident Code DESCRIPTION REQUIRED QTY
C5NZPU 16" PIPE 500
C5NZP6 12" PIPE 275

2nd table

Ident Code Shipment_# Ship Qty
C5NZPU NZ1566 100
C5NZPU NZ1899 200
C5NZPU NZ2001 200
C5NZP6 NZ1852 175
C5NZP6 NC1945 100

The result is:

Ident Code DESCRIPTION REQ. QTY Shipment_# Ship Qty
C5NZPU 16" PIPE 500 NZ1566 100
C5NZPU 16" PIPE 500 NZ1899 200
C5NZPU 16" PIPE 500 NZ2001 200
C5NZP6 12" PIPE 500 NZ1852 175
C5NZP6 12" PIPE 500 NZ1945 100

I want it to be:

C5NZPU 16" PIPE 500 NZ1566/NZ1899/NZ2001 500
C5NZP6 12' PIPE 275 NZ1852/NZ1945 275

Does anyone know how I can get it?

I appreciate very much.

Guilherme

[Delerna]

SQL provides numerous aggregate functions.

Sum(),Avg(),Min (),Max() etc

unfortunately there is no Concatenate() in access as far as I am aware
That would make this task all too easy ;)

There are many ways you can fudge it and my way is to do it in my front end, which these days, for me, are web pages. I don't think that will suit you though and my memory is a bit fuzzy how to do it in access

It is relatively easy if the number of records is fixed but it gets complicated where you need to concatenate a variable number of fields which you appear to need.

Someone has recently posted a function to do this but I think it may have been a function for SQL Server and not access.

If I find the other post I will post a link
or if I find a sample in an old database I will post that.

If someone else beats me with an answer for you then ...good.