query help

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Brian
    #1

    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.
    Tags: None
    • Fred Zuckerman
      #2
      Re: query help

      "Brian" <Brian.Kibler@g mail.comwrote in message
      news:fe7123e6-e146-4bc0-a814-e9763202128f@y5 g2000hsf.google groups.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


        Comment

        • Keith Wilby
          #3
          Re: query help

          "Brian" <Brian.Kibler@g mail.comwrote in message
          news:fe7123e6-e146-4bc0-a814-e9763202128f@y5 g2000hsf.google groups.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.
          https://www.keithwilby.com


            Comment

            • Baz
              #4
              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" <Brian.Kibler@g mail.comwrote in message
              news:fe7123e6-e146-4bc0-a814-e9763202128f@y5 g2000hsf.google groups.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.

                Comment

                • Brian
                  #5
                  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.

                    Comment

                    • Fred Zuckerman
                      #6
                      Re: query help

                      "Brian" <Brian.Kibler@g mail.comwrote in message
                      news:c86d49c7-3399-4d4a-afe4-e5c39a24c4f4@i1 2g2000prf.googl egroups.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


                        Comment

                        • Bob Quintal
                          #7
                          Re: query help

                          "Fred Zuckerman" <ZuckermanF@sbc global.netwrote in
                          news:kH6fj.8218 3$Um6.46166@new ssvr12.news.pro digy.net:
                          "Brian" <Brian.Kibler@g mail.comwrote in message
                          news:c86d49c7-3399-4d4a-afe4-e5c39a24c4f4
                          @i12g2000prf.go oglegroups.
                          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(tblTes ts.[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

                            Comment

                            • Fred Zuckerman
                              #8
                              Re: query help

                              "Bob Quintal" <rquintal@sPAmp atico.cawrote in message
                              news:Xns9A1A6AD 0AE79BBQuintal@ 66.150.105.47.. .
                              "Fred Zuckerman" <ZuckermanF@sbc global.netwrote in
                              news:kH6fj.8218 3$Um6.46166@new ssvr12.news.pro digy.net:
                              >
                              >"Brian" <Brian.Kibler@g mail.comwrote in message
                              >news:c86d49c 7-3399-4d4a-afe4-e5c39a24c4f4
                              @i12g2000prf.go oglegroups.
                              >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.
                              >>
                              >Unfortunatel y 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(tblTes ts.[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


                                Comment

                                Previous template Next
                                Working...