Duncan,
Suppose your table looks like:
VolunteerName, ServiceDate, StartTime, EndTime
Tom Smith, 11/1/2001, 09:30:00, 11:30:00
Tom Smith, 11/15/2001, 06:00:00, 16:00:00
Tom Smith, 01/17/2002, 04:30:00, 06:30:00
CREATE VIEW vwVolunteerHours +
(VolunteerName, Hours2001, Hours2002, Hours2003, Hours2004,
Hours2005, Hours2006) +
AS SELECT +
VolunteerName, +
(IFEQ( (IYR(2001)),((EndTime-StartTime)/60),0 )),
(IFEQ( (IYR(2002)),((EndTime-StartTime)/60),0 )),
(IFEQ( (IYR(2003)),((EndTime-StartTime)/60),0 )),
(IFEQ( (IYR(2004)),((EndTime-StartTime)/60),0 )),
(IFEQ( (IYR(2005)),((EndTime-StartTime)/60),0 )),
(IFEQ( (IYR(2006)),((EndTime-StartTime)/60),0 ))
(IFEQ( (IYR(2002)),((EndTime-StartTime)/60),0 )),
(IFEQ( (IYR(2003)),((EndTime-StartTime)/60),0 )),
(IFEQ( (IYR(2004)),((EndTime-StartTime)/60),0 )),
(IFEQ( (IYR(2005)),((EndTime-StartTime)/60),0 )),
(IFEQ( (IYR(2006)),((EndTime-StartTime)/60),0 ))
Tom Smith, 2,0,0,0,0,0
Tom Smith, 10,0,0,0,0,0
Tom Smith, 0,2,0,0,0,0
CREATE VIEW vwSumVolunteerHrs +
(VolunteerName, Hours2001, Hours2002, Hours2003, Hours2004,
Hours2005, Hours2006) +
AS SELECT +
VolunteerName, +
SUM(Hours2001) +
SUM(Hours2002) +
SUM(Hours2003) +
SUM(Hours2004) +
SUM(Hours2005) +
SUM(Hours2006) +
GROUP BY VolunteerName
Tom Smith, 12,2,0,0,0,0
Then you can get your list by using a query such as:
SELECT VolunteerName +
FROM vwSumVolunteerHrs +
WHERE +
Hours2001 < 50 AND +
Hours2002 < 50 AND +
Hours2003 < 50 AND +
Hours2004 < 50 AND +
Hours2005 < 50 AND +
Hours2006 < 50
Hours2004 < 50 AND +
Hours2005 < 50 AND +
Hours2006 < 50
That will list each volunteer whose hours are less than 50 in every
year.
By creating views as illustrated, you may not need a report.
You can simply:
BROWSE ALL +
FROM vwSumVolunteerHrs +
WHERE +
Hours2001 < 50 AND +
Hours2002 < 50 AND +
Hours2003 < 50 AND +
Hours2004 < 50 AND +
Hours2005 < 50 AND +
Hours2006 < 50
Hours2004 < 50 AND +
Hours2005 < 50 AND +
Hours2006 < 50
If you only care about 2003 and 2004, your query can include
only those years:
SELECT VolunteerName from vwSumVolunteerHrs +
FROM vwSumVolunteerHrs +
WHERE +
Hours2003 < 50 AND +
Hours2004 < 50
If your table has only the hours volunteered, your first view will be simpler than what I showed.
If your table has only the hours volunteered, your first view will be simpler than what I showed.
Of course, you can change your select statements into views if you
look at this data very often:
CREATE VIEW vw2003LT50 +
VolunteerName, Hours2003 +
AS SELECT +
VolunteerName, +
Hours2003 +
FROM vwSumVolunteerHrs +
WHERE +
Hours2003 < 50
Best Regards,
Randy Peterson
Blue Springs, Missouri
-------- Original Message --------
Subject: [RBASE-L] - RBase 6.5++ Query structure
From: "Duncan Walker" <[EMAIL PROTECTED]>
Date: Sat, July 29, 2006 9:38 pm
To: [email protected] (RBASE-L Mailing List)
I have a table holds the following information
- the volunteers name
- The hours volunteered for the year by each volunteer.
(This info is held for multiple years ie 2001, 2002, 2003 etc).
I am using the Object Manager- Reports and the Where Builder and am trying
to create a query that will display all volunteers who have done less than
50 hours, each year, over a number of years ie 2003 and 2004. I am having a
problem as I'm not very good at these compound queries. I had tried the
following but got back no rows. (year =2003 and totalhours <100) and
(year=2004and totalhours <100).
I would appreciate any help.
Thanks
Duncan
