Results 1 to 6 of 6
  1. #1
    Join Date
    May 2006
    Posts
    386

    Unanswered: Query does not filter correctly based on various fieldsd

    Dear All,
    I am stuck with creating a query which does not work. Bascially, this is the scenario:

    1- We have a database of students, as part of their registrations we ask them to submit certain documents.
    2- Some students have not submitted all the required documents and we now want to create a query to give us a list of all students who have not sbumitted certain documents. For example,

    Mike Logan has not submitted the following documents

    CV Proof of Qualification Proof of ID Guarantee Letter

    However, he has submitted Proof of Income and Proof of Savings.

    Now, lets assume that we have 20 students who have submitted some documents and not all.
    To make it clearer, lets say 10 students have submitted all of the above documents but not Proof of Qualifications

    The fields I have used for this is a Yes or NO field, so when I create the query, in the criteria row I have written "No" or is Null to get a list of all those who have not submitted certain documents but the query but unfortunately, the query filter is not correct.

    Bascially, even if some one has got Yes for one document does not appear in the list?
    Emi-UK
    Love begets Love, Help Begets Help

  2. #2
    Join Date
    Nov 2011
    Posts
    5
    In your criteria field you can put the following:

    Is Null to look for empty fields

    or

    "" to look for zero length entries.


    That should work. If not then there may be other issues.

    Let me know if it works?

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    without eeing the query and the table design its tough to give an answer
    howeevr from what you are saying it sounds like you have one table with all your data in
    my guess is your query is probable using an OR rather than and AND in your where clause.


    I'd also suggest you change your design to have a default value of 'no' rather than null.


    but it doens't matter that much.

    my guess is your SQL will be something like:-

    Code:
    select my, column, list from mytable
    where not acolumn = true
    or not bcolumn =true
    or not ccolumn =true
    or not dcolumn =true
    or not ecolumn =true
    or not fcolumn =true
    order by some, column, sequence
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2011
    Posts
    5
    As stated above,

    It will be helpful to know the initial structure of your tables at first. For example they should be normalised correctly in the first instance.

    A simple SQL query incorporating criteria to display null fields is fairly simple and in a well structured database it would produce the correct result.

    Again quite a difficult question to answer without seeing the structure of your database

  5. #5
    Join Date
    May 2006
    Posts
    386
    Thank you for all your supports.
    Healdem, you are very rigth in your guess. I do have one table that has the fields and you are also write that I use Or rather than AND, should I use And instead?

    Re the default value, as i had added this field at a time when the database had 1000s of records so those previous records have null value when all new records do have No as default value.

    Chrisguk, are you suggesting I should just copy and paste your criteria as it is?
    Emi-UK
    Love begets Love, Help Begets Help

  6. #6
    Join Date
    Nov 2011
    Posts
    5
    Quote Originally Posted by Emal View Post
    Thank you for all your supports.
    Healdem, you are very rigth in your guess. I do have one table that has the fields and you are also write that I use Or rather than AND, should I use And instead?

    Re the default value, as i had added this field at a time when the database had 1000s of records so those previous records have null value when all new records do have No as default value.

    Chrisguk, are you suggesting I should just copy and paste your criteria as it is?
    "" or IS Null should work as is

Posting Permissions

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