Stock Code Relation to Many Purchase Orders

[walt750]

I have been trying and looking for a few days to a solution into a
query

I am looking up one stock code from a table containing multiple
outstanding purchase orders for that one item.

The problem comes in that I'd like to display all those PO numbers in
one cell.

Example:

StockCode Purchase Orders
0123456789 45-1234, 46-4567, 47-4546
Can anyone help ... or lead me in a direction?

[Jason Lepack]

So right now you're returning:

StockCode, [Purchase Orders]
0123456789, 45-1234
0123456789, 46-4567
0123456789, 47-4546

So you need:
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Generic%20Function%2 0To%20Concatenate%20Child%20Records'

Cheers,
Jason Lepack

On May 2, 4:22 pm, [e-mail] wrote:

I have been trying and looking for a few days to a solution into a
query

I am looking up one stock code from a table containing multiple
outstanding purchase orders for that one item.

The problem comes in that I'd like to display all those PO numbers in
one cell.

Example:

StockCode Purchase Orders
0123456789 45-1234, 46-4567, 47-4546

Can anyone help ... or lead me in a direction?

[Rich P]

If your system is normalized (and it sounds like it might be - has a
one-to-many query) then the actual data would look like this for each
stock order in the Detail Data table

StockCode Purchase Orders
0123456789 45-1234
0123456789 46-4567
0123456789 47-4546

In order to get it into this form

StockCode Purchase Orders
0123456789 45-1234, 46-4567, 47-4546

you need to use a Crosstab query - which would actually make it look
like this:

StockCode Order1 Order2 Order3
0123456789 45-1234 46-4567 47-4546

You can read about crosstab queries in Access help.


Rich

*** Sent via Developersdex http://www.developersdex.com ***

[walt750]

On May 2, 6:56 pm, Rich P <[e-mail]:

If your system is normalized (and it sounds like it might be - has a
one-to-many query) then the actual data would look like this for each
stock order in the Detail Data table

StockCode Purchase Orders
0123456789 45-1234
0123456789 46-4567
0123456789 47-4546

In order to get it into this form

StockCode Purchase Orders
0123456789 45-1234, 46-4567, 47-4546

you need to use a Crosstab query - which would actually make it look
like this:

StockCode Order1 Order2 Order3
0123456789 45-1234 46-4567 47-4546

You can read about crosstab queries in Access help.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Well yes ... and normally that would probably be ok .... except for
the fact that there are 100's of PO's ... and that will exceed the
column limit, I'm sure.
Perhaps I can rewrite my other query's to request only one record at a
time instead of running a crosstab for all of them at the same time.

[walt750]

On May 2, 3:22 pm, [e-mail] wrote:

I have been trying and looking for a few days to a solution into a
query

I am looking up one stock code from a table containing multiple
outstanding purchase orders for that one item.

The problem comes in that I'd like to display all those PO numbers in
one cell.

Example:

StockCode Purchase Orders
0123456789 45-1234, 46-4567, 47-4546

Can anyone help ... or lead me in a direction?

That worked fantastic!!
Thanks

[Jason Lepack]

Glad to hear it! But what worked?

Cheers,
Jason Lepack

On May 3, 11:35 am, [e-mail] wrote:

On May 2, 3:22 pm, [e-mail] wrote:

I have been trying and looking for a few days to a solution into a
query

I am looking up one stock code from a table containing multiple
outstanding purchase orders for that one item.

The problem comes in that I'd like to display all those PO numbers in
one cell.

Example:

StockCode Purchase Orders
0123456789 45-1234, 46-4567, 47-4546

Can anyone help ... or lead me in a direction?

That worked fantastic!!
Thanks