Export to Excel from Query, Need Pointers

[SaskiFX]

I need a suggestion for the correct way to do something, so I can begin some research in that direction.



I have a search form that users can use to narrow down the contents of my database to a subset of records. They can view a quick listing of the results there, or open a more detailed view of a single record. I want to allow them to export the search results out to an excel file. When doing the export I want to allow the user to customize the output to only the information they need about each record. So currently, when a user has narrowed down his search, pressing the export button brings up a smaller window with a list of Headers and check boxes for each one. User will check the boxes he wants to, which will add those headers to the export, then clicks Finish and it generates the file. Seems simple enough in my head.

The search form that the user is accessing is running off a query to bring all my table data together. (Based on this.) However, that query (QRY_SearchAll) does not include everything that a user could select for export. So I duplicated that query: qryExport will use all the same search options, but also show me everything possible to export. I figure whatever I use to do the export can run off that query perhaps.

So the real questions is what direction do I go next? After the user has selected his/her headers, do I use those checks and a bunch of if statements to build a new SQL string and re-query only what I need exported? Use the already created query (qryExport) and add the records to the excel file one by one with a loop of some sort, omitting un-checked sections? Perhaps use or generate a query to create a temporary table so I can use a simple DoCmd.TransferSpreadsheet?

Also to take into consideration, I need to do some data manipulation as I perform the export. I have a field that has data that I want to separate as I export it. Inside Access as the user is using the DB he/she will never need it seperated, so I figure doing it at the time if export would be ok, especially if I am adding to excel record by record with a loop setup. (Example: a field has "4.5-1/8-10N 5056 AL Honeycomb Core" listed in Access, when doing the export I want to split out the beginning of that into 3 different columns using Split(). If needed I could separate these out inside access, and just never display them to the user.)

Any suggestions of a direction to take that would be considered the right way to do things? Or am I so far down the rabbit hole going the wrong direction that I have no real "right way" left?

Thanks!

[June7]

If you want to use TransferSpreadsheet or open the export wizard, then must be a query object that can be named in the arguments. So if you want to allow users to select fields for export, use QueryDefs to save the desired query. Here is example:

Private Sub btnExcel_Click()
Dim qdfUser As DAO.QueryDef
Dim strSQL As String
CurrentDb.QueryDefs.Delete ("UserQuery")
'code to build SQL string
strSQL = "something"
Set qdfUser = CurrentDb.CreateQueryDef("UserQuery", strSQL)
DoCmd.OpenQuery "UserQuery", , acReadOnly
DoCmd.RunCommand acCmdExportExcel 'or use TransferSpreadsheet method
End Sub

[SaskiFX]

So you suggest creating a new query on the fly as it were and then exporting that, then tossing it when finished. That sounds doable.

How about for the splitting of fields when I do the export? I was thinking of starting with something like this method because I can then make the excel file look "pretty" (Column widths, etc) for the end user, but its exporting everything at once, not one record at a time.

[June7]

That code shows opening query then exporting with TransferSpreadsheet, which is the same as I suggested. A lot of bells and whistles (data validation and error handling) but I don't see anything in there that allows picking fields or splitting data.

Splitting data into multiple fields would be part of the procedure that builds the SQL statement for the CreateQueryDef action.

[SaskiFX]

Splitting data into multiple fields would be part of the procedure that builds the SQL statement for the CreateQueryDef action.

My ignorance of SQL is showing. How would I go about doing that in the SQL statement, or are you speaking of something else?

[June7]

Like:

Dim strSQL As String

strSQL = "SELECT Left([fieldname], InStr([fieldname],"-")-1) AS Part1, Mid([fieldname], InStr([fieldname],"-")+1, InStrRev([fieldname],"-")-InStr([fieldname],"-")-1) AS Part2, Mid([fieldname], InStrRev([fieldname],"-")+1) AS Part3 FROM tablename;"

This assumes every record has value in the field and every value has all 3 parts and always 2 hyphens separating the 3 parts and no other hyphens.

How much of the 3rd part text do you want? Just the 10N?

[SaskiFX]

Alright, I have the query being built when someone clicks my export button. I have the logic on the form for the check boxes, and my code checks to see which boxes are checked to include those parts in the SQL string. It successfully concatenates them together and executes the query. Yea! Its even using TransferSpreadsheet successfully.

