I will chime in with a different approach. I believe that having to add a new table column every year is not the best approach, it would be best to just add new records to the table without having to change the structure; this is what databases do best.
I would restructure the database to something like this, although there are many ways to do it. CustID Year Month Sales CumYear 101 2020 1 $50.00 $50.00 101 2020 2 $50.00 $100.00 101 2020 3 $50.00 $150.00 101 2020 4 $50.00 $200.00 101 2020 5 $50.00 $250.00 101 2020 6 $50.00 $300.00 101 2020 7 $50.00 $350.00 101 2020 8 $50.00 $400.00 101 2020 9 $50.00 $450.00 101 2020 10 $50.00 $500.00 101 2020 11 $50.00 $550.00 101 2020 12 $50.00 $600.00 101 2021 1 $50.00 $50.00 101 2021 2 $50.00 $100.00 101 2021 3 $50.00 $150.00 101 2021 4 $50.00 $200.00 You don’t even need to have the cumulative columns since it can be generated on the fly very easily. Every month you just add new record that can be easily recalculated and with this information, you can generate all kinds of reports with all kinds of details. Again, this is the approach I would take since it does not need yearly changes to the structure and the table grows vertically (records) rather that horizontally (columns) which is the preferred approach. Javier, Javier Valencia, PE <mailto:[email protected]> [email protected] O: 913-829-0888 C: 913-915-3137 From: 'U56.S11+G43' via RBASE-L <[email protected]> Sent: Friday, April 2, 2021 11:15 AM To: RBASE-L <[email protected]> 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] <mailto:[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/!%26!AAAAAAAAAAAYAAAAAAAAABa4qWuusAlBoskXxE2AV1mChgAAEAAAABioiZ2ncxVIquAVVx8vTYUBAAAAAA%3D%3D%40vtgonline.com.
