SQL in Access Report

[ApexData]

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]

[MGFoster]

-----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-----
Ap******@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]

[ApexData]

Thanks for the response.

I took your advice and tried the following code:

SELECT IIf([RPR]=True,"RPR",
IIf([MNT]=True,"MNT",
IIf([BAT]=True,"BAT","Unknown"))) AS GroupByThis,
[RPR],
[BAT],
[MNT],
*
FROM [T-SERVICE];

The repairs have grouped just fine, but the battery and maint will only
group with records that are not already displayed in the repairs group.

Any further advice?

[MGFoster]

Ap******@gmail.com wrote:

Thanks for the response.

I took your advice and tried the following code:

SELECT IIf([RPR]=True,"RPR",
IIf([MNT]=True,"MNT",
IIf([BAT]=True,"BAT","Unknown"))) AS GroupByThis,
[RPR],
[BAT],
[MNT],
*
FROM [T-SERVICE];

The repairs have grouped just fine, but the battery and maint will only
group with records that are not already displayed in the repairs group.

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

You may have to go back to your original UNION query, with the addition
of a GroupByThis column. E.g.:

SELECT "RPR" As GroupByThis, *
FROM [T-Service]
WHERE [RPR] = True
UNION
SELECT "BAT" As GroupByThis, *
FROM [T-Service]
WHERE [BAT] = True
UNION
SELECT "MNT" As GroupByThis, *
FROM [T-Service]
WHERE [MNT] = True
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBRE7ZwIechKqOuFEgEQKwrwCg4/+EDv8jV+47+CAGLaCoH/WDcCMAni3n
Dx9Xa1Cla3dw3zNvYjxuoxXA
=45Cm
-----END PGP SIGNATURE-----

[ApexData]

THAT WORKED !!!

Thankyou Very Much. I spent a long time on this one.
Your advice was right on target. Someone else also had
recommended a similar solution in another group, but it just
took time to sink in.

Thanks Again :)