Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #10447

Re: Repeating data in Query result

Show all headers | View raw

On 02/24/2015 09:51 AM, csavedgeebay@gmail.com wrote:
> I've created a query to show open PO's.  The issue is there can be multiple open PO's for one item.  In the Query result I would just like to see the item and item identifying information once with the multiple open PO numbers.  However, when I add in the open PO numbers my item information begins to repeat.  Is there anything I can do about this?
>
> Example of what I would like to see:
>
> Item:      Desc:   Open PO NUM:
> 567589    Oxcarb    345
>                      568
>                      554
>                      445
>                      678
>
> Next item.
>

   This could be done with a little VBA:

SELECT Item, Desc, Concat("SELECT po_num FROM item_id, po_table WHERE 
po_id = item_id ORDER BY po_num ASC", "po_num", "|") AS [Open PO Num]
FROM item_table
<WHERE item_id = ???>



Where Concat() is a function (in a module) that runs the given query (as 
above) and joins the results into a single string that is returned to 
the original query:


Function Concat(sub_select as string, field as string, delimeter as string)
   dim db as Database
   dim recset as RecordSet
   dim recs as integer
   dim tmp as string

   set db = CurrentDB

   recset = db.Execute(sub_select)

   if (recset.Count = 0)
     tmp = "None"
   else
     for recs = 1 to recset.Count
       tmp = tmp & recset.Fields(field).Value & delimeter
     next recs
     ' remove trailing *delimeter*
     tmp = Left(tmp, Len(tmp)-1)
   end if

   set recset = nothing
   set db = nothing

   Concat = tmp
End Function


Example output:

  Item:      Desc:   Open PO NUM:
  567589    Oxcarb    345|568|554|445|678
  .
  .
  .


   This code could be made more robust for sure. Google 'access concat 
function' or similar for more/better examples as I've just jotted this 
out off the top of my heda this morning.


-- 
Norman
Registered Linux user #461062
AMD64X2 6400+ Ubuntu 10.04 64bit

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Find similar | Unroll thread

Thread

Repeating data in Query result csavedgeebay@gmail.com - 2015-02-24 06:51 -0800
  Re: Repeating data in Query result Patrick Finucane <patrickfinucanetx@gmail.com> - 2015-02-24 08:59 -0800
    Re: Repeating data in Query result csavedgeebay@gmail.com - 2015-02-24 09:08 -0800
      Re: Repeating data in Query result Patrick Finucane <patrickfinucanetx@gmail.com> - 2015-02-24 11:34 -0800
  Re: Repeating data in Query result "Ron Paii" <None@none.com> - 2015-02-24 11:28 -0600
  Re: Repeating data in Query result Norman Peelman <npeelman@cfl.rr.com> - 2015-03-01 08:11 -0500

csiph-web