Group lables? Not sure how to best describe this question. 2

[Melagan]

This one should be fun ... say I have a table that looks like this:

First Last County
John Adams Fresno
Bill Will Fresno
Jane Doe Fresno
Alex Jones Los Angeles
Pete Smith Los Angeles
Robert James Ventura
Paul Garcia Ventura


And I want the SQL to present the data like this:
First Last County
Fresno
John Adams Fresno
Bill Will Fresno
Jane Doe Fresno
Los Angeles
Alex Jones Los Angeles
Pete Smith Los Angeles
Ventura
Robert James Ventura
Paul Garcia Ventura

SELECT t.First, t.Last, t.County
FROM tblLables
ORDER BY t.County

Maybe some kind of IIF statement for t.First ?

~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

I think it's more a report issue than a query ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[traingamer]

Why bother when you can write a report in 45 seconds to do it that way????????????

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Melagan]

Because I export this recordset to an excel spreadsheet. Currently, I filter the data then insert a blank row between every row, then type in the County name above each "group" so that the sheet looks like the data I posted above.

The point is to have a mailing lable print with just the county name before each group of lables for each county.

If there is a way to accomplish all of this within a report and not even bother with Excel, I'm all for that.


~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

A starting point:
SELECT t.First, t.Last, t.County FROM tblLables AS t
UNION SELECT '', '', County FROM tblLables
ORDER BY 3, 2, 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Melagan]

PH that's really close - this is the returned result.

And this is what I'm after:

I prefer the data to be layed out this way because of the way the mail merge is set up. The county field is aligned to the upper right and very opaque, whereas the First field is aligned center, bigger font, and bold. Example:

Maybe it would be easiest to leave the SQL as-is and do the special sorting/formatting in Excel VB ?



~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

and this ?
SELECT t.First, t.Last, t.County FROM tblLables AS t
UNION SELECT County, '', County FROM tblLables
ORDER BY 3, 2, 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[traingamer]

I am again amazed! I need to look a lot more closely at Union queries.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Melagan]

Nice. I second the kind comments from Traingamer and agree that I need to look into Union queries a bit further. Thanks again PH!

~Melagan
______
"It's never too late to become what you might have been.