|
|
Posted by MGFoster on 04/26/06 02:11
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
You don't need a UNION query, just a regular SELECT
(Access SQL syntax)
SELECT IIf(repair=True,"Repair",
IIf(battery=True,"Battery",
IIf(maint=True,"Maint","Unknown") As GroupByThis,
repair,
maint,
battery,
<other columns>
FROM table1
WHERE <some criteria>
Set up your report using the Grouping/Sorting dialog box
(menu bar: View > Sorting and Grouping)
Select the GroupByThis column in the Field/Expression column of the
dialog box. Set the Group Header to Yes. Place the GroupByThis column
in the Group Header column. If you want to put totals for each
grouping, set the Group Footer to Yes and set up =Sum() Text Boxes.
Now the report is set up to group each GroupByThis value.
For more info on creating report groups see the Access Help articles
under table of contents heading
Reports and Report Snapshots
Sorting and Grouping Records in a Report
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBRE6sgoechKqOuFEgEQI3YACeK41dgagIW4yD/7FgbdGQXTPX3jUAoKzC
g5e9onkszM/TwkfuX0UVVC7H
=SF3w
-----END PGP SIGNATURE-----
ApexData@gmail.com wrote:
> I have one table used to maintain information on Service Events.
> Each record contains a Repair, Maint, and Battery checkbox
> (bound/boolean). Any one of these fields may be checked or left empty.
> Ex. REPAIR=True, MAINT=False, BATTERY=True
>
> I want to generate ONE report that lists all the REPAIRS, then lists
> all the MAINT, and then lists all the BATTERY events. One group after
> the other in the same report. Because (ex. REPAIR and BATTERY) may
> both be true in the same record, the record will appear 2 times in the
> report, once in the REPAIR group and once in the BATTERY group.
>
> I am not sure how to approach this. I'm new to SQL. I' m using MS
> AccessSQL.
>
> I Tried the following code, but the results are not sorting properly
> and not grouped.
>
> SELECT * FROM [TABLE1] WHERE [REPAIR]
> UNION ALL
> SELECT * FROM [TABLE1] WHERE [MAINT]
> UNION ALL
> SELECT * FROM [TABLE1] WHERE [BATTERY]
>
Navigation:
[Reply to this message]
|