Duncan - Try this:
SELECT name FROM volunteers + WHERE name IN + (SELECT name FROM volunteers where year = 2003 AND totalhours < 100) + AND name IN + (SELECT name FROM volunteers where year = 2004 AND totalhours < 100) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Sami Aaron Software Management Specialists 913-915-1971 [EMAIL PROTECTED] -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Duncan Walker Sent: Sunday, July 30, 2006 9:35 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: RBase 6.5++ Query structure Thanks Randy but the data base is very simple name, year, total hours. the printout looks like this name total hours. year, john smith 155 2003 harry brown 95 2003 john smith 100 2004 harry brown 85 2004 john smith 105 2005 harry brown 155 2005 All I want to do is be able to is extract a report that will show those who have performed <100 hours over several consecutive years. If I choose the years 2003 and 2004 ,in the example above the report would show harry brown. ________________________________ From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Sunday, July 30, 2006 3:27 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: RBase 6.5++ Query structure 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 )) 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 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 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. 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
