SQL beginner: How to count search result "categories"

[Arvin Portlock]

My SQL experience is pretty limited, sorry if this is a
REALLY basic question.

I have a database of bibliographic records. Each record is
of a certain type as recorded in a Type field: books, articles,
dissertations, reviews, etc. When a user searches the database
I'd like to return a overview of results, by type:

Your search retrieved 291 records:

41 articles
2 dissertations
8 reviews

Of course clicking on each group will yield the records of
that particular Type. Ideally I guess I'd like a query generic
enough to handle any number of different Types. That is it
would include obituaries someday if any of those were added
without any changes to the SQL. Barring that, a query that
explicitely included each Type and would be updated whenever
a new Type was added is acceptable.

I've heard about, but never used, SQL keywords like UNION and
GROUP BY and weird things like that. Would this be maybe a
good way to finally learn how to use these SQL features?

I'm working in Access 2000 via ADO. Moving to SQL Server is
a possibility if Access proves inadequate.

Thanks

[Rick Brandt]

Arvin Portlock wrote:

My SQL experience is pretty limited, sorry if this is a
REALLY basic question.

I have a database of bibliographic records. Each record is
of a certain type as recorded in a Type field: books, articles,
dissertations, reviews, etc. When a user searches the database
I'd like to return a overview of results, by type:

Your search retrieved 291 records:

41 articles
2 dissertations
8 reviews

Of course clicking on each group will yield the records of
that particular Type. Ideally I guess I'd like a query generic
enough to handle any number of different Types. That is it
would include obituaries someday if any of those were added
without any changes to the SQL. Barring that, a query that
explicitely included each Type and would be updated whenever
a new Type was added is acceptable.

I've heard about, but never used, SQL keywords like UNION and
GROUP BY and weird things like that. Would this be maybe a
good way to finally learn how to use these SQL features?

I'm working in Access 2000 via ADO. Moving to SQL Server is
a possibility if Access proves inadequate.

Thanks

Assuming that the "search" is based on other fields (not the TYPE field) then
you need a simple Totals query for the basic starting point.

SELECT [Type], Count(*) As Qty
FROM TableName
WHERE (your filter criteria)
GROUP BY [Type]

Now, the challenge is to use that query with a dynamic ability to apply
different filter criteria based on the users inputs. Do you know how to do
that?

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Arvin Portlock]

Rick Brandt wrote:

Arvin Portlock wrote:

I have a database of bibliographic records. Each record is
of a certain type as recorded in a Type field: books, articles,
dissertations, reviews, etc. When a user searches the database
I'd like to return a overview of results, by type:

Your search retrieved 291 records:

41 articles
2 dissertations
8 reviews

Assuming that the "search" is based on other fields (not the TYPE
field) then
you need a simple Totals query for the basic starting point.

SELECT [Type], Count(*) As Qty
FROM TableName
WHERE (your filter criteria)
GROUP BY [Type]

Holy moley, that's incredible. Such a simple query that does
EXACTLY EXACTLY what I want! I've been testing it on a bunch
of different tables. Thanks for the quick reply. I'm going
to look at GROUP BY again. Now with a specific example where
it met my needs I might have a better chance of understanding
it this time.

Now, the challenge is to use that query with a dynamic ability to apply
different filter criteria based on the users inputs. Do you know how
to do
that?

Yes, thanks. That much I can muddle through.

Regards,

Arvin