info from rows to one single row 1

[msii]

I need to take a customer with multiple products and put all informaton on one row to export into excel to send off to a fulfillment center.

An example from Northwind looks like this:

OrderDate CompanyName OrderID ProductID Quantity UnitPrice
7/4/1996 Vins et alcools Chevalier 10248 Queso Cabrales 12 $14.00
7/4/1996 Vins et alcools Chevalier 10248 Singaporean Mee 10 $9.80
7/5/1996 Toms Spezialitäten 10249 Tofu 9 $18.60
7/5/1996 Toms Spezialitäten 10249 Queso Cabrales 12 $14.00
7/8/1996 Hanari Carnes 10250 Jack's New Englr 10 $7.70
7/8/1996 Hanari Carnes 10250 Manjimup Dried s 35 $42.40



And I need it to look like this:

OrderDate CompanyName OrderID (ProductID Quantity Unit Price) (ProductID Quantity Unit Price) etc . . .
7/4/1996 Vins et alcools Chevalier 10248 Queso Cabrales 12 $14.00 Singaporean Mee 10 $9.80
7/5/1996 Toms Spezialitäten 10249 Tofu 9 $18.60 Queso Cabrales 12 $14.00
7/8/1996 Hanari Carnes 10250 Jack's New Englr 10 $7.70 Manjimup Dried s 35 $42.40


Any help would be appreciated.

thanks,

MSII
klarson@msiincdirect.com %-)(double dazed)

 

[LittleSmudge]

Can you REALLY guarantee that there will be only TWO items ordered per customer ?


So what is the MAXIMUM number of items that you would allow a customer to order at any one time?

Are the fulfilment center REALLY interested in getting a spreadsheet that is 3 * ( n + 1 ) columns wide { Where n is you max number of items ordered. }
By the way this Col = 3*(n+1) sets a real MAX to n because Col cannot be greater than 255 => n(max) = 84


G LS

 

[Guest_imported]

I had a problem like this before, the propriatory database they wanted used comma delimited import with the first 20 fields as order and address info followed by (item number, price, and quant.),(item number, price, and quant.),etc. If they ordered 1 item you got 23 fields, 2 items 26, etc.

I tried to figure out a good way to do it, fooled around for a few hours trying VB routines, then gave up and exported it to *cough* FileMaker and made a conversion program in under 2 minutes.

I had it pretty close but it just wasn't worth my time. I would be interested in seeing someone elses aproach to the solution. Sometimes the easiest things are very hard, and the hard ones are easy.

 

[msii]

The MAX number of orders per customer requested by fullfillment would be NINE. But in my case the max would be FIVE.

My hopes would be to get this query into a spread sheet so I could make additional changes if needed, then save as a comma delimited file and send off to fulfillment.

The total number of columns/fields the fulfillment center is asking for is 84 in a comma delimited file.

Thanks for you help,

 

[dmeyerrph]

I would be interested if anyone has a solution. I have a similar problem.
My database has:
Customer Name, Address, Product, QTY
For Example:

Frosty Snowman, NorthPole, Ice Cubes, 200
Frosty Snowman, Northpole, Ice Cream, 130
Santa Claus, South Pole, Ice cubes, 200

I want to make one table that would list all the Products that a customer has on one line

Customer Name, Address, Product1, QTY1, Product 2, QTY2, etc

These customers may have 1 to 6 products.

Thanks for your help.

 

[LittleSmudge]

Okay chaps - it seems like there is a real demand for this sort of thing - so creativity hat on time and here we go.

What follows is in ADO - ( so if you're using A97 or earlier then convert to DAO )

Lets assume that the Order information is currently held in a relational database structure of:-

tblOrder
OrderId
OrderDate
CompanyName
.. etc ..

tblLineItems
LineItemId
OrderRef Foreign Key to tblOrder
ProductId
Quantity
UnitPrice

So this is a One-To-Many schema
On tblOrder.OrderId = tblLineItems.OrderRef



Design a table as follows

tblFlatLandOrders
OrderId
OrderDate
CompanyName
ProductId1
Quantity1
UnitPrice1
ProductId2
Quantity2
UnitPrice2
ProductId3
Quantity3
UnitPrice3
..
etc
..
ProductId9
Quantity9
UnitPrice9


Then in code ( some commandbutton's OnClick event probobly )

' This code typed "on the fly" by G LittleSmudge 2nd October 2002 - look out for typing errors.
Dim intItemCount As Integer
Dim rstOrder As ADODB.RecordSet
Dim rstItem As ADODB.RecordSet
Dim rstOutput As ADODB.RecordSet

Set rstOrder As New ADODB.RecordSet
Set rstItem As New ADODB.RecordSet
Set rstOutput As New ADODB.RecordSet

rstOrder.ActiveConnection = CurrentProject.Connection
rstItem.ActiveConnection = CurrentProject.Connection
rstOutput.ActiveConnection = CurrentProject.Connection

rstOutput.CurserType = adOpenKeyset
rstOutput.LockType = adLockPessimistic

rstOutput.Open "SELECT * FROM tblFlatLandOrders"
'Remember to EMPTY FlatLandOrders first - or do it now.

rstOrder.Open "SELECT * FROM tblOrders"  '( Add WHERE Clause here to only select certain Orders - Eg Date bound range )

WHILE NOT rstOrder.EOF
    rstOutput.AddNew
    rstOutput!OrderId = rstOrder!OrderId
    rstOutput!OrderDate = rstOrder!OrderDate
    rstOutput!CompanyName = rstOrder!CompanyName

    intItemCount = 1
    rstItem.Open "SELECT * FROM tblItems WHERE OrderRef = " & rstOrder!OrderId"
    WHILE NOT rstItem.EOF
        If intItemCount = 10 Then
            MsgBox "Oh dear. Too Many Items.",,"A disaster has occured - and you promised so faithfully .. .."
            rstItem.MoveLast
        Else
            ' HERE COMES THE REAL CLEVER BIT.
            rstOuput("ProductId" & intItemCount) = rstItem!ProductId
            rstOuput("Quantity" & intItemCount) = rstItem!Quantity
            rstOuput("UnitPrice" & intItemCount) = rstItem!UnitPrice
            rstItem.MoveNext
            intItemCount = intItemCount + 1
        End If
    WEND
    rstOutput.Update
    rstOrder.MoveNext
WEND

rstOutput.Close
rstItem.Close
rstOrder.Close

End Sub

QED ?

G LS