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

G

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?
 
T

Tom Ellison

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
 
D

dcichelli

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
 
D

Duane Hookom

Is it possible to normalize your table structure? The colors should be field
values in a "color" field rather than field names.
 
G

Guest

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
 
D

Duane Hookom

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 said:
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 said:
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" <[email protected]>
wrote
in message news:[email protected]...
 
D

dcichelli

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.
 
G

Guest

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 said:
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 said:
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 said:
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" <[email protected]>
wrote
in message

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?
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top