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.

Re: query help

"Brian" wrote in message
news:fe7123e6-e146-4bc0-a814-e9763202128f@y5g2000hsf.googleg roups.com...
>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.

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

Re: query help

"Brian" wrote in message
news:fe7123e6-e146-4bc0-a814-e9763202128f@y5g2000hsf.googleg roups.com...
>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.

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

Re: query help

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" wrote in message
news:fe7123e6-e146-4bc0-a814-e9763202128f@y5g2000hsf.googleg roups.com...
>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.

Re: query help

"Fred Zuckerman" wrote in
news:kH6fj.82183$Um6.46166@newssvr12.news.prodigy.net:

> "Brian" wrote in message
> news:c86d49c7-3399-4d4a-afe4-e5c39a24c4f4
@i12g2000prf.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.
>
> 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
>
>
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

Re: query help

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.

Re: query help

"Brian" wrote in message
news:c86d49c7-3399-4d4a-afe4-e5c39a24c4f4@i12g2000prf.google groups.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.

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

Re: query help

"Bob Quintal" wrote in message
news:Xns9A1A6AD0AE79BBQuintal@66.150.105.47...
> "Fred Zuckerman" wrote in
> news:kH6fj.82183$Um6.46166@newssvr12.news.prodigy.net:
>
>> "Brian" wrote in message
>> news:c86d49c7-3399-4d4a-afe4-e5c39a24c4f4
> @i12g2000prf.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.
>>
>> 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
>>
>>
> 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


Cool.
Tks Bob.
I thought I had tried that before.
Fred