No need for the second view, Jim. The grouping can be done in the report by locating the SUM of BilledAmt,PaidAmt,CanceledAmt in the IMonth break footer. No detail lines in the report.
Regards, Stephen Markson ForenSys The Forensic Systems Group www.ForenSys.ca 416 512 6950 > -----Original Message----- > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of James > Bentley > Sent: October 9, 2008 10:34 PM > To: RBASE-L Mailing List > Subject: [RBASE-L] - RE: Group By Problems > > Jan, > Stephen has a good start in solving your problem. I would use two views. > The first is similar to the one outlined below. > CREATE VIEW NewV (IMonth,BilledAmt,PaidAmt,CanceledAmt) AS + > SELECT (IMON(InvDate)),InvAmt,0,0 FROM Table WHERE (it's a bill) + > UNION ALL + > SELECT (IMON(PayDate)),0,PAmt,0 FROM Table WHERE (it's a payment) + > UNION ALL + > SELECT (IMON(PayDate)),0,0,InvAmt FROM Table WHERE (it's a canceled) > This view give one row for a whole bunch of items; i.e., many items for > each month. > > The second view would be > CREATE VIEW RptVue (DuesMonth, BillAmt, PaidAmt, LostAmt) AS SELECT + > IMonth,BilledAmt,PaidAmt,CanceledAmt FROM NewV GROUP BY IMonth > You use this view to produce your view > > > Jim Bentley > American Celiac Society > [EMAIL PROTECTED] > tel: 1-504-737-3293 > > > > ----- Original Message ---- > From: Stephen Markson <[EMAIL PROTECTED]> > To: RBASE-L Mailing List <[email protected]> > Sent: Thursday, October 9, 2008 1:55:22 PM > Subject: [RBASE-L] - RE: Group By Problems > > Try creating a view that does not GROUP BY and achieve the grouping by > placing totals in a break footer. Break on month. Use the WHERE clause in > the PRINT command to restrict to whatever year you're looking at. Use > UNION > in the view definition to separate billed, paid and cancelled: > > CREATE VIEW NewV (TranDate,IMonth,BilledAmt,PaidAmt,CanceledAmt) AS + > SELECT InvDate,(IMON(InvDate)),InvAmt,0,0 FROM Table WHERE (it's a bill) + > UNION + > SELECT PayDate,(IMON(PayDate)),0,PAmt,0 FROM Table WHERE (it's a payment) > + > UNION + > SELECT PayDate,(IMON(PayDate)),0,0,InvAmt FROM Table WHERE (it's a > canceled) > > Set up a break on TranMonth. > PRINT TheReport WHERE TranDate BETWEEN StartDate AND EndDate. > > TheReport has a break on IMonth. > > > > Regards, > > Stephen Markson > ForenSys The Forensic Systems Group > www.ForenSys.ca > 416 512 6950 > > ________________________________________ > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Jan Barley > Sent: October 9, 2008 1:44 PM > To: RBASE-L Mailing List > Subject: [RBASE-L] - RE: Group By Problems > > Buddy, > Its actually more complicatd than that. I am trying to generate a report > to > show whats been billed, paid, and canceled by month. My problem is the > billing amount uses one date (InvDate) and the paid and canceled uses > another (PayDate). I want those figures to all go under the same month. > I > have set up 3 different views - DuesBill, DuesPaid, and DuesLost. > DuesBill is (tmon(InvDate)) and (sum(InvAmt)) > DuesPaid is (tmon(PayDate)) and (sum(PAamt)) > DuesLost is (tmon(PayDate)) and (sum(InvAmt)) > > what I am trying to do is combine all three views into one so I can look > at > these figures by month. The new view would consist of: > > DuesMonth,BillAmt,PaidAmt,LostAmt > > every time I try to combine these views I keep getting the illegal column > specification! > > Any thoughts are appreciated!!! > > Jan > > > > > > No virus found in this incoming message. > Checked by AVG - http://www.avg.com > Version: 8.0.173 / Virus Database: 270.7.5/1704 - Release Date: 10/9/2008 > 4:56 PM
