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
--- RBASE-L
================================================
TO POST A MESSAGE TO ALL MEMBERS:
Send a plain text email to [email protected]
(Don't use any of these words as your Subject:
INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH,
REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP)
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: INTRO
================================================
TO UNSUBSCRIBE:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: UNSUBSCRIBE
================================================
TO SEARCH ARCHIVES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: SEARCH-n
(where n is the number of days). In the message body,
place any
text to search for.
================================================
