Need help with a query using dynamic data

[jax1000rr]

Hello,



I need to create a query that will group by a column named defect_no in my table.

My issue is that this field is dynamic and can have up to 15 different values at any given time; and I need to be able to export a list of records by the corresponding defect_no into individual XLS files based on the defect_no field. Some days there may only be 3 different values for the defect_no column and others there may be all 15.

Thoughts?

TIA and please let me know if more clarification is needed.....it's been a long day!

[Dal Jeanis]

If your issue is that you need to export to different spreadsheets based upon the value of the field, then you either create a single query that pulls all the records, SORTED by that field but not grouped by it, and process each record, opening the appropriate XLS whenever the sort field changes...

or, you create ONE query that GROUPs by the field, store those values in an array, then cycle through the items in the array, and for each, open a SECOND query to get the records for that XLS, process them, then close the XLS and get the next item from the array.

It's relatively simple VB. I think I posted an example a couple of weeks back, but I don't know what to search for to find it.

There's an example of loading an array via SQL here https://www.accessforums.net/queries...00/index2.html

I couldn't find the "writing to an excel spreadsheet" code right off the bat.

[ssanfu]

@Dal:

You might check out Ken Snell's site: Ken's ACCESS Examples and Information for Working With EXCEL Workbook Files
http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm


(one of my many 'reference' sites )

[Dal Jeanis]

@ssanfu - Perfect. Good site.

I actually have working examples of an export to excel at work, but I was just "burning some midnight electrons" at home.