Need help in counting in same record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with 25-30 fields. The values in these fields are text...such
as BLUE or RED. I need to count how many times BLUE appears in a record, or
how many times RED appears in a record.
I have tried so many querys, and I cant find one that works. Can anyone help?
 
You need to normalize your database. The color should be appearing in a
single field only.

But this will add up the red and the blues --

SELECT LLLL.Field1,
(IIf([Field2]="red",1,0))+(IIf([Field3]="red",1,0))+(IIf([Field4]="red",1,0))+(IIf([Field5]="red",1,0))+(IIf([Field6]="red",1,0))+(IIf([Field6]="red",1,0))+(IIf([Field7]="red",1,0))+(IIf([Field8]="red",1,0))+(IIf([Field9]="red",1,0))
AS Red,
(IIf([Field2]="blue",1,0))+(IIf([Field3]="blue",1,0))+(IIf([Field4]="blue",1,0))+(IIf([Field5]="blue",1,0))+(IIf([Field6]="blue",1,0))+(IIf([Field7]="blue",1,0))+(IIf([Field8]="blue",1,0))+(IIf([Field9]="blue",1,0)) AS Blue
FROM LLLL;
 
Thanks for your suggestion. Just want to confirm the syntax. If I just want
to count RED can I use
SELECT LLLL.Field1,

(IIf([Field2]="red",1,0))+(IIf([Field3]="red",1,0))+(IIf([Field4]="red",1,0))+(IIf([Field5]="red",1,0))+(IIf([Field6]="red",1,0))+(IIf([Field6]="red",1,0))+(IIf([Field7]="red",1,0))+(IIf([Field8]="red",1,0))+(IIf([Field9]="red",1,0))
AS Red,

Do i have to include the FROM LLLL; at the end?

KARL DEWEY said:
You need to normalize your database. The color should be appearing in a
single field only.

But this will add up the red and the blues --

SELECT LLLL.Field1,
(IIf([Field2]="red",1,0))+(IIf([Field3]="red",1,0))+(IIf([Field4]="red",1,0))+(IIf([Field5]="red",1,0))+(IIf([Field6]="red",1,0))+(IIf([Field6]="red",1,0))+(IIf([Field7]="red",1,0))+(IIf([Field8]="red",1,0))+(IIf([Field9]="red",1,0))
AS Red,
(IIf([Field2]="blue",1,0))+(IIf([Field3]="blue",1,0))+(IIf([Field4]="blue",1,0))+(IIf([Field5]="blue",1,0))+(IIf([Field6]="blue",1,0))+(IIf([Field7]="blue",1,0))+(IIf([Field8]="blue",1,0))+(IIf([Field9]="blue",1,0)) AS Blue
FROM LLLL;

LLLLL said:
I have a table with 25-30 fields. The values in these fields are text...such
as BLUE or RED. I need to count how many times BLUE appears in a record, or
how many times RED appears in a record.
I have tried so many querys, and I cant find one that works. Can anyone help?
Click to expand...
Click to expand...
 
LLLL is the name of the table I used when I tested it so use your table name
instead.

If you open the query in design view you could put this in the field row of
the grid --
Red:
(IIf([Field2]="red",1,0))+(IIf([Field3]="red",1,0))+(IIf([Field4]="red",1,0))+(IIf([Field5]="red",1,0))+(IIf([Field6]="red",1,0))+(IIf([Field6]="red",1,0))+(IIf([Field7]="red",1,0))+(IIf([Field8]="red",1,0))+(IIf([Field9]="red",1,0))



LLLLL said:
Thanks for your suggestion. Just want to confirm the syntax. If I just want
to count RED can I use
SELECT LLLL.Field1,

(IIf([Field2]="red",1,0))+(IIf([Field3]="red",1,0))+(IIf([Field4]="red",1,0))+(IIf([Field5]="red",1,0))+(IIf([Field6]="red",1,0))+(IIf([Field6]="red",1,0))+(IIf([Field7]="red",1,0))+(IIf([Field8]="red",1,0))+(IIf([Field9]="red",1,0))
AS Red,

Do i have to include the FROM LLLL; at the end?

KARL DEWEY said:
You need to normalize your database. The color should be appearing in a
single field only.

But this will add up the red and the blues --

SELECT LLLL.Field1,
(IIf([Field2]="red",1,0))+(IIf([Field3]="red",1,0))+(IIf([Field4]="red",1,0))+(IIf([Field5]="red",1,0))+(IIf([Field6]="red",1,0))+(IIf([Field6]="red",1,0))+(IIf([Field7]="red",1,0))+(IIf([Field8]="red",1,0))+(IIf([Field9]="red",1,0))
AS Red,
(IIf([Field2]="blue",1,0))+(IIf([Field3]="blue",1,0))+(IIf([Field4]="blue",1,0))+(IIf([Field5]="blue",1,0))+(IIf([Field6]="blue",1,0))+(IIf([Field7]="blue",1,0))+(IIf([Field8]="blue",1,0))+(IIf([Field9]="blue",1,0)) AS Blue
FROM LLLL;

LLLLL said:
I have a table with 25-30 fields. The values in these fields are text...such
as BLUE or RED. I need to count how many times BLUE appears in a record, or
how many times RED appears in a record.
I have tried so many querys, and I cant find one that works. Can anyone help?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top