I scrapped the idea of using SQL or VBA to split out the data on that one field, and I just have it in the table itself now. (There were only about 30 different entries for the table, so it was manageable.) Now I have a different conundrum.

When I am done searching, each item returned may have Stresses associated with it. The number of Stresses for each item is variable, so I can't pre-plan the fields for export. I currently have another query that using a function generates a Column with the stress ID's for each stress, seperated by a comma. So it looks like this: "LB001A, LB001L, LT001U, LB001U, LT002U". This one has enough test data to generate 5 for this particular item, and the codes mean different things in our setup.

When this data gets exported to excel, I want the end users to be able to see/sort the data based on these stresses, so I don't want to leave them all in one field which would be simple. I want to take the stress codes, put them into appropriate columns and then only use the columns I need.

Perhaps an example will help some. Say I have two search results, and regardless of the other data I have these stresses getting returned by my query. (The codes stand for L(T)###(S). L for Load, T is they type of load (bending, shear, etc), ### is the code, and S is the scenario (Actual, limit, ultimate, etc.))
Item 1: LB001A, LB001L, LT001U, LB001U, LT002U
Item 2: LB001A, LB001L, LT002U, LC001U

On the spreadsheet I'm looking for:


Any pointers or suggestions on which direction to head on this?

[June7]

Your spreadsheet image not showing in post.

Not understanding why stresses should not be one field.

[SaskiFX]

I posted the image of the spreadsheet here: http://i.imgur.com/Zz5uzs2.png

I want it in more than one field so the end user can go in and sort the results in excel by the different stresses. So for example the user could be looking at a sheet with 200 entries and sort to the top all the entries that have Compression, Ultimate loads.

Also the more granular my data is, the better for the end user.

[June7]

Sorry, that site is blocked. I can try looking at image tonight.

However, still doesn't answer questions:

Why Excel and not just Access forms and reports to display filtered records?

Why stresses can't be single column/field.

Do you know how to manually set lookup in Excel?

Will probably be a steep learning curve to figure out the coding for what you describe. Manipulating Excel object from Access VBA always a challenge. Not coding I can nor desire to do for you.

[SaskiFX]

That's kind of what I was afraid of. I was trying to think if I could do it all within Access. Is it possible to loop through the results of a query? Perhaps have access loop record by record, examine the Stresses field, increment some counters. Then could I use ALTER TABLE on query results (if this is possible) to add the needed Columns, then loop one more time over the Stresses and fill the fields as needed. Then when I use TransferSpreadsheet I don't really have to tinker with Excel really.

[June7]

Yes, code can modify table but would be better to output as a query.

I still don't understand what you want to do with stresses. Hopefully the image will be informative.

[June7]

I can see the image now. Really looks like a CROSSTAB query. I see the column headings based on the letters but what are the 3-digit codes for? I see two Load - Tension, Ultimate columns because of LT001U and LT002U codes. Are those codes the data?

What does the raw data table look like?

[SaskiFX]

The 3 digit codes are just identifiers. The end data won't display these codes, it will be replaced with the actual data. So perhaps LT001U would end up saying something like "300lbs Pull Blah Blah". I'm not sure what the actual stress data is going to look like. The issue I run into is that I have no idea how many stresses each item may need to have entered. So maybe there is only one "Tension, Ultimate" and "Bending, Ultimate" load for a given item, but the next item has two "Tension, Ultimate" and a "Bending, Actual". If I had a guarantee that each item was only going to have one type of each load, then I could go ahead and make assumptions on column headers. The only real assumption I can make is any particular item won't have the exact same stress code twice, as it would kick back a duplicate primary key.

So my table for this data looks like:

tblStressID
- ItemID
- StressID
- StressType
- LoadType
- RawDetails
- ExactDetails

ItemID links back to a table full of the various items. StressID is the code (LT001U), StressType is the kind of load (Tension), LoadType is the type of load (Ultimate), RawDetails will be a plain text bit, and ExactDetails will be some standard formatting of the RawDetails.

ItemID and StressID are tagged as primary keys so I can't dupe them, and ItemID is tied back to the main table.

[June7]

Meant I would like to see actual data. Why don't you know what actual stress data will look like? I don't understand RawDetails and ExactDetails. What field has the actual stress value? Won't this be a number?

What is the db for? Stress of what?

ItemID and StressID are compound PK in this table?