Total up different values within same field

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

Guest

ok i'm a complete newb at this....

Name Room name Mon Tues Wed Thu Fri
Bob a am pm am am pm
Ed a pm am am pm am
Sid c am pm off am pm
This is a very simpe representation of the table. What i would like to be
able to do in a query is input the room name as a criteria (i did this) and
have that display a total of just the "am" for each day (can't do this!)

Ie results would be (for example table above)

Room Mon Tue Wed Thu Fri
a 1 1 2 1 1

Any ideas?
Thanks for your time
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yea, redesign the table so it isn't in spreadsheet format:

This is just an example, since I don't know what you're trying to do,
overall, in your db.

CREATE TABLE available_rooms (
rm_user TEXT(25) NOT NULL , -- probably cud link to Users table
rm_name TEXT(20) NOT NULL ,
use_day TEXT(3) NOT NULL
CHECK (use_day IN ('Mon', 'Tue', 'Wed', 'Thu', 'Fri') ,
day_period TEXT(3) NOT NULL
CHECK (day_period IN ('AM', 'PM', 'OFF') ,
CONSTRAINT PK_available_rooms
PRIMARY KEY (rm_user, rm_name, use_day, day_period)
)

The data:

Mon Tues Wed Thu Fri
rm_user rm_name use_day day_period
Bob a Mon am
Bob a Tue pm
Bob a Wed am
Ed a Mon pm
Ed a Tue am
Ed a Wed am
Sid c Mon am
Sid c Tue pm
Sid c Wed off

The query:

SELECT use_day, COUNT(*)
FROM available_rooms
WHERE rm_name = 'a'
AND day_period = 'am'
GROUP BY use_day

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQfmBpoechKqOuFEgEQJcxACfcjH6Ujfqr/f4jQk6+29BFm9BMtQAoMqq
/cKxpCGmJYPRLZWYn2796dd1
=DWb5
-----END PGP SIGNATURE-----
 
Seventh said:
ok i'm a complete newb at this....

Name Room name Mon Tues Wed Thu Fri
Bob a am pm am am pm
Ed a pm am am pm am
Sid c am pm off am pm
This is a very simpe representation of the table. What i would like to be
able to do in a query is input the room name as a criteria (i did this) and
have that display a total of just the "am" for each day (can't do this!)

Ie results would be (for example table above)

Room Mon Tue Wed Thu Fri
a 1 1 2 1 1

Any ideas?
Thanks for your time
Click to expand...

The following is untested:

SELECT YT1.RoomName
,COUNT(IIF(YT1.Mon = "am", 1, 0)) AS OutputMon
,COUNT(IIF(YT1.Tue = "am", 1, 0)) AS OutputTue
,COUNT(IIF(YT1.Wed = "am", 1, 0)) AS OutputWed
,COUNT(IIF(YT1.Thu = "am", 1, 0)) AS OutputThu
,COUNT(IIF(YT1.Fri = "am", 1, 0)) AS OutputFri
FROM YourTable AS YT1
GROUP BY YT1.RoomName
 
Back
Top