Insert blank rows between groups?

  • Thread starter Thread starter Muppet
  • Start date Start date
M

Muppet

I have a table with groupings of data and I would like to insert an empty
row between the groupings so that I can export the query to Excel. Access
can do it so much faster than Excel could, so I would like to know if there
is a query that can do this.

I have data like:

Color
Red
Red
Red
Blue
Blue
Green
Green

And I'd like to insert a blank row between each group:

Red
Red
Red

Blue
Blue

Green
Green


In practicality, I'll be using ADO and will copy the recordset into an Excel
worksheet. I'm using Access to help me build the query so that I can see the
SQL. In Excel I know that I can use the subtotals feature but I have to do
this so many times every day and Access can manipulate my data *so* much
faster that I would want to use a SQL approach, if possible.

Is there a query that can do this?

Thanks.
 
Muppet said:
I have a table with groupings of data and I would like to insert an empty
row between the groupings so that I can export the query to Excel. Access
can do it so much faster than Excel could, so I would like to know if there
is a query that can do this.

I have data like:

Color
Red
Red
Red
Blue
Blue
Green
Green

And I'd like to insert a blank row between each group:

Red
Red
Red

Blue
Blue

Green
Green


In practicality, I'll be using ADO and will copy the recordset into an Excel
worksheet. I'm using Access to help me build the query so that I can see the
SQL. In Excel I know that I can use the subtotals feature but I have to do
this so many times every day and Access can manipulate my data *so* much
faster that I would want to use a SQL approach, if possible.

Is there a query that can do this?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

No, Access queries can't do that. Why don't you write & run a macro
(VBA code) in Excel that inserts the empty row?

== pseudo code ==

remember first row value as lastGroup
(1) get next row value as newValue
if newValue is empty row stop
if newValue <> lastGroup
insert row
lastGroup = newValue
goto (1)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQHrlCoechKqOuFEgEQLg9QCfb16eJp6Gk+ilb7iwit6BH9OlZkoAnR90
1aOagmpv4vGhwMlyvxYp6BII
=NZUh
-----END PGP SIGNATURE-----
 
Back
Top