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
