multiple product column into one row 1

[msii]

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

I am running a SQL 7 database with an Access interface/frontend.

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 UnitPrice) (ProductID Quantity UnitPrice) etc . . 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


The MAX number of orders per customer allowed by fullfillment would be NINE. But in my case the max would be FIVE with the other total 4 product related fields blank.

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,

MSII
klarson@msiincdirect.com

 

[malexanian]

How are your coding skills? Would you prefer T-SQL in SQL Server 7.0 or VBA in Access? There are fairly simple solutions to both.

 

[msii]

VBA Access would be great.

I will be looking forward to your reply.

I have spent so much time trying to figure this out . . .

I can't wait to hear back from you.

Once a gain thanks . . .

 

[malexanian]

We will use VBA to generate a tab-delimited string. Since I don't know which version you are using, I will use DAO (I haven't used the ADO stuff in Access itself yet; it's been a while). Note the use of the CurrentDb object... I don't think anything after 97 has this; you may have to modify the code for use with ADO and Access's ADO objects. Also note that I surround all text fields with double quotes. This makes the job of recognizing text for the importing application much easier.

Create a regular module in Access. Call it basWhatever.

<begin>
Option Explicit

Public Function ProductText(ByVal OrderID As Long) As String

Dim db as DAO.Database, rs as DAO.Recordset
Set db = CurrentDB

Set rs = db.OpenRecordset(&quot;SELECT ProductID, Quantity,
UnitPrice
FROM tblLineItem
WHERE OrderID = &quot; &
CStr(OrderID) & &quot;;&quot;,
dbOpenForwardOnly)
' Use an ORDER BY clause if you want the products
sorted.

ProductText = &quot;&quot;
If rs.EOF Then Exit Sub
' If no products for this order, return empty string
With rs
Do Until .EOF
ProductText = ProductText &
!ProductID & vbTab &
&quot;&quot;&quot;&quot; & !Quantity & &quot;&quot;&quot;&quot; & vbTab &
!UnitPrice & vbTab
.MoveNext
Loop
.Close
End With

' Trim the last tab off.
Set ProductText = Left$(ProductText, Len(ProductText)-1)

Set rs = Nothing
Set db = Nothing

End Function
</end>

This function takes the order ID and generates one string with all products for that order ID. To use it:

Create a query: Note the use of Chr$(9) for the tab character.
SELECT
rderDate & Chr$(9) &
&quot;&quot;&quot;&quot; & o.CompanyName & &quot;&quot;&quot;&quot; & Chr$(9) &
rderID & Chr$(9) & ProductText(rderID) AS Line
FROM tblOrder o

This query creates a view with a single field, Line, which contains all the data you need in a tab-delimited format. You can simply copy the data and paste it into Excel. Excel is intelligent enough to break it into separate columns by tab.

Let me know if you have any other questions.

 

[msii]

Thanks . . .I'll give it shot.

 

[msii]

Thanks for all the help Malex, One other thing, Could you elaborate on the querie. I'm not quite understanding how to get that to work. I've got the module to work but can't seem to get the querie to work.

Thanks

 

[entaroadun]

The query has to be run in Access, since it uses the Access VBA function, Chr$(). Also note that the query is being run off of tblOrder, not tblOrderLineitem. This table should be a parent to tblOrderLineitem, which is the original table you named at the beginning of this thread. This is so you only get one line for every orderID.

The query should return only one field with the entire line in a tab-delimited format.