Hi All --

I have a lot of experience with programming databases and the like, but I
cannot for the life of me remember how to do this. I have been tasked with
creating a summary report in MS Access that will show the Classified and
Unclassified Roads in a Road System.

Table: tblRoad
The relevant Fields in this table are:
District (Text) -- PK
Road_System (Text)
Road_Number (Text)
Classified_Road (Yes/No)

The Grouping should be something like this:

District "Alpha"
Road System "Foo1"
Classified Roads: 33
Unclassified Roads: 89
Road System "Foo2"
Classified Roads: 102
Unclassified Roads: 462
Road System "Foo3"
Classified Roads: 0
Unclassified Roads: 22
District "Beta"
Road System "Foo4"
Classified Roads: 1298
Unclassified Roads: 3

You get the idea. The District is the primary key in this query, and the
query should display how many roads are classified and how many roads are
unclassified in a given Road System in a given District. I have done some
reading and screwing around with SQL and I'm pretty sure I need to use the
Count() function and group by Road System and District, but nothing I've
tried works. Actually that's not true. I wrote a tremendously complicated
UNION query that does work, sort of, but I KNOW there has to be a simpler
solution. I mean, reports like these are the reason they invented SQL in
the first place! And I'm pretty sure that I did something like this a long
time ago. I just can't remember how I did it.

Can anyone throw me a bone, here?

TIA,
Greg

Greg Burns wrote:
Summer course?

If you search the archives, I think someone posted a similar question recently
(ie sometime THIS year). It was something about road segments and paths
between start and destination.

--
Ed Prochak
running http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost

"Greg Burns" <gburns (AT) gci (DOT) net> wrote

[...]

how about

create table roads (
district char(10) not null primary key,
system char(10) not null,
number char(10) not null,
classified char(1) not null,
check (classified in ('y', 'n'))
)

select
district,
system,
count(case when classified = 'y' then 1 else null end) as
classified,
count(case when classified = 'n' then 1 else null end) as
nonclassified,
count(1) as nr_of_roads
from roads
group by district, system

if you would like a "total" over district as well you can either use
"grouping sets" - that is if your db supports them -, or construct
them your self as

select
district,
system,
count(case when classified = 'y' then 1 else null end) as
classified,
count(case when classified = 'n' then 1 else null end) as
nonclassified,
count(1) as nr_of_roads
from roads
group by district, system
union all
select
district,
'Total' as system,
count(case when classified = 'y' then 1 else null end) as
classified,
count(case when classified = 'n' then 1 else null end) as
nonclassified,
count(1) as nr_of_roads
from roads
group by district, 'Total'


HTH
/Lennart

the above email no longer works due to spam.
lennart D0t jonsson aT enlight D0t net

"Lennart Jonsson" <lennart (AT) kommunicera (DOT) umea.se> wrote


Don't know what database the "(case when classified = 'y' then 1 else null
end)" construct is from, but the following works in Oracle.

select t0.district, t0.system, nvl(class,0) as classified, nvl(unclass,0) as
unclassified
from
(
select distinct district, system
from roads
) t0
,
(
select district, system, count(*) as class
from roads
where classified='y'
group by district, system
) t1
,
(
select district, system, count(*) as unclass
from roads
where classified='n'
group by district, system
) t2
where t0.district = t1.district(+)
and t0.system = t1.system(+)
and t0.district = t2.district(+)
and t0.system = t2.system(+)

Hi all --

Thanks for your suggestions; they were all excellent. However, I am
unfortunately constrained to using Microsoft Access, so a SQL Server /
Oracle solution wasn't going to work for me. The solution I lit upon was
using a Crosstab query. Here is the SQL for the query:

First, I set up a basic Select query that included all the fields I would
need:

Query Name: qryRoads
SELECT tblRoad.District, tblRoad.Road_System, tblRoad.Road_Number,
tblRoad.Classified_Road
FROM tblRoad;

Query Name: qryRoads_Crosstab
TRANSFORM Count(qryRoads.Road_Number) AS CountOfRoad_Number
SELECT qryRoads.District, qryRoads.Road_System, Count(qryRoads.Road_Number)
AS [Total Of Road_Number]
FROM qryRoads
GROUP BY qryRoads.District, qryRoads.Road_System
PIVOT qryRoads.Classified_Road;

This query's output looked something like this:
[District], [Road_System], [Total Of Road_Number], [-1], [0]
Petersburg, KAK, 167, 40, 127
Petersburg, KUI, 389, 72, 317
Petersburg, MIT, 52, 44, 8

The "-1" and "0" fields refer to the two possible values returned by the
Classified_Road field (-1 is True, 0 is False). The report I generated from
this query was EXACTLY what I needed.

Again, thanks for your help. Although I didn't use any specific solution
presented here, it put me on the right track. I appreciate it.

Happy coding!
Greg


"Greg Burns" <gburns (AT) gci (DOT) net> wrote

nvl(val, replaceWithThisIfValIsNull)

nvl replaces any nulls returned by the value given


Exactly. I don't know why they don't use the standard SQL syntax (maybe they
do in later releases).