PLEASE HELP - query results to multiple rows.

[Stephen]

Hi Folks,

I'm trying this again cause I'm not getting any responses from my previous
posts...

I'm running a query for assigned garments against a pervasive sql db that
returns results based on customer id and order id parameters. the problem is
that each record in my db can have up to 10 garments associated with it. each
record in the db has fields for garments 1 - 10. most instances have an
average of three to five garments with populated values. what I would like
to have is a row returned to my sheet for each associated garment.

rather than having a single long row that lists the wearer, garment
descript, assigned and charge values, I would like to have one row per
garment.

what I have is...

wearer garment 1 assigned 1 charged 1 garment 2 assigned 2
charged 2
j smith top 5 3 pant
4 2


what I want is...

wearer garment assigned charged
j smith top 5 3
j smith pant 4 2


I'm at a real loss here and running under a deadline that's getting tighter
by the minute. If anyone know of a different board that I might do better
posting in please let me know.

thanks to all who reply.

 

[Don Guillett]

try this idea
Sub rearrangedata()
For i = 2 To Cells(Rows.Count, "a").End(xlUp).row
If Len(Trim(Cells(i, "b"))) > 0 Then
dlr = Cells(Rows.Count, "H").End(xlUp).row + 1
Cells(dlr, "h").Value = Cells(i, "a")
Cells(dlr, "i").Value = Cells(i, "b")
Cells(dlr, "j").Value = Cells(i, "c")
Cells(dlr, "k").Value = Cells(i, "d")
End If
If Len(Trim(Cells(i, "e"))) > 0 Then
dlr = Cells(Rows.Count, "H").End(xlUp).row + 1
Cells(dlr, "h").Value = Cells(i, "a")
Cells(dlr, "i").Value = Cells(i, "e")
Cells(dlr, "j").Value = Cells(i, "f")
Cells(dlr, "k").Value = Cells(i, "g")
End If
Next i
End Sub

 

[Stephen]

I'm trying a new approach cause I think i have too many tables to hit.

My main sheet has a dropdown that pulls the customer names from a sheet
called 'Orders'. 'Orders has three columns - A=order number, B=order code,
and C=order desc. (essentially the customer name). using the input range of
Orders!$C$2:$C$235 and a cell link of H3, I am able to populate cell I3 on my
main sheet with the order number using the index function.

Two more sheets named, 'Wearers' & 'Garments' have queries that auto refresh
taking their parameters from my main sheets' I3 cell each time that value
changes. This works well... Each time I select a different customer name
from my dropdown, my cell link value changes, my index function changes the
order number, and my wearers and garments sheets refresh with updated values
based on the new parameter (order number).

Now, my main sheet which is the only shset I want my users to work with (I
can hide all supporting sheets) needs to report back the following
information... column A is Order Description (from order sheet based on order
number) which right now I can accomplish with a VLookup (can a VLookup be
used in a macro?), First Name (from Wearers sheet), Last Name (from Wearers
sheet), Garment Description (from Garments sheet), Label (from Wearers
sheet), allocated (from wearers sheet), charged (from wearers sheet).

Now any given row returned in the Wearers sheet can have between 1 and 10
columns for allocated, charged, and garment type (numeric value that
corresponds to the Garments Sheet and is associated with a given Garment
Description), which makes for 30 potential cloumns which can contian values
for and single wearer.

If I can use VLookup in a macro I might be albe to loop through all wears
and return a row for each garment type, allocaed, and charged for any wearer,
which would give me the muptiple rows per wearer that is my final goal for my
main sheet (see my original post of what I am looking for v. what I was
getting).

Any ideas are greatly appreciated!!!