I have a report built and use the Object Manager - Reports and the Where
Builder to define my query. I wondered if you could help me with the query
using this method versus from the R>
Thanks
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Sami Aaron
Sent: Sunday, July 30, 2006 10:49 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - RE: RBase 6.5++ Query structure
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
