i can't even think how to describe it...

[emmahope206]

Hi All,

I have a table that looks like this (but is considerably longer):



Sub Area Area
Birmingham West Midlands
Coventry West Midlands
Glasgow Scotland
Edinburgh Scotland
Highlands Scotland

and I need results like this:

Area SubArea1 SubArea2 SubArea3
West Midlands Birmingham Coventry
Scotland Glasgow Edinburgh Highlands

There are a varying number of subareas per area but never any more than 10.

Please can anyone help, i've been racking my brains but can't come up with a solution.

[June7]

Doubt this can be done with a single query, perhaps with a sequence of queries. VBA could definitely be written to read the records into another table in the revised order. For an experienced programmer the code would be quite simple.

How many Areas?

[emmahope206]

10 areas at present but i'm hoping to future-proof, it could be more.

[June7]

I should have asked earlier. Did you already consider a conventional report with grouping on Area and SubAreas listed under each group? This is easily produced with report Grouping & Sorting features. Is this not acceptable output structure?

[emmahope206]

Hi June7,

Thanks for the help but unfortunately it won't do the trick. I need to export it to Excel.

On top of all this, i need a solution that solely uses non-action queries (i.e. no 'make tables' etc. This is because this is a data set that changes constantly & the end-users refresh their Excel report via a button press in Excel.

Any help anyone else can suggest would be greatfully received.

[June7]

VBA can do this. Think I would build the VBA in the workbook. Code would connect to the Access file, read in a recordset, cycle through the recordset and save values to cells of worksheet. Here is link to an Excel workbook that has code doing that. It won't run because I am not including the source data but you can see the code. http://www.box.net/shared/iig01rg0ut
Some worksheets are hidden. Can't remember if I left this copy protected. If it is protected there is no password, just press Enter.

You could have button in Access that would open the workbook by a hyperlink.

Alternative is to build the entire procedure in Access and push the data to Excel one value at a time. Over a network the first idea is probably better. Only one call over the network and that is to run the SQL that builds the recordset.

Changes in dataset should not preclude use of CREATE TABLE or purging an existing table and rewriting records. The source table structure doesn't change, right? This thread http://forums.aspfree.com/microsoft-...ry-322123.html has an example of how I transpose data from column to record. Utilizes a 'temp' table - a table that holds data only for the duration of the process then is purged. Your situation is complicated because areas do not have the same number of subareas, but that can be dealt with.

Regardless of which method used, code will have to deal with the changing data. Even changes in the source table structure could be dealt with.

What needs to be done with the data in Excel? Nothing to prevent the source table being exported.