Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query question (2000)

    I'm trying to have a query return records (that contain a name field and date field) for which the same name appears in the name field more than twice for a certain time period. I know how to set the date criteria, but I can't figure out how to set the name criteria. Can you help?

    Eric

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (2000)

    Add 3 fields to a query: Name (twice) and date (just once). Turn on your subtotals (Sigma button). Set the subtotal of the second name field to Count and its crietria to >2. Then enter the desired dates in the criteria for the date field.

    HTH <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  3. #3
    2 Star Lounger
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (2000)

    Thanks Mark,
    However, it doesn't seem to be working. It's simply giving me all the names for the entered date. The second name field is returning the total number of fields that contain any name. What am I doing wrong?

    Eric

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (2000)

    DOH! I'm sorry - I gave bad advice. Please don't tell anyone. <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>
    That's what I get for giving instructions without testing it first.

    Ok - I've tested this and it works. Create 2 Queries.
    Query 1) From your table, enter the following fields:
    Name, Name, and Date
    Turn on Totals
    Make the second Name field a Count
    Criteria: Enter your date criteria

    Query 2) From your first query, enter the following fields:
    Name, CountOfName
    Turn on Totals
    Make the CountOfName field a Sum
    Criteria: >2 (or whatever your needs happen to be)

    That should do it. Again, sorry about the incorrect advice the first time.

    HTH <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  5. #5
    2 Star Lounger
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (2000)

    Thanks! That works the way it's supposed to, however I have another problem. My date criteria is actually a date range (a whole month or year) and the count is giving me a result per date in that range. So when I enter >2 for the count criteria I only get those names within the date range that show up more than twice in any one day instead of more than twice for the whole month. Any ideas?

    Thanks,
    Eric

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (2000)

    I should have elaborated on the date criteria. You should use the syntax: Between Date1 And Date2. For example: Between #1/1/01# And #2/1/01#

    (As you know, Access will enter the "#" for you as you type in the criteria field of the query.)

  7. #7
    2 Star Lounger
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (2000)

    That's what I was doing. But the query returns a list of names and how many times they appear per single date. So there are some names that appear many times but with a number in the count field for how many times on each particular date they show up. Then when I add the >2 criteria. I only have two names because they appeared more than twice for a single date. What I want is to narrow it down to names that appear a total of more than twice for all the dates in any month, year, etc. But there were names that appeared three times in the month, but only once for any particular date. So when I entered >2, they didn't show up. Does what I'm trying to do make sense?

    Thanks for your help,
    Eric

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (2000)

    have you tried a find duplicates query

  9. #9
    2 Star Lounger
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (2000)

    Well, I figured it out. Maybe you can figure out a better way, but I've got three queries set up. In the first I just query for the records for the date range I need. The second contains only Name and Name count fields with criteria >2 for the name count. This gives me the names that occur more than three times in a given time span. In the third I have the rest of the data from the records I need from the first query, with the names from the second query as the criteria for Name.

    Thanks for your help,
    Eric

  10. #10
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (2000)

    have you tried this without grouping on the date in the query criteria

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •