Dear Bruce,
Thanks a lot for your extensive answer. Your assumption is correct: SALES is computed by an „upstream“ transaction table. Probably my description of the SALES table was ambiguous. It’s a very good hint and actually good practice to keep same things together and not to mix it up with other stuff. So when I generate a new table *salesAnnual *that only contains the aggregate totals per year as proposed by you it would look something like this: *salesAnnual* *------------------------------ CustID INT 2016 CURRENCY 2017 CURRENCY 2018 CURRENCY * *2019 CURRENCY* * 2020 CURRENCY * *2021 CURRENCY * By this I am very flexible just to add another year (column) or more customers (rows). BTW: I abstained from using AUTONUM fields, as this may cause unexpected problems in R:Base. The report I intend to generate would look something like this: *Annual sales volumes per customer* *Cust# Name 2016 2018 2020 2021 ----------------------------------------------- 1023 Benz 3,200 7,100 500 3,500 1077 Zotter AG 1,200 2,500 1093 Hayek 300 200 TOTAL 3,500 7,100 1,900 6,000 * When I use the CHOOSE command to allow the user to select from available years for his report I still have the problem that I only want to show the years for selection but not the CustID. So I assume I still need a WHERE clause? *CHOOSE vChoose FROM #COLUMNS IN salesAnnual + WHERE ???? CHKBOX 3 + TITLE "Select 3 previous years for comparison" + CAPTION "Annual Sales Volumes" + LINES 5 FORMATTED * Thanks again Rolf rbytes schrieb am Freitag, 2. April 2021 um 20:55:00 UTC+2: > In the SELECT statement, the SUM function should read: > *(SUM(monthTotal))* > > B > > ------ Original Message ------ > From: "Bruce Chitiea" <[email protected]> > To: "[email protected]" <[email protected]> > Sent: 4/2/2021 11:52:04 AM > Subject: Re: [RBASE-L] - WHERE clause in CHOOSE command > > Rolf: > > The examples below are best viewed as formatted with the monospaced > COURIER NEW font. > > If I understand your question, each month of your SALES table contains the > total sales for that month. I presume then that SALES is calculated from > transaction detail contained in an "upstream" table. > > Your CHOOSE challenge may be better served, by re-structuring your SALES > table as SALESMONTHLY: > > *salesMonthly* > *-------------------* > *salesMonthlyID INT PK AUTONUM* > *salesYear TEXT(4)* > *month_int INT* > *month_txt TEXT(3)* > *monthTotal CURRENCY* > > There is no annual total in this table. Rather, annual sales totals are > stored within a second table: SALESANNUAL, calculated from the data in > SALESMONTHLY: > > *salesAnnual* > *------------------------------* > *salesAnnualID INT PK AUTONUM* > *salesYear TEXT(4)* > *yearTotal CURRENCY* > > For simplicity in the example, you may calculate and insert annual totals > in one step for ALL years (you can narrow it down to one year with a simple > WHERE clause): > > *INSERT INTO salesAnnual (salesYear, yearTotal) +* > *SELECT salesYear, SUM(monthTotal)) +* > * FROM salesMonthly +* > * GROUP BY salesYear* > > So, imagine that you have complete monthly data for the years 2017 through > 2020. The salesAnnual table would look something like this: > > *salesAnnual* > *salesAnnualID salesYear yearTotal* > *---------------------------------* > *201 2017 $108,900* > *202 2018 $185,300* > *203 2019 $260,000* > *204 2020 $ 45,000* > > Does that give you what you need for your CHOOSE command? > > Best, Bruce > > Bruce A. Chitiea | SafeSectors, Inc. > 112 Harvard Ave #272 | Claremont CA 91711-4716 | USA > *[email protected]* | +011 (909) 238-9012 c | +011 (909) 912-8678 f > > ------ Original Message ------ > From: "'U56.S11+G43' via RBASE-L" <[email protected]> > To: "RBASE-L" <[email protected]> > Sent: 4/2/2021 9:14:42 AM > Subject: [RBASE-L] - WHERE clause in CHOOSE command > > > > Maybe anybody has an idea how to frame the WHERE command to select only > certain columns from a table? > > I am having a table SALES with kind of the following columns: > 1. CustomerID > 2. Jan > 3. Feb > 4. Mar > .... > 13. Dec > 14. Total_2018 > 15. Total_2019 > 16. Total_2020 > 17. Total_2021 > > A new column (Total_...) will be added for each new year, so I can keep > sales history for many years. > > Then I have a report that prints the total of the current year (e.g. > Total_2021) with three more "Total-Columns" allowing comparisons on > different years. > > As columns will be added each year the report should dynamically adapt and > show totals for new years for selection as they appear. > > Now, how do I build the WHERE clause in the CHOOSE command that allows the > user do select two different columns (of former years), but of course only > from those that contain annual totals "Total_*"? > > CHOOSE vChoose FROM #COLUMNS IN SALES WHERE ???? CHKBOX 3 + > TITLE "Select 3 previous years for comparison" + > CAPTION "Annual Sales Volumes" + > LINES 5 FORMATTED > > Thanks very much for your ideas > > Rolf Bullinger > > -- > For group guidelines, visit > http://www.rbase.com/support/usersgroup_guidelines.php > --- > You received this message because you are subscribed to the Google Groups > "RBASE-L" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/rbase-l/d27f0282-c8e9-44db-a289-f68fb68eb553n%40googlegroups.com > > <https://groups.google.com/d/msgid/rbase-l/d27f0282-c8e9-44db-a289-f68fb68eb553n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > -- > For group guidelines, visit > http://www.rbase.com/support/usersgroup_guidelines.php > --- > You received this message because you are subscribed to the Google Groups > "RBASE-L" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/rbase-l/em4c34f5ff-eef9-49cf-bd6b-3685f3716e71%40pathfinder > > <https://groups.google.com/d/msgid/rbase-l/em4c34f5ff-eef9-49cf-bd6b-3685f3716e71%40pathfinder?utm_medium=email&utm_source=footer> > . > -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/bff7ce48-e684-4e65-9ce3-6304d8f79014n%40googlegroups.com.
