Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2010
    Posts
    14

    Unanswered: Query not returning all records that match criteria

    Unfortunately I don't have time to look through all the posts to see if my issue has been posted previously. So here goes.

    I am trying to run a query using Access 2007.

    I am using two tables to return clients and addresses. My criteria is from table one status column should = client (drop down list), and from table two email (yes/no field) should be no. At least one client record, that I know meets the criteria, is not being returned (there appear to be more because the number is far lower than the number of clients we have).

    See attachement for screen shot of my query design view.

    Removing the email column critera returned all clients.
    Last edited by Vallynmar; 07-12-10 at 11:27.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No screenshot attached.
    The SQL of the query, rather than a screenshot, is better and easier for us however
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2010
    Posts
    14
    How would I get the SQL?

  4. #4
    Join Date
    Jul 2010
    Posts
    14
    Never mind I found it.

    SELECT [CYAM Contacts].Prefix, [CYAM Contacts].[First Name], [CYAM Contacts].[Last Name], [CYAM Contacts].[Default Addr Line 1], [CYAM Contacts].[Address Line 2], [CYAM Contacts].[Default Addr City], [CYAM Contacts].[Default Addr State], [CYAM Contacts].[Default Addr Zip], [CYAM Contacts].Status, [CYAM Contact Subscriptions].Email
    FROM [CYAM Contacts] LEFT JOIN [CYAM Contact Subscriptions] ON ([CYAM Contacts].ID = [CYAM Contact Subscriptions].[First Name]) AND ([CYAM Contacts].ID = [CYAM Contact Subscriptions].[Last Name])
    WHERE ((([CYAM Contacts].Status)="CLIENT") AND (([CYAM Contact Subscriptions].Email)=No))
    ORDER BY [CYAM Contacts].[Last Name], [CYAM Contacts].Status;

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What does this return?
    Code:
    SELECT [CYAM Contact Subscriptions].Email, COUNT(*) AS counter
    FROM [CYAM Contacts] INNER JOIN [CYAM Contact Subscriptions] ON ([CYAM Contacts].ID = [CYAM Contact Subscriptions].[First Name]) AND ([CYAM Contacts].ID = [CYAM Contact Subscriptions].[Last Name])
    WHERE [CYAM Contacts].Status="CLIENT"
    GROUP BY [CYAM Contact Subscriptions].Email
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jul 2010
    Posts
    14
    should I replace the whole code or add it somewhere.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Put it in a new query and run it. The result will tell us something about your data,
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jul 2010
    Posts
    14
    email counter
    checked 4
    unchecked 76

  9. #9
    Join Date
    Jul 2010
    Posts
    14
    Somethings to note:

    If I use the design query function it seems to affect my results. I used the query wizard then set the criteria and was able to get what appears to be the correct results. It seems to be something with the subscriptions table that is the issue. When I ran the manually designed query without the criteria, there were several records that did not have their yes/no box checked that should have. This was true with all of the yes/no fields not just the email field.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That is likely the LEFT OUTER join. You would need to post the two SQL statements in question to get a definitive answer.

    Are you saying you have now solved the problem?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jul 2010
    Posts
    14
    No i have not solved the problem totally. I will need to be doing more queries with more criteria set and I don't think they will work. Which two statements do you need?

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You probably already know this but...

    For yes/no defined fields, they should only have 1 of 2 values: True/Yes or False/No. They shouldn't be "blank" unless you didn't make them a Yes/No type field. If they are indeed Yes/No type fields, a blank (unchecked) value would indicate False/No.
    Last edited by pkstormy; 07-17-10 at 12:21.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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