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/embbef9fb1-3bc2-4f73-bbe4-53d8594934e6%40pathfinder.
