Strange behavior on query - Help??

Discussion in 'Microsoft Access Queries' started by Tim, Dec 13, 2004.

  1. Tim

    Tim Guest

    I have tblA with unique PrimaryKeyX.
    Then tblB has many PrimaryKeyX matches, and for each there is a fieldY
    that is either True or False.

    tblB does not have a related match to every record in tblA, but every
    record in tblB does relate to tblA by the PrimaryKeyX.

    So, I need 2 queries. One that shows all records in tblA where the
    related tblB records have ALL of their FieldY set to False, or where
    there are no matches in tblB.
    This works nice and quick as follows:

    SELECT tblA.PrimaryKeyX
    FROM tblA
    WHERE False = ALL (SELECT tblB.fieldY FROM tblB WHERE
    tblA.PrimaryKeyX= tblB.PrimaryKeyX)

    The other thing I need is: All records in tblA where the related
    records in tblB where ONE OR MORE of the FieldY are set to True.

    It was suggested to me that I achieve this by changing the above as
    follows (changing FALSE to TRUE, and changing ALL to SOME):

    SELECT tblA.PrimaryKeyX
    FROM tblA
    WHERE True = Some (SELECT tblB.fieldY FROM tblB WHERE
    tblA.PrimaryKeyX= tblB.PrimaryKeyX)

    HOWEVER... everytime I try and run this one, Access fails and wants me
    to send an error report to Microsoft and repair my database.

    I've tried creating an entirely new database, setting up fresh links
    to my SQL server for tables, and importing the queries from old, but
    the problem persists.

    I would VERY much appreciate any help. All other solutions to getting
    this query take too long to run.
     
    Tim, Dec 13, 2004
    #1
    1. Advertisements

  2. Tim

    John Vinson Guest

    On Mon, 13 Dec 2004 12:28:16 -0500, Tim <> wrote:

    >The other thing I need is: All records in tblA where the related
    >records in tblB where ONE OR MORE of the FieldY are set to True.


    It's much simpler than you're making it: since you're just checking
    for the existance of any record, you can use a simple Join.

    SELECT DISTINCT tblA.PrimaryKeyX
    FROM tblA INNER JOIN tblB
    ON tblA.PrimaryKeyX
    WHERE tblB.fieldY=True;

    The DISTINCT will cause the query to show only one record from tblA,
    no matter how many occurances of FieldY are True; but if there are
    none, there will be no match and therefore no retrieval.

    John W. Vinson[MVP]
    Join the online Access Chats
    Tuesday 11am EDT - Thursday 3:30pm EDT
    http://community.compuserve.com/msdevapps
     
    John Vinson, Dec 13, 2004
    #2
    1. Advertisements

  3. Tim

    Tim Guest

    This results in the error: "Join expression not supported". I've
    looked at the meaning behind the error and tried playing with it but
    can't figure it out.
    Does that error make sense to you?
    Thanks for the help!

    On Mon, 13 Dec 2004 13:43:23 -0700, John Vinson
    <jvinson@STOP_SPAM.WysardOfInfo.com> wrote:

    >SELECT DISTINCT tblA.PrimaryKeyX
    >FROM tblA INNER JOIN tblB
    >ON tblA.PrimaryKeyX
    >WHERE tblB.fieldY=True;
     
    Tim, Dec 13, 2004
    #3
  4. PARDON ME for jumping in.

    John forgot the second part of the join in his sample SQL

    SELECT DISTINCT TblA.PrimaryKeyX
    FROM TblA INNER JOIN TblB
    ON tblA.PrimaryKeyX = tblB.PrimaryKeyX
    WHERE TblB.FieldY = True

    Tim wrote:
    >
    > This results in the error: "Join expression not supported". I've
    > looked at the meaning behind the error and tried playing with it but
    > can't figure it out.
    > Does that error make sense to you?
    > Thanks for the help!
    >
    > On Mon, 13 Dec 2004 13:43:23 -0700, John Vinson
    > <jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
    >
    > >SELECT DISTINCT tblA.PrimaryKeyX
    > >FROM tblA INNER JOIN tblB
    > >ON tblA.PrimaryKeyX
    > >WHERE tblB.fieldY=True;
     
    John Spencer (MVP), Dec 14, 2004
    #4
  5. Tim

    John Vinson Guest

    On Mon, 13 Dec 2004 20:12:31 -0500, "John Spencer (MVP)"
    <> wrote:

    >PARDON ME for jumping in.
    >
    >John forgot the second part of the join in his sample SQL
    >
    >SELECT DISTINCT TblA.PrimaryKeyX
    >FROM TblA INNER JOIN TblB
    >ON tblA.PrimaryKeyX = tblB.PrimaryKeyX
    >WHERE TblB.FieldY = True


    Thanks John! Too hasty there; my apologies, Tim.

    John W. Vinson[MVP]
    Join the online Access Chats
    Tuesday 11am EDT - Thursday 3:30pm EDT
    http://community.compuserve.com/msdevapps
     
    John Vinson, Dec 14, 2004
    #5
  6. Tim

    Tim Guest

    Thanks a lot guys!
    That was painfully simple wasn't it :).

    On Mon, 13 Dec 2004 20:12:31 -0500, "John Spencer (MVP)"
    <> wrote:

    >PARDON ME for jumping in.
    >
    >John forgot the second part of the join in his sample SQL
    >
    >SELECT DISTINCT TblA.PrimaryKeyX
    >FROM TblA INNER JOIN TblB
    >ON tblA.PrimaryKeyX = tblB.PrimaryKeyX
    >WHERE TblB.FieldY = True
    >
    >Tim wrote:
    >>
    >> This results in the error: "Join expression not supported". I've
    >> looked at the meaning behind the error and tried playing with it but
    >> can't figure it out.
    >> Does that error make sense to you?
    >> Thanks for the help!
    >>
    >> On Mon, 13 Dec 2004 13:43:23 -0700, John Vinson
    >> <jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
    >>
    >> >SELECT DISTINCT tblA.PrimaryKeyX
    >> >FROM tblA INNER JOIN tblB
    >> >ON tblA.PrimaryKeyX
    >> >WHERE tblB.fieldY=True;
     
    Tim, Dec 14, 2004
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Dale Fye

    Strange query behavior

    Dale Fye, May 14, 2004, in forum: Microsoft Access Queries
    Replies:
    0
    Views:
    146
    Dale Fye
    May 14, 2004
  2. Dale Fye

    Strange Query Behavior (Repost)

    Dale Fye, May 17, 2004, in forum: Microsoft Access Queries
    Replies:
    4
    Views:
    148
    Guest
    May 17, 2004
  3. ag

    Strange behavior when closing query

    ag, Aug 19, 2004, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    125
    Allen Browne
    Aug 20, 2004
  4. Sunny

    strange behavior on query result

    Sunny, Feb 25, 2005, in forum: Microsoft Access Queries
    Replies:
    3
    Views:
    167
    Chris2
    Feb 25, 2005
  5. AG

    Strange Query Behavior

    AG, Aug 10, 2006, in forum: Microsoft Access Queries
    Replies:
    5
    Views:
    176
    Wei Lu [MSFT]
    Aug 14, 2006
Loading...

Share This Page