Advanced JOIN query

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

Mike

I have two tables [tblProjects] and [tblPO]. I want to
select all the projects and all of the associated
purchase order numbers. Instead of returning a row for
each occurance, i want to display all of the po numbers
in a single field for each projects. Example:

I don't waat
ProjID = 1 | PO_Num = 1
ProjID = 1 | PO_Num = 2
ProjID = 1 | PO_Num = 3

I want
ProjID = 1 | PO_Num = 1, 2, 3

Thanks in advanced.
 
You will have to use VBA code to build the desired "recordset" -- a query
won't do this directly for you.
 
Dear Mike:

You can do this with just query work, first creating a table to
receive the results.

Clear the table and then begin appending all the ProjIDs, leaving the
PO_Num column blank. Then update it, using a conditional that tests
whether it is empty or not. If not, first append ", ". Then append
the PO_Num.

This is, however, lousy practice. What will you do if there are 200
PO_Nums for a single ProjID? The column can easily grow too wide to
hold this. What you want is a spreadsheet, not a database.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
That's right... I forgot about that, Duane!

--

Ken Snell
<MS ACCESS MVP>

Duane Hookom said:
There is a generic concatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP
--

Mike said:
I have two tables [tblProjects] and [tblPO]. I want to
select all the projects and all of the associated
purchase order numbers. Instead of returning a row for
each occurance, i want to display all of the po numbers
in a single field for each projects. Example:

I don't waat
ProjID = 1 | PO_Num = 1
ProjID = 1 | PO_Num = 2
ProjID = 1 | PO_Num = 3

I want
ProjID = 1 | PO_Num = 1, 2, 3

Thanks in advanced.
Click to expand...
Click to expand...
 
Back
Top