Query help - creating a row from multiple rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a customer table that has a row for each of the last 15 items the
customer has ordered. Now I need to build a table that has one row per
customer with each of the 15 items on that one row. So I need to take 15 rows
of customer_no, Item_no and create a table of customer_no, item_no1,
item_no2, item_no3, etc.

Any ideas?

Thanks

Brenda
 
Dear Brenda:

It would be best not to store the 15 columns of items in a table, but
just to generate it using a query when you need it.

This may simply require a crosstab query. Doing this would require
that the rows of your table already contain a "rank" of your top 15,
counting them from 1 to 15. What do you have in your existing table
that allows you to tell this order? Perhaps it is a date/time column
or an OrderNumber, assuming you assign OrderNumbers in ascending
sequence. Also, you would need to have a way of breaking a tie in
case the date/time or OrderNumber of two rows is the same. Perhaps it
would be sufficient to do this on the Item_no. However, if items 15
and 16 were on the same order with the same date/time, then using the
Item_No to choose one over the other to be included in your "top 15"
would be somewhat arbitrary. If you set a limit, like showing "the
last 15 items" then some arbitrary cut-off is likely mandatory.

If you will create a query that shows the information needed to
perform this, I will try to add the ranking. From that you can
readily create the crosstab.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I need the table to have the fifteen columns. Using it as input to a program
on
another plateform. The item order does not matter. It seems strange, but
they just want any of the fifteen items ordered by a client. If the client
has more than 15 items order, then it will just arbitrary cut one off.

I need a way to take fifteen table1.cust_no, table1.item_no and turn it into
table2.cust_no, table2.item1_no, table2.item2_no, etc.

Brenda
 
Dear Brenda:

I would call it a "principle" of database engines that you cannot get
them to do something arbitrarily. If you want 15 items you must do so
in an orderly and well defined manner. While you could implement a
column containing a randomly generated number, if it doesn't matter
which 15 then you could simply supply the first 15 in item_no order.

I suggest the following steps. First, if there is any possibility of
having a duplication in the cust_no / item_no combination, perform a
SELECT DISTINCT query on this pair to give only unique combinations
and base the rest of the query work on this query.

Next, eliminate any item_no values not in the TOP 15 for each cust_no
sorted by item_no, using a correlated subquery. Add a ranking column
to count these 1 to 15, then TRANSFORM this using a crosstab so the
numbers 1 to 15 become column headings.

There are a number of possible challenges in doing this. I'd like you
to proceed till you find one you cannot conquer, then bring me up to
date on your progress and the challenge you have and I'll respond at
that point.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top