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

Groups > comp.databases.ms-access > #10406 > unrolled thread

Repeating data in Query result

Back to article view | Back to comp.databases.ms-access

Contents

  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

#10406 — Repeating data in Query result

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.

[toc] | [next] | [standalone]

#10408

On Tuesday, February 24, 2015 at 9:51:19 AM UTC-5, csaved...@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

I do not see how you can do that in a query. 

[toc] | [prev] | [next] | [standalone]

#10409

Do you think I could do this within a table?  Do you have any suggestions?


On Tuesday, February 24, 2015 at 11:59:38 AM UTC-5, Patrick Finucane wrote:
> On Tuesday, February 24, 2015 at 9:51:19 AM UTC-5, csaved...@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
> 
> I do not see how you can do that in a query.

[toc] | [prev] | [next] | [standalone]

#10413

On Tuesday, February 24, 2015 at 12:08:58 PM UTC-5, csaved...@gmail.com wrote:
> Do you think I could do this within a table?  Do you have any suggestions?
> 
> 
> On Tuesday, February 24, 2015 at 11:59:38 AM UTC-5, Patrick Finucane wrote:
> > On Tuesday, February 24, 2015 at 9:51:19 AM UTC-5, csaved...@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
> > 
> > I do not see how you can do that in a query.

Not within a table.  With a form.  I'd probably use a listbox to present the values.  A report can hide duplicate values but I doubt that's what you want.

[toc] | [prev] | [next] | [standalone]

#10410

<csavedgeebay@gmail.com> wrote in message 
news:1d846e68-473e-4578-90c5-40debd53e6cd@googlegroups.com...
> 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.

Use SELECT DISTINCT to remove the duplicates. Use a form or report to format 
and display the data. 

[toc] | [prev] | [next] | [standalone]

#10447

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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-access

csiph-web