Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

And and Or Queries

Discussion in 'Information Technology' started by doublezer00, Jul 28, 2006.

Tags:
  1. doublezer00

    doublezer00
    Expand Collapse
    Guest

    Hiya,

    wonder if anyone can help me get a pay rise:)

    got a database say:

    name code
    geff a1
    geff a2
    fred a1


    in query i used Or for "a1" Or "a2" and it brings up codes a1 or a2, if i
    use And it brings back nothing. i want to query multiple codes and reult only
    all codes contained. so if query is a1 and a2 i would not want fred to be
    displayed. there are thousands of codes and hundreds of names, names are on a
    seperate table for reference and multiple names are used in the main matrix.

    please help, ive tried for three days and got nuthin!
    please email me, i need some experienced contacts.

    be well:)
     
  2. Loading...

    Similar Threads Forum Date
    Importance of Hukamnama - Some Queries Sikh Sikhi Sikhism Jun 14, 2009
    Queries History of Sikhism Oct 26, 2006

  3. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    Just to clarify, it sounds like you have multiple rows in your table, with
    data like the example.

    So if you look for a code of "a1" AND "a2", you'll never find it! That's
    because you'll only ever have ONE code in the code field (as it should be).

    What is it about "geff" that makes excluding "fred" important. Is it that
    "geff" has more than one record? If that's the case, run a query that
    returns all the names of folks with more than one record. Then join that
    query back to the table and find all the folks who are NOT on the list of
    "more than one record".

    By the way, if your database has more than one "fred", are they the same
    person more than once, or two (or more) different people?

    Regards

    Jeff Boyce
    Microsoft Office/Access MVP

    "doublezer00" <preston_george@msn.com> wrote in message
    news:3869782D-17BB-4E82-A67E-1545ACD22497@microsoft.com...
    > Hiya,
    >
    > wonder if anyone can help me get a pay rise:)
    >
    > got a database say:
    >
    > name code
    > geff a1
    > geff a2
    > fred a1
    >
    >
    > in query i used Or for "a1" Or "a2" and it brings up codes a1 or a2, if i
    > use And it brings back nothing. i want to query multiple codes and reult
    > only
    > all codes contained. so if query is a1 and a2 i would not want fred to be
    > displayed. there are thousands of codes and hundreds of names, names are
    > on a
    > seperate table for reference and multiple names are used in the main
    > matrix.
    >
    > please help, ive tried for three days and got nuthin!
    > please email me, i need some experienced contacts.
    >
    > be well:)
     
  4. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    Try this --
    SELECT DoubleZer00.Name, DoubleZer00.Code, DoubleZer00_1.Code
    FROM DoubleZer00 INNER JOIN DoubleZer00 AS DoubleZer00_1 ON DoubleZer00.Name
    = DoubleZer00_1.Name
    WHERE (((DoubleZer00.Code)="a1") AND ((DoubleZer00_1.Code)="a2"));


    "doublezer00" wrote:

    > Hiya,
    >
    > wonder if anyone can help me get a pay rise:)
    >
    > got a database say:
    >
    > name code
    > geff a1
    > geff a2
    > fred a1
    >
    >
    > in query i used Or for "a1" Or "a2" and it brings up codes a1 or a2, if i
    > use And it brings back nothing. i want to query multiple codes and reult only
    > all codes contained. so if query is a1 and a2 i would not want fred to be
    > displayed. there are thousands of codes and hundreds of names, names are on a
    > seperate table for reference and multiple names are used in the main matrix.
    >
    > please help, ive tried for three days and got nuthin!
    > please email me, i need some experienced contacts.
    >
    > be well:)
     
  5. Sylvain Lafontaine

    Sylvain Lafontaine
    Expand Collapse
    Guest

    You have hundred of code? Hum, not sure that you will find an easy way out
    of this but for your particular exemple, you might try something like:

    Select * From T as T1
    Where (code = 'a1' and Exists (select * from T as T2 where T2.Code = 'a2'
    and T2.name = T1.name)) Or
    (code = 'a2' and Exists (select * from T as T2 where T2.Code = 'a1' and
    T2.name = T1.name))

    or maybe:

    Select * From T as T1
    Where name in (Select name from T as T2 Group By Name Having Count(*) >= 2)


    However, as you have hundred of code, I think that you should learn how to
    use temporary tables and the Group By and Having clauses.

    --
    Sylvain Lafontaine, ing.
    MVP - Technologies Virtual-PC
    E-mail: http://cerbermail.com/?QugbLEWINF


    "doublezer00" <preston_george@msn.com> wrote in message
    news:3869782D-17BB-4E82-A67E-1545ACD22497@microsoft.com...
    > Hiya,
    >
    > wonder if anyone can help me get a pay rise:)
    >
    > got a database say:
    >
    > name code
    > geff a1
    > geff a2
    > fred a1
    >
    >
    > in query i used Or for "a1" Or "a2" and it brings up codes a1 or a2, if i
    > use And it brings back nothing. i want to query multiple codes and reult
    > only
    > all codes contained. so if query is a1 and a2 i would not want fred to be
    > displayed. there are thousands of codes and hundreds of names, names are
    > on a
    > seperate table for reference and multiple names are used in the main
    > matrix.
    >
    > please help, ive tried for three days and got nuthin!
    > please email me, i need some experienced contacts.
    >
    > be well:)
     
  6. Sylvain Lafontaine

    Sylvain Lafontaine
    Expand Collapse
    Guest

    Of course, there is an error in my previous post about the second exemple
    and where you have to add the tests for 'a1' and 'a2' code in the subquery
    (the one with the Group By clause). This exemple was more about retrieving
    all records with more than a single code. Here's the correct one:

    Select * From T as T1
    Where name in (Select name from T as T2 Where code = 'a1' or code = 'a2'
    Group By Name Having Count(*) >= 2

    --
    Sylvain Lafontaine, ing.
    MVP - Technologies Virtual-PC
    E-mail: http://cerbermail.com/?QugbLEWINF


    "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
    wrote in message news:eILSutlmGHA.2112@TK2MSFTNGP04.phx.gbl...
    > You have hundred of code? Hum, not sure that you will find an easy way
    > out of this but for your particular exemple, you might try something like:
    >
    > Select * From T as T1
    > Where (code = 'a1' and Exists (select * from T as T2 where T2.Code = 'a2'
    > and T2.name = T1.name)) Or
    > (code = 'a2' and Exists (select * from T as T2 where T2.Code = 'a1' and
    > T2.name = T1.name))
    >
    > or maybe:
    >
    > Select * From T as T1
    > Where name in (Select name from T as T2 Group By Name Having Count(*) >=
    > 2)
    >
    >
    > However, as you have hundred of code, I think that you should learn how to
    > use temporary tables and the Group By and Having clauses.
    >
    > --
    > Sylvain Lafontaine, ing.
    > MVP - Technologies Virtual-PC
    > E-mail: http://cerbermail.com/?QugbLEWINF
    >
    >
    > "doublezer00" <preston_george@msn.com> wrote in message
    > news:3869782D-17BB-4E82-A67E-1545ACD22497@microsoft.com...
    >> Hiya,
    >>
    >> wonder if anyone can help me get a pay rise:)
    >>
    >> got a database say:
    >>
    >> name code
    >> geff a1
    >> geff a2
    >> fred a1
    >>
    >>
    >> in query i used Or for "a1" Or "a2" and it brings up codes a1 or a2, if i
    >> use And it brings back nothing. i want to query multiple codes and reult
    >> only
    >> all codes contained. so if query is a1 and a2 i would not want fred to be
    >> displayed. there are thousands of codes and hundreds of names, names are
    >> on a
    >> seperate table for reference and multiple names are used in the main
    >> matrix.
    >>
    >> please help, ive tried for three days and got nuthin!
    >> please email me, i need some experienced contacts.
    >>
    >> be well:)

    >
    >
     

Share This Page