Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Advice (WIN 2000 Acc 97)

    I have a table with 20 fields (all Text). Each field has either a 'Y' or is blank.
    Of the 20 fields, I am only concerned with 10 of them ('good' records). If there is a 'Y' in any of these
    10 fields, I want to include the entire record.
    The only records I want to exclude ('bad' records) are if any of these 'bad' records has a 'Y' and none of the
    'good' records has a 'Y'.

    I figured the best way to do it is to use a query with the 10 'good' fields and use Is Not Null as the criteria.
    There is sample data & a copy of the query attached. The problem I have, is that the query is not allowing me
    to enter Is Not Null on the criteria line for any field past Field 8. Is this a limitation? Do I need to write this in SQL?

    (The sample just shows 10 fields total for clarity - there are actually 20)

    Am I on the right track here?

    Thanks for helping !

    Michael
    Attached Files Attached Files

  2. #2
    Lounger
    Join Date
    Mar 2003
    Location
    Gainesville, Florida, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Advice (WIN 2000 Acc 97)

    Your attachment is an empty zip file

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Advice (WIN 2000 Acc 97)

    I just tried it and the SampleData.xls file is in there.

    Wonder why you don't see it? Anyone else try to open it?

    Michael

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Advice (WIN 2000 Acc 97)

    I get the SampleData.xls file OK. Haven't had time to study it.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query Advice (WIN 2000 Acc 97)

    Yes - I can see the attached Excel worksheet with the Access screenshot embedded.

    As to your problem, are you doing a query which involves having the Totals visible? That is one thing that puts limitations on the number of criteria.

    A second possibility is that you need to add more rows to the query grid - you can click on any row and insert additional rows - if you highlight several rows then that many rows will be added. That would be my guess as the best thing to try first.

    As far as your design, it does seem a bit cumbersome - one possibility would be to create a number which represented the various conditions and store that in a single field. Since you want to know if any of them are true, just check for a number greater than one. A Long Integer would do nicely with it's 32 bits. It does however make form design rather complex however - you would probably want to use an unbound form. Just another design approach with various tradeoffs.
    Wendell

  6. #6
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Advice (WIN 2000 Acc 97)

    Sometimes I post a question, with a feeling that the answer will be beyond my scope.
    Then there are times like these, where the answer is so simple, and I just wind up making the question more difficult
    than it should be!

    I added the rows (using Insert/Rows of course) <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> and the query runs fine and gives the
    correct results.

    Thank you very much Wendell (and everyone else) for having patience with stuff like this !

    Michael

Posting Permissions

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