query help

I am trying to make a query and I am stuck. There probably is a
simple solution to this so hopefully one of you can help. Here is
what I want to do:

I have a table that has 2 fields (Test Type and Date Performed). The
table is populated with 6 or 7 different test types and the date each
was performed. Each test type will be performed a bunch of times so
there will be more than one entry for each type. What I want to do is
count the number of tests for a certain test type performed before a
specified date and count the ones performed after that date. Is there
an easy way to do this?

Thanks.

"Brian" <Brian.Kibler@gmail.comwrote in message
news:fe7123e6-e146-4bc0-a814-e9763202128f@y5g2000hsf.googlegroups.com...Try this:

PARAMETERS Mdate DateTime;
TRANSFORM Count(tblTests.[test type]) AS TestCount
SELECT tblTests.[test type]
FROM tblTests
GROUP BY tblTests.[test type]
PIVOT IIf([date performed]<=[Mdate],"Before " & [Mdate],"After " & [Mdate]);

Fred Zuckerman

"Brian" <Brian.Kibler@gmail.comwrote in message
news:fe7123e6-e146-4bc0-a814-e9763202128f@y5g2000hsf.googlegroups.com...Do this with queries. Off the top of my head:

Open a query in design view and include your table. Include the two fields
on the grid along with your table's Primary Key. Click on the "totals"
toolbar item, set the Primary Key field to "Count", [Test Type] to "Group
By" and [Date Performed] to "Where". In the criteria for [Date Performed]
type (for example) <#01/01/2008# to show tests before the date. Use another
query with ">" in the date field criteria for tests after the date.

Untested but I think it should work.

Keith.
www.keithwilby.com

SELECT
Iif([Date Performed] >= [Enter a Date], "On or After", "Before") As
BeforeAfter,
Count(*) As TestCount
FROM
MyTable
WHERE
[Test Type] = [Enter a Test Type]
GROUP BY
Iif([Date Performed] >= [Enter a Date], "On or After", "Before")

If you are unfamiliar with SQL, proceed as follows:

Start a new query in the query designer.
Click the "SQL" button on the toolbar, or select it from the View menu.
Paste the above code into the SQL window, being sure to replace the
"SELECT;" text that Access fills in automatically.
Replace "MyTable" with the actual name of your table.
Change back to the Design view (again from the toolbar or view menu), so you
can study the graphical representation of the query.

You may need to modify the last line above depending on how you want to
treat tests that were *on* the date you specify.

"Brian" <Brian.Kibler@gmail.comwrote in message
news:fe7123e6-e146-4bc0-a814-e9763202128f@y5g2000hsf.googlegroups.com...

Fred, I tried yours and it worked so thanks for the help. I just have
one issue with it. How can I make it fill in a zero if there are no
tests of a certain type before/after the desired date? Right now it
just gives a blank field.

"Brian" <Brian.Kibler@gmail.comwrote in message
news:c86d49c7-3399-4d4a-afe4-e5c39a24c4f4@i12g2000prf.googlegroups.com...Unfortunately I do not know how to do that. If you use the query in a form
or report, then you can use the Nz() function to display zeros.
Fred Zuckerman

"Fred Zuckerman" <ZuckermanF@sbcglobal.netwrote in
news:kH6fj.82183$Um6.46166@newssvr12.news.prodigy. net:
@i12g2000prf.googlegroups.The Nz() function should work in the query. Try
TRANSFORM Nz(Count(tblTests.[test type]),0) AS TestCount

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

"Bob Quintal" <rquintal@sPAmpatico.cawrote in message
news:Xns9A1A6AD0AE79BBQuintal@66.150.105.47...
Cool.
Tks Bob.
I thought I had tried that before.
Fred