Two out of three ain't bad... actually it would be pretty nice.

Discussion in 'Microsoft Access Queries' started by Guest, May 5, 2006.

  1. Guest

    Guest Guest

    I'm trying to write a query and I can't seem to nail down how it should
    go. I have a list that consists of the following information.

    Name Red Green Blue Yellow
    Jim Yes No Yes No
    Bill Yes Yes Yes No
    Frank No Yes Yes No

    And I want to be able to select the records that have two out four, or
    maybe three out of four colors without regard as to which color it really is.
    I know how to blue blue's and green's with red's etc... but not 3 out of 4.

    Can someone help?
     
    Guest, May 5, 2006
    #1
    1. Advertisements

  2. Guest

    Tom Ellison Guest

    Dear Inneedo:

    It would be easier to provide what you need if you would provide the name of
    your table, relevant columns, and some sample data. Likely I could write
    query for this if you did.

    Tom Ellison


    "In need of assistance" <> wrote
    in message news:...
    >
    >
    > I'm trying to write a query and I can't seem to nail down how it should
    > go. I have a list that consists of the following information.
    >
    > Name Red Green Blue Yellow
    > Jim Yes No Yes No
    > Bill Yes Yes Yes No
    > Frank No Yes Yes No
    >
    > And I want to be able to select the records that have two out four, or
    > maybe three out of four colors without regard as to which color it really
    > is.
    > I know how to blue blue's and green's with red's etc... but not 3 out of
    > 4.
    >
    > Can someone help?
     
    Tom Ellison, May 5, 2006
    #2
    1. Advertisements

  3. Guest

    Guest

    Hey Inneedo...

    Ok. If your "red," "green," "blue," etc fields are checkboxes than you
    are in luck. Access assigns a numerical value to these fields. A
    checked box gets a -1 value and an unchecked box gets a 0 value. You
    may then create a query with one field as the name, and the other field
    as "SumofColors: [red] + [green] + [blue] + [yellow]" If one field is
    checked, the value will be -1, if two fields are checked the value will
    be -2 and so on. So, for the SumofColors field in your query you can
    then add a criteria such as <-1 or >=-2

    Let me know if it works!

    Diane
     
    , May 8, 2006
    #3
  4. Guest

    Duane Hookom Guest

    Is it possible to normalize your table structure? The colors should be field
    values in a "color" field rather than field names.
    --
    Duane Hookom
    MS Access MVP

    "In need of assistance" <> wrote
    in message news:...
    >
    >
    > I'm trying to write a query and I can't seem to nail down how it should
    > go. I have a list that consists of the following information.
    >
    > Name Red Green Blue Yellow
    > Jim Yes No Yes No
    > Bill Yes Yes Yes No
    > Frank No Yes Yes No
    >
    > And I want to be able to select the records that have two out four, or
    > maybe three out of four colors without regard as to which color it really
    > is.
    > I know how to blue blue's and green's with red's etc... but not 3 out of
    > 4.
    >
    > Can someone help?
     
    Duane Hookom, May 8, 2006
    #4
  5. Guest

    Guest Guest

    Re: Two out of three ain't bad... actually it would be pretty nice

    Alrighty, I'll try to be a bit more explicit rather than abstract. The
    values for the 2 out of 3 fields aren't yes/no fields but they are all either
    the same value or null so they could be if we re-did the field. They are
    either the date the election was held that year or they are null.


    Table Name: Weld_VoterHistory

    Field Names: First Name, Last Name, 2004 Pri, 2002 Pri, 2000 Pri, 1998 Pri

    Sample Values: John, Rogers, 08/2004, 08/2002, 08/2000, 08/1998

    i.e. [Weld_VoterHistory].[2004 Pri]

    Does this help clear things up? I need to pick people that have voted in 3
    out of the last 4 primaries.


    Sample Data

    First Name Last Name 2004 Pri 2002 Pri 2000 Pri 1998 Pri
    RITA ANESHAN Null Null Null Null
    LORETTA ARMFIELD Null Null Null 0898
    LOWELL ARMFIELD 0804 Null Null 0898
    JOY AUTRAND Null Null Null
    Null
    DONNA BARKER Null 0802 Null Null
    GERALD BARKER 0804 0802 0800 0898






    "Tom Ellison" wrote:

    > Dear Inneedo:
    >
    > It would be easier to provide what you need if you would provide the name of
    > your table, relevant columns, and some sample data. Likely I could write
    > query for this if you did.
    >
    > Tom Ellison
    >
    >
    > "In need of assistance" <> wrote
    > in message news:...
    > >
    > >
    > > I'm trying to write a query and I can't seem to nail down how it should
    > > go. I have a list that consists of the following information.
    > >
    > > Name Red Green Blue Yellow
    > > Jim Yes No Yes No
    > > Bill Yes Yes Yes No
    > > Frank No Yes Yes No
    > >
    > > And I want to be able to select the records that have two out four, or
    > > maybe three out of four colors without regard as to which color it really
    > > is.
    > > I know how to blue blue's and green's with red's etc... but not 3 out of
    > > 4.
    > >
    > > Can someone help?

    >
    >
    >
     
    Guest, May 9, 2006
    #5
  6. Guest

    Duane Hookom Guest

    Re: Two out of three ain't bad... actually it would be pretty nice

    Normalize, normalize, normalize, normalize, normalize.... excuse me I had to
    get this out of my system. I feel better now.

    In a properly normalized table structure, you would count the number of
    records between a couple dates. If you can't change your "worksheet"
    formatted table structure, you can use a union query to normalize. Then
    query your union query.

    --
    Duane Hookom
    MS Access MVP

    "In need of assistance" <> wrote
    in message news:...
    >
    > Alrighty, I'll try to be a bit more explicit rather than abstract. The
    > values for the 2 out of 3 fields aren't yes/no fields but they are all
    > either
    > the same value or null so they could be if we re-did the field. They are
    > either the date the election was held that year or they are null.
    >
    >
    > Table Name: Weld_VoterHistory
    >
    > Field Names: First Name, Last Name, 2004 Pri, 2002 Pri, 2000 Pri, 1998 Pri
    >
    > Sample Values: John, Rogers, 08/2004, 08/2002, 08/2000, 08/1998
    >
    > i.e. [Weld_VoterHistory].[2004 Pri]
    >
    > Does this help clear things up? I need to pick people that have voted in
    > 3
    > out of the last 4 primaries.
    >
    >
    > Sample Data
    >
    > First Name Last Name 2004 Pri 2002 Pri 2000 Pri 1998 Pri
    > RITA ANESHAN Null Null Null
    > Null
    > LORETTA ARMFIELD Null Null Null
    > 0898
    > LOWELL ARMFIELD 0804 Null Null 0898
    > JOY AUTRAND Null Null Null
    > Null
    > DONNA BARKER Null 0802 Null
    > Null
    > GERALD BARKER 0804 0802 0800 0898
    >
    >
    >
    >
    >
    >
    > "Tom Ellison" wrote:
    >
    >> Dear Inneedo:
    >>
    >> It would be easier to provide what you need if you would provide the name
    >> of
    >> your table, relevant columns, and some sample data. Likely I could write
    >> query for this if you did.
    >>
    >> Tom Ellison
    >>
    >>
    >> "In need of assistance" <>
    >> wrote
    >> in message news:...
    >> >
    >> >
    >> > I'm trying to write a query and I can't seem to nail down how it
    >> > should
    >> > go. I have a list that consists of the following information.
    >> >
    >> > Name Red Green Blue Yellow
    >> > Jim Yes No Yes No
    >> > Bill Yes Yes Yes No
    >> > Frank No Yes Yes No
    >> >
    >> > And I want to be able to select the records that have two out four, or
    >> > maybe three out of four colors without regard as to which color it
    >> > really
    >> > is.
    >> > I know how to blue blue's and green's with red's etc... but not 3 out
    >> > of
    >> > 4.
    >> >
    >> > Can someone help?

    >>
    >>
    >>
     
    Duane Hookom, May 10, 2006
    #6
  7. Guest

    Guest

    While I totally agree with Duane, we can still tweak this.

    You can create a query that sets up fields for each year and translates
    the null vs. vales to numbers as such:

    2004Sum: IIf(IsNull([2004]),0,1)

    Then you can create another query or report on that query to sum each
    row by person.
     
    , May 10, 2006
    #7
  8. Guest

    Guest Guest

    Re: Two out of three ain't bad... actually it would be pretty nice

    Thanks for the help, I would agree that this table is a disaster. I didn't
    set it up, just trying to make sense out of it now. I appreciate the advice.

    "Duane Hookom" wrote:

    > Normalize, normalize, normalize, normalize, normalize.... excuse me I had to
    > get this out of my system. I feel better now.
    >
    > In a properly normalized table structure, you would count the number of
    > records between a couple dates. If you can't change your "worksheet"
    > formatted table structure, you can use a union query to normalize. Then
    > query your union query.
    >
    > --
    > Duane Hookom
    > MS Access MVP
    >
    > "In need of assistance" <> wrote
    > in message news:...
    > >
    > > Alrighty, I'll try to be a bit more explicit rather than abstract. The
    > > values for the 2 out of 3 fields aren't yes/no fields but they are all
    > > either
    > > the same value or null so they could be if we re-did the field. They are
    > > either the date the election was held that year or they are null.
    > >
    > >
    > > Table Name: Weld_VoterHistory
    > >
    > > Field Names: First Name, Last Name, 2004 Pri, 2002 Pri, 2000 Pri, 1998 Pri
    > >
    > > Sample Values: John, Rogers, 08/2004, 08/2002, 08/2000, 08/1998
    > >
    > > i.e. [Weld_VoterHistory].[2004 Pri]
    > >
    > > Does this help clear things up? I need to pick people that have voted in
    > > 3
    > > out of the last 4 primaries.
    > >
    > >
    > > Sample Data
    > >
    > > First Name Last Name 2004 Pri 2002 Pri 2000 Pri 1998 Pri
    > > RITA ANESHAN Null Null Null
    > > Null
    > > LORETTA ARMFIELD Null Null Null
    > > 0898
    > > LOWELL ARMFIELD 0804 Null Null 0898
    > > JOY AUTRAND Null Null Null
    > > Null
    > > DONNA BARKER Null 0802 Null
    > > Null
    > > GERALD BARKER 0804 0802 0800 0898
    > >
    > >
    > >
    > >
    > >
    > >
    > > "Tom Ellison" wrote:
    > >
    > >> Dear Inneedo:
    > >>
    > >> It would be easier to provide what you need if you would provide the name
    > >> of
    > >> your table, relevant columns, and some sample data. Likely I could write
    > >> query for this if you did.
    > >>
    > >> Tom Ellison
    > >>
    > >>
    > >> "In need of assistance" <>
    > >> wrote
    > >> in message news:...
    > >> >
    > >> >
    > >> > I'm trying to write a query and I can't seem to nail down how it
    > >> > should
    > >> > go. I have a list that consists of the following information.
    > >> >
    > >> > Name Red Green Blue Yellow
    > >> > Jim Yes No Yes No
    > >> > Bill Yes Yes Yes No
    > >> > Frank No Yes Yes No
    > >> >
    > >> > And I want to be able to select the records that have two out four, or
    > >> > maybe three out of four colors without regard as to which color it
    > >> > really
    > >> > is.
    > >> > I know how to blue blue's and green's with red's etc... but not 3 out
    > >> > of
    > >> > 4.
    > >> >
    > >> > Can someone help?
    > >>
    > >>
    > >>

    >
    >
    >
     
    Guest, May 10, 2006
    #8
  9. Guest

    Guest Guest

    Re: Two out of three ain't bad... actually it would be pretty nice

    Thanks, I'll give this a shot. I'm confident that it will work.


    "" wrote:

    > While I totally agree with Duane, we can still tweak this.
    >
    > You can create a query that sets up fields for each year and translates
    > the null vs. vales to numbers as such:
    >
    > 2004Sum: IIf(IsNull([2004]),0,1)
    >
    > Then you can create another query or report on that query to sum each
    > row by person.
    >
    >
     
    Guest, May 10, 2006
    #9
    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. Bayou BoB

    How would I make a Union Query out of these two simple tables?

    Bayou BoB, Jan 27, 2004, in forum: Microsoft Access Queries
    Replies:
    3
    Views:
    232
    Duane Hookom
    Jan 28, 2004
  2. mario
    Replies:
    1
    Views:
    218
    Michel Walsh
    Mar 2, 2004
  3. Dave

    A pretty easy one for the experts

    Dave, Mar 12, 2004, in forum: Microsoft Access Queries
    Replies:
    4
    Views:
    161
    David
    Mar 15, 2004
  4. Bill Foley

    Two Out Of Three Ain't Bad...

    Bill Foley, Feb 24, 2007, in forum: Microsoft Access Queries
    Replies:
    4
    Views:
    133
    Bill Foley
    Feb 24, 2007
  5. Phil Smith

    Query Too Complex But it is pretty simple.

    Phil Smith, Jun 23, 2009, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    165
    Jeff Boyce
    Jun 23, 2009
Loading...

Share This Page