Query record set into single excel row

[bruegel]

First of all, I may be using the wrong terms and would be grateful for anyone who can correct me.



I need to convert info currently in an Access 2003 database into a single row for use in an excel spreadsheet. The actual is quite complex and will probably use nested queries so I'll create a simple example based on a smoothie recipe.

Access structure (record set from simple query of three tables)

Item Type Amount in ml

Apple Fruit 100
Pear Fruit 150
Carrot Vegetable 75
Tomato Vegetable 60

The result I need in contiguous excel cells is

Apple Fruit 100 Pear Fruit 150 Carrot Vegetable 75 etc

No calculated fields required. Please don't say I need VBA

Thanks for help

Pieter

[June7]

How many records need to do this with? Even Excel has a limit on how many columns. Is there another identifier field that can be used to group records? This might be possible with multiple crosstab queries then join the crosstabs.

Need to know the real data structure. If you want to provide the db for analysis, follow instructions at bottom of my post.

Why is this necessary?

[bruegel]

The reason:

(I'll try to keep this simple. If I go into full detail we'll both be here all night. You might drop off reading this anyway}.

I keep my basic data for each project in access 2003 tables in a single mdb. I use a variety of gui's set up in seperate mdbs to access the basic data and query it for my own use. That way one gui can interrogate different sets of raw data simply by linking to tables in a different basic mdb.

I also work with various companies around the world which need some of my raw data for use in their programs. These are all designed to import from excel with all the information pertaining to a particular item in a single row. Virtually all my tables have one to many relationships with another table or two (using junction tables). However my foreign partners don't need all my data (nor can they all use the same field format)

I therefore generate the excel files separately for each partner. It's actually not as bad as it sounds since 90% of the info needed can be pulled out in a single row. There is however this one element where anything between two and seven "ingredients"- which Access would usually display in a table with several rows - has to be transposed into a single row. My business is growing and I have got to the point where I have to supply each new partner with about 8,000 rows of info (each of up to 50 columns).

The way I was approaching it was to have a "pot" of queries each doing its own job and picking out as it suits. Simple example - some want two fields for first name and last name, others need Last Name, First name so I insert a concatenate expression as needed. I was looking for a (make table?) query which would transpose the access table record set into a single row. I could then drop this info into the individual queries for each partner as needed. I did try using Allen Browne's concatenated related records function to create a simple CSV file but only managed to display my inadequate understanding of VBA.


Sorry if that was an over complicated explanation.

Pieter

[June7]

Allen Browne's function will concatenate data into a single string but if you need the data to remain as distinct values, that is not the approach to take.

You addressed my curiosity about why and touched on what you have tried but offers little new info about the data structure. Surely you don't really want 8,000 rows of data transposed into a single Excel row?

I can best explore solutions by working with data. As I said, multiple crosstabs might accomplish what you want but I need a better understandng of the data.

[bruegel]

The item count is 8000 so that's the no of rows. Am preparing some dat for you to look at. Pieter

[bruegel]

Have attached a zip with sample mdb and xls. Hope with above info the formatting will make sense

thanks

Pieter

[June7]

Clarify, you want data of related records to be combined into one line? Example from your WriterShare query:

SongTitle	WriterName	WritersShare
Yeah Sista (303 Version)	Harrow, David	100.00%
Yeah Sista (Drum And Bass Style)	Harrow, David	100.00%
Yeah Sister	Harrow, David	100.00%
Ysaebud	Burns, Gary	25.00%
Ysaebud	Kooner, Jagz	25.00%
Ysaebud	Weatherall, Andrew	50.00%

Should output like:
Yeah Sista (303 Version): Harrow, David 100.00%
Yeah Sista (Drum And Bass Style): Harrow, David 100.00%
Yeah Sister: Harrow, David 100.00%
Ysaebud: Burns, Gary 25.00%; Kooner, Jagz 25.00%; Weatherall, Andrew 50.00%

Allen Browne's concatenation function is capable of doing this. The function can, however, run very slow.

I don't see how to accomplish this through queries alone. VBA appears to be necessary no matter if the data is concatenated to a single string or kept as distinct values.

[bruegel]

Thanks for the insight. Your conclusion about my output aim is correct. I suspected I was at the limits of my understanding and I guess I will have to find someone who can write a module for me. Should I mark solved? Pieter

[ssanfu]

You might check out Ken Snell's site at http://www.accessmvp.com/KDSnell/EXCEL_Export.htm
Scroll down until you see "Write Data From a Recordset into an EXCEL Worksheet using Automation (VBA)"

It might give you some ideas on how to proceed.

[June7]

I used the reference offered by ssanfu as a guide to write and test procedure specific to your data.

Option Compare Database
Option Explicit

Sub ExportMusicList()
Dim lngColumn As Long, lngRow As Long
Dim xlx As Object, xlw As Object, xls As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean
Dim strTitle As String
blnEXCEL = False
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
      Set xlx = CreateObject("Excel.Application")
      blnEXCEL = True
End If
Err.Clear
On Error GoTo 0
xlx.Visible = True
Set xlw = xlx.Workbooks.Open("D:\Forums\SabresInputExample.xls")
Set xls = xlw.Worksheets("Sheet1")
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("WriterShare", dbOpenDynaset, dbReadOnly)
lngRow = 1
lngColumn = 2
If rst.EOF = False And rst.BOF = False Then
    rst.MoveFirst
    Do While rst.EOF = False
        If strTitle = rst!SongTitle Then
            xls.Cells(lngRow, lngColumn).Value = rst!WriterName
            xls.Cells(lngRow, lngColumn + 1).Value = rst!WritersShare
            rst.MoveNext
            lngColumn = lngColumn + 2
        Else
            strTitle = rst!SongTitle
            lngRow = lngRow + 1
            lngColumn = 2
            xls.Cells(lngRow, 1).Value = strTitle
        End If
    Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Set xls = Nothing
Set xlw = Nothing
Set xlx = Nothing
End Sub

[bruegel]

Thanks for the help guys. I really appreciate it. I need to bone up on my VB. This will take a good few days as I'm a real beginner. (I'm not IT dept but enjoy databases and SQL as an intellectual excercise which has a bearing on my job but is by no means a huge part of it. VB is just one more thing on the list of things to learn along with Japanese and playing piano). I'll mark thread as solved in the meantime. If you'd like feedback I'm on bruegel@gms.com thanks Pieter