By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

Stock Code Relation to Many Purchase Orders

P: n/a
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?

May 2 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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, walt...@gmail.com 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?

May 2 '07 #2

P: n/a
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 ***
May 3 '07 #3

P: n/a
On May 2, 6:56 pm, Rich P <rpng...@aol.comwrote:
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.

May 3 '07 #4

P: n/a
On May 2, 3:22 pm, walt...@gmail.com 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

May 3 '07 #5

P: n/a
Glad to hear it! But what worked?

Cheers,
Jason Lepack

On May 3, 11:35 am, walt...@gmail.com wrote:
On May 2, 3:22 pm, walt...@gmail.com 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

May 3 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.