[RBG7-L] - Re: Monthly report

[Neil Little]

Title: Message

Thank you. I have tried a temporary view but it doesn't work either. Indexing is something I have not tried. Perhaps, that is where the problem is and why it won't combine and sum the each product. I'll have to read up on indexing to find out what it is and how it works. Thanks for your help.

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
Sent: Wednesday, September 15, 2004 18:07
To: RBG7-L Mailing List
Subject: [RBG7-L] - Re: Monthly report

Neil,

Some others have given you Select statements for your results.  However,  you can also use a view to base your report on. You should create a view from your three tables.  Linking columns would be the common column for Master and Detail along with the common column for Detail and Product.  (Make sure your linking columns are indexed)  Your view can have computed columns (such as Qty * Price) so making sums is even easier.

With this view, you can then create a report based on the view that has many kinds of summaries and totals rather easily.  Selections from date ranges and selections from any other field are easily made.  You  can make "Break Points" by customer, by product, etc. that gives you all the sums and totals you have indicated with DBCalc functions.

I may have missed something, but I think the answer would lie in a view driven report.

-------------- Original message --------------

I have been trying, for several months, to figure out a way to retrieve data from tables and put it into a monthly report. I have 3 tables--- transmaster, transdetail, and product. I need to find a way to recall the data based on the date in the transmaster and make a report using that information. The report needs to contain the product, price, qantity of each product and the total product and total price. I need to make the report in the following format;

Productname Quantity Listprice Total

Flow test 1 $50.00 $50.00

FDC Cap 5 $200.00 $1000.00

ETC.......

I have tried using SELECT AS, AS SELECT and GROUP BY statements and nothing works. Using a crosstab does not yield the desired result. I can recall the required files by using an RMD or EEP and creating a temporary table. I just can't get the pruduct quantity or total to sum or to sum properly. Any suggestions would be appreciated. I can send my program if needed....it's small since it is still in the development stage. Thanks in advance.

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.760 / Virus Database: 509 - Release Date: 9/10/2004

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.760 / Virus Database: 509 - Release Date: 9/10/2004

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.760 / Virus Database: 509 - Release Date: 9/10/2004