PC Review


Reply
Thread Tools Rate Thread

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

 
 
=?Utf-8?B?SW4gbmVlZCBvZiBhc3Npc3RhbmNl?=
Guest
Posts: n/a
 
      5th May 2006


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?
 
Reply With Quote
 
 
 
 
Tom Ellison
Guest
Posts: n/a
 
      5th May 2006
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:(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?



 
Reply With Quote
 
 
 
 
dcichelli@gmail.com
Guest
Posts: n/a
 
      8th May 2006
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

 
Reply With Quote
 
Duane Hookom
Guest
Posts: n/a
 
      8th May 2006
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:(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?



 
Reply With Quote
 
=?Utf-8?B?SW4gbmVlZCBvZiBhc3Npc3RhbmNl?=
Guest
Posts: n/a
 
      9th May 2006

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:(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?

>
>
>

 
Reply With Quote
 
Duane Hookom
Guest
Posts: n/a
 
      10th May 2006
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:(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:(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?

>>
>>
>>



 
Reply With Quote
 
dcichelli@gmail.com
Guest
Posts: n/a
 
      10th May 2006
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.

 
Reply With Quote
 
=?Utf-8?B?SW4gbmVlZCBvZiBhc3Npc3RhbmNl?=
Guest
Posts: n/a
 
      10th May 2006

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:(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:(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?
> >>
> >>
> >>

>
>
>

 
Reply With Quote
 
=?Utf-8?B?SW4gbmVlZCBvZiBhc3Npc3RhbmNl?=
Guest
Posts: n/a
 
      10th May 2006

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

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Column of formulas with some cells containing data derived from thereformula's I would like to find a formula to copy all cells with data (whichis actually text) from that column to another but leaving no blank cells inthis new column barwald.simon@gmail.com Microsoft Excel Discussion 2 10th Apr 2013 11:56 PM
Can this be done with a listbox combo box. would be pretty cool =?Utf-8?B?QnJpYW5QYXVs?= Microsoft Access 3 9th Sep 2006 11:37 PM
Actually, it's Outlook Express I would like to ask about =?Utf-8?B?U3R1ZmZlc2hlYWQ=?= Microsoft Outlook 1 18th Dec 2004 12:49 AM
If only MSDN examples would actually work! Tyson Ackland Microsoft VB .NET 1 10th Jun 2004 06:37 AM
Would Like Internet Explorer To Actually Work J Harrison Windows XP Internet Explorer 0 31st Jul 2003 12:46 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 08:21 AM.