Comments by Wosu (Top 5 by date)

Wosu 21-May-12 8:58am View

First I created a table t1 AS:
CREATE Table t1 (SID Varchar 15 NOT NULL, MM1 int NOT NULL,
MA1 int NOT NULL,
ME1 int NOT NULL,
TM1 int NOT NULL,
TA1 int NOT NULL,
TE1 int NOT NULL,
WM1 int NOT NULL,
WA1 int NOT NULL,
WE1 int NOT NULL,
ThM1 int NOT NULL,
ThA1 int NOT NULL,
The1 int NOT NULL,
FM1 int NOT NULL,
Fa1 int NOT NULL,
FE1 int NOT NULL)

NOTE
WHERE MM1 = Monday Morning Week1
MA1 = Monday Afternoon Week1
ME1 = Monday Evening Week1
-
-
-
-
-
FM1 = Friday Morning Week1
FA1 = Friday Afternoon Week1
FE1 = Friday Evening Week1.
If for instance the following values are inserted into t1:

Insert INTO t1(SID, MM1, MA1, ME1, TM1, TA1, TE1, WM1, WA1, WE1, ThM1, ThA1, ThE1, FM1, FA1, FE1)
VALUES(Wk001, 1, 1, 2, 3, 2, 2, 1, 2, 1, 3, 3, 1, 2, 1, 2)

My challenge is how to query the table t1 to Add up the 1s separately row-wise;
Also a query to Add up the 2s separately and divide the Sum of the 2s by 2 in order to obtain the actual times the value 2 occured. The same for the 3s.

In my effort I did this:

SELECT SID, MM1+MA1+ME1+TM1+TA1+TE1+WM1+WA1+WE1+ThM1+ThA1+ThE1+FM1+FA1+FE1 AS [Times Rain]
FROM dbo.t1
WHERE MM1=1 OR
MA1 = 1 OR
ME1 = 1 OR
TM1=1 OR
TA1=1 OR
TE1=1 OR
WM1=1 OR
WA1=1 OR
WE1=1 OR
ThM1=1 OR
ThA1=1 OR
ThE1=1 OR
FM1=1 OR
FA1=1 OR
FE1=1

Instead of this query giving me 6, the result i got was 27. Which is wrong!

I also tried to get the times the 2s occured as:

SELECT SID, MM1/2+MA1/2+ME1/2+TM1/2+TA1/2+TE1/2+WM1/2+WA1/2+WE1/2+ThM1/2+ThA1/2+ThE1/2+FM1/2+FA1/2+FE1/2 AS [Times Shine]
FROM dbo.t1
WHERE MM1=2 OR
MA1 = 2 OR
ME1 = 2 OR
TM1=2 OR
TA1=2 OR
TE1=2 OR
WM1=2 OR
WA1=2 OR
WE1=2 OR
ThM1=2 OR
ThA1=2 OR
ThE1=2 OR
FM1=2 OR
FA1=2 OR
FE1=2

This also failed to work for me!
Please a working solution will be appreciated, thanks.

Wosu 21-May-12 7:58am View

Thanks losmac. I appreciate ur time and codes, however in my case scenario, my Database Table t1 and its Attributes are connected programatically to 3 Combo Boxes that I created on a 5 VB.NET Window Forms to represent 5 different days for instance. The 3 Combo Boxes on the 5 different Forms are labelled Morning, Afternoon and Evening respectively. Each of the Combo Box provides the items- Rainfall; Sun shine; and Dew Cover. With this application interface, a User is only expected to select from the Combo Box the item that applies for the time of the day and finally clicks on Update button - which updates the Table t1 defualt value of zero(0). This is the logic by which t1 is populated. My challenge is just how to Add up the 1s returned for Rainfall, the 2s returned for Sunshine and the 3s returned for the Dew cover row-wise. In order to provide accurate result for the No. of times it Rained; The No. of times it Shine & No. of times Dew covered. For the number of days in question.

Wosu 18-May-12 9:41am View

Imagine that you need to keep record of how many times in the days of a week or month or year that Rain fell, Sun shine and Dew covered a certain geographic region. Now imagine that this parameters are to be recorded in the mornings, Afternoons, and Evenings. So if I decide to assign 1 for Rainfall, 2 for Sun shine and 3 for Dew Cover as nature presents them in the Morning, Afternoon and Evening of the days of a certain week for instance. I know that I will need to divide the sum total of the Sun shine and Dew cover by 2 and 3 respectively to be able to have the accurate number of times the event occured. OK, my challenge is how to create a View that would accurately Sum the times Rainfell(i.e the 1s) and the times Sun shine(i.e the 2s) and the times Dew covered(i.e the 3s) Row wish. Thanks

Wosu 18-May-12 9:06am View

The result should be displayed as a View. Thanks

Wosu 18-May-12 9:03am View

Deleted

The result should be displayed as a View. Thanks