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?

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" <(E-Mail Removed)> wrote
in message news:2786121E-D6D7-438B-A220-(E-Mail Removed)...
>
>
> 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?

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

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" <(E-Mail Removed)> wrote
in message news:2786121E-D6D7-438B-A220-(E-Mail Removed)...
>
>
> 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?

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" <(E-Mail Removed)> wrote
> in message news:2786121E-D6D7-438B-A220-(E-Mail Removed)...
> >
> >
> > 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?
>
>
>

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" <(E-Mail Removed)> wrote
in message news:A42AFD37-4A47-414C-9769-(E-Mail Removed)...
>
> 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" <(E-Mail Removed)>
>> wrote
>> in message news:2786121E-D6D7-438B-A220-(E-Mail Removed)...
>> >
>> >
>> > 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?
>>
>>
>>

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.

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" <(E-Mail Removed)> wrote
> in message news:A42AFD37-4A47-414C-9769-(E-Mail Removed)...
> >
> > 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" <(E-Mail Removed)>
> >> wrote
> >> in message news:2786121E-D6D7-438B-A220-(E-Mail Removed)...
> >> >
> >> >
> >> > 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?
> >>
> >>
> >>
>
>
>

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


"(E-Mail Removed)" 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.
>
>