help with a query (probably simple)

[Clay]

The data below is a list of inmates(DOCNUM) and the date
that they were sentenced(YrMnthday) and the type of Crime
they commited(CategoryNum). There are several other
fields in my main table (tblAdmissions) such as race,
sex, sentence legnth, etc. that I will need to do
calculations on. The data below are the duplicate
DOCNUM's from this table. For our calcualations, we want
to develop reports that will count the number of inmates
by different breakdowns such as race, sex, etc. for each
month of admission.

My problem, is creating a query that will return a DOCNUM
only one time for each sentence date. In the data below
for example, I want to count DOCNUM 73993 twice, because
he was admitted into prison in both Jan. and May.
However, for DOCNUM 81887, he was only admitted one time
in Dec., but for two different Crime Category's. So, for
him, I want to count the MINIMUM CategoryNum...so in his
case, I would only want the 1st record. For DOCNUM
82081, we would want to count only his CategoryNum "1"
crimes for both dates that he was admitted (Nov 29 and
Mar 12).
DOCNUM Cmnt YrMnthday CategoryNum
73993 A 20010525 2
73993 A 20010117 2
81887 A 20011205 1
81887 A 20011205 2
82081 A 20011129 3
82081 A 20010312 1
82081 A 20011129 1
82081 A 20010312 3

I hope this explains what I am trying to do. Thank you
for any help - it is greatly appreciated.

Clay

 

[John Viescas]

Create a Totals query to "boil down" your data:

SELECT DOCNUM, Cmnt, YrMnthDay, Min(CategoryNum) As Category
FROM MyTable
GROUP BY DOCNUM, Cmnt, YrMnthDay

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[MGFoster]

The data below is a list of inmates(DOCNUM) and the date
that they were sentenced(YrMnthday) and the type of Crime
they commited(CategoryNum). There are several other
fields in my main table (tblAdmissions) such as race,
sex, sentence legnth, etc. that I will need to do
calculations on. The data below are the duplicate
DOCNUM's from this table. For our calcualations, we want
to develop reports that will count the number of inmates
by different breakdowns such as race, sex, etc. for each
month of admission.

My problem, is creating a query that will return a DOCNUM
only one time for each sentence date. In the data below
for example, I want to count DOCNUM 73993 twice, because
he was admitted into prison in both Jan. and May.
However, for DOCNUM 81887, he was only admitted one time
in Dec., but for two different Crime Category's. So, for
him, I want to count the MINIMUM CategoryNum...so in his
case, I would only want the 1st record. For DOCNUM
82081, we would want to count only his CategoryNum "1"
crimes for both dates that he was admitted (Nov 29 and
Mar 12).
DOCNUM Cmnt YrMnthday CategoryNum
73993 A 20010525 2
73993 A 20010117 2
81887 A 20011205 1
81887 A 20011205 2
82081 A 20011129 3
82081 A 20010312 1
82081 A 20011129 1
82081 A 20010312 3

I hope this explains what I am trying to do. Thank you
for any help - it is greatly appreciated.

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

Just a guess:

SELECT DOCNUM Cmnt, YrMnthDay, Min(CategoryNum) As MinCategory
FROM TableName
WHERE < criteria >
GROUP BY DOCNUM Cmnt, YrMnthDay

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

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

iQA/AwUBQHMAE4echKqOuFEgEQL7mgCfTScwDskuDqSnbLKnd4oXw8gbtvYAn062
qz0IHwgI1Q56MIsEKBg2Peco
=d+zF
-----END PGP SIGNATURE-----

 

[Guest]

I think you want to create a totals query - group by
Docnum, group by categorynum, and count on yrmnthday.
So, for a given docnum, for a given categorynum, you will
get a count.

 

[John Spencer (MVP)]

Only

SELECT DocNum,
Left(YrMnthDay,6) as YrMonth,
Min(CategoryNum) as CatNum
FROM YourTable
GROUP BY DocNum, Left(YrMnthDay,6)

Or if you want to get admissions on multiple dates within the month

SELECT DocNum,
YrMnthDay,
Min(CategoryNum) as CatNum
FROM YourTable
GROUP BY DocNum, YrMnthDay

You can save the above as queryOne and then use that in a second query

SELECT T.*
FROM YourTable as T INNER JOIN QueryOne As Q
ON T.DocNum = Q.DocNum AND
T.YrMnthDay = Q.YrMnthDay AND
T.CategoryNum = Q.CatNum

You can also do this in a single query, using a subquery in the Inner Join.

SELECT T.*
FROM YourTable as T INNER JOIN
(SELECT DocNum,
YrMnthDay,
Min(CategoryNum) as CatNum
FROM YourTable
GROUP BY DocNum, YrMnthDay) As Q
ON T.DocNum = Q.DocNum AND
T.YrMnthDay = Q.YrMnthDay AND
T.CategoryNum = Q.CatNum