Bruce, Ich sehe Du sprichst Deutsch oder hast mindestens einen griffbereiten Übersetzer.
With Hayek my mind was more for August Friedrich von Hayek, but you just inspire my imagination ;-) Please take you time. I am glad at all to get any valuable hints and you will have more important business than this. Thanks again, Rolf rbytes schrieb am Samstag, 3. April 2021 um 17:45:37 UTC+2: > Herr Rolf: > > I may have time to evaluate a response this afternoon. In the meantime, an > important question: Sie haben "Hayek" geschrieben. Denken Sie dabei an > Freidrich oder an Salma? :) > > By the way, the time is now 0845 in Los Angeles. > > 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/3/2021 4:14:14 AM > Subject: Re: Re[2]: [RBASE-L] - WHERE clause in CHOOSE command | Errata > > > > 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 > > <https://groups.google.com/d/msgid/rbase-l/bff7ce48-e684-4e65-9ce3-6304d8f79014n%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/71ced837-52ae-47d7-86f6-2277552d9ee1n%40googlegroups.com.
