Sounds good! I am in the process of changing some reports to use the temp tables and views as you mentioned rather than the old way.
Jim ________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Tuesday, June 08, 2010 5:21 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Multi column report The main ItemMaster table has the category, so the category is available by linking the sales detail table to it by the item number. I went ahead and finished the report. It may not be the most elegant but it works and is very fast. 1-Created a temp view that sums all the sales by item number. 2-Created a temp table for each category that includes an autonum column, itemnumber column and dollars sold column. 3-Inserted the records for each category into the respective tables from summing view above and ordered the insert by Dollarssold Descending This made (3) tables having the category items and summed sales by item number. Since they were inserted in Decending sales order, the autonum value of 1 for each table was the highest sales, etc. Altered the temp table created above for the category that had the 160 items by adding CatBItem, CatBSales, CatCItem and CatCSales columns. (Used the category table that would always have the most item numbers in it) Updated this temp table by using the autonum columns. For example.. Update CatATable set CatBItem = t2.ItemNumber, CatBSales = t2.DollarsSold from CatATable t1, CatBTable t2 where t1.Rank = t2.Rank (Rank being the autonum column) Update CatATable set CatCItem = t2.ItemNumber, CatCSales = t2.DollarsSold from CatATable t1, CatCTable t2 where t1.Rank = t2.Rank (Rank being the autonum column) I now have one table that the report is easily based upon. By doing the update from the autonum column, the first record is the highest sales for all three categories, the second is next, etc. etc. It was very few lines of code and runs very fast. Will handle any number of items and nothing is hard coded. Should work now and in the future. All views and tables are temporary and work in multiuser conditions. Thanks, -Bob ----- Original Message ----- From: "Jim Belisle" <[email protected]> To: "RBASE-L Mailing List" <[email protected]> Sent: Tuesday, June 8, 2010 4:37:48 PM GMT -06:00 US/Canada Central Subject: [RBASE-L] - RE: Multi column report Bob, In the detail section, is there a category field associated with each of these parts? Jim ________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Tuesday, June 08, 2010 3:59 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Multi column report I am drawing a blank and quite truthfully have not had a lot of time to think about this request, so any thoughts appreciated. I have a table that holds sales detail information. The customer is requesting a report that shows total sales in decending order for each category. Easy enough, but they want (3) columns, one each for a category of product, showing sales in decending order. So the report would need to look like this.... (a simplified example here) -------------------------------------------------------- Sales by Category -------------------------------------------------------- Category A Category B Cateory C -------------------------------------------------------- ABC $5000 123 $3320 A00 $1500 XYZ $4100 321 $3005 A21 $999 XAZ $2850 999 $1852 A99 $732 etc. etc.. (The 3 digit values representing part numbers here) Note that all this data is in one table. The columns involved would be PartNo., DollarsSold and Category. There are approximately 160 parts in Category A, 50 in Category B and only 22 in Category C. So there would not be values for B and C in the lower rows. Any thoughts on an elegant solution? Thanks, -Bob
