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 <tel:(909)%20238-9012> c
| +011 (909) 912-8678 <tel:(909)%20912-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/em6768f015-cb13-431a-a477-7952ea4291a8%40pathfinder.
