Query to Excel Groups as Separate Worksheets?

[LTillner]

I have a query that groups by grade level. I output the query to Excel. I need to do 2 things

1 - Add subtotals by a secondary grouping ('Objective_Code") - as a new row at the bottom of the query subgroup

2 - Output my query with each grade level as a separate tab to a new Excel workbook.

What are the easiest ways to do these two tasks. I'm using the gui's to do stuff in Access, unfortunately. I'm not that well versed in where to put the code, etc and I haven't been writing the SElect statements, just using the lame Access query interface.

Thanks for any help!

Lynette

 

[SkipVought]

Lynette,

What's the rationale for putting each grade on a separate sheet?

If your tabular output were ALL in one sheet, I believe that underlying requirements could be easily met with a few keystrokes using Data/Subtotals and/or Data/Pivot Table Report.

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[LTillner]

Skip,

My rationale is that the final spreadsheet has a tab for each grade level. (This is the way management wants to see the final data). I figured, why not put the data where it will end up from the start?

There are 2 queries that feed into the spreadsheet. If I start with one then I only have to move the parts and pieces to different tabs and a different spreadsheet from the 2nd, shorter query.

Thanks,
Lynette

 

[SkipVought]

Since you are at the beginning of this project, you have the opportunity to decide whether or not to use best practices in spreadsheet design or not.

"This is the way management wants to see the final data." Think of it, management cannot SEE more than one tab's worth of data at a time. From the standpoint of what they want to SEE, it should make no functional difference whether they SEE the data on separate tabs or they SEE the data as a result of a point 'n' click selection.

Let me assure you that segmenting your data into separate sheeets will severely complicate your life in days to come when changes are requested or when questions are asked related to the composite content of the data. Putting your data into multiple sheets will multiply your woes.

From Excel you could...

1. use Data/Get External Data/New Database Query/Access.... to query your table(s) and return the resultset to the sheet. The query could even be a Parameter Query with a Parameter for the class. Whenever the Class Value changes, the query returns that Class' data.

2. Sort on whatever class grouping (or have the ort done in the query) and use Data/Subtotals on the resultset.

That's about as simple as it is.

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[LTillner]

Skip,

I appreciate your comments.

I'm not segmenting the data, just the specially sorted data for the final reports in the format that Management requires.

This is automation of something that has, in the past, always been manually done, season after season.

The original data comes from a mainframe and that file is not touched, just the extracts from the file that are formatted for each posting to the intranet/network drives in Excel format are.

What I'm automating is extracting the right data set and formating it for final presentation on separate tabs of a single workbook. --- It's the old, the client is always right, even when they're wrong, game!

Anyway, thanks for your tips. I always appreciate it.

Lynette

 

[SkipVought]

Well you have a solution that can be used either way.

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]