Combining three parametering queries and then combining their totals

[pascale]

Hi, I have three queries coming from the same table

SELECT "ID", "Date", "Dévise" AS "EUR", "Net", "ExchangeR", "Net" * "ExchangeR" AS "EUR Solde" FROM "Paypal" WHERE "Dévise" = 'EUR'

This one is for Euros the others are for GBP and USD. from each there is another query which gives me the SUM at a specified date

SELECT SUM( "EUR Solde" ) FROM "soldeEUR" WHERE "Date" <= :INPUTDATE

all work great, I now wish somehow to combine the three so that I add the date only once and I can SUM the three totals.
I have tried to use one query but this does not work, I would like to use a form if possible, I have tried this but I have to add the date three times and I dont know then how to sum the replies, I have saved each query as a table in view section
Any suggestions welcome
Regards
Paul

Here is a screen shot of the query by @ratslinger

The answers should be (EUR Solde) -66.44, (GBP Solde) 6561.77 & (USD Solde) 68.84

[Ratslinger]

I feel obligated to again state this could be much better with some careful planning instead of piecing items as you go along. The example here is that what you require can be done in a single SQL statement instead of multiple statements creating VIEWs. Embedded HSQL (Base default) is very old and cannot handle many things. With a newer HSQL (used in split DB’s and preferred method over embedded) all these interim steps are unnecessary.

With that said, there are some issues which you allude to but are really not clear on so I’ll base this answer on what it seems you are stating.

It appears you have created three VIEWs - EUR, GBP & USD. With that the names used in following statement are “soldeEUR”, “soldeGBP” and “soldeUSD” with the calculated field in each as “EUR Solde”, “GBP Solde”, “USD Solde” respectively. The following statement gets a total for each and a grand total:

Corrected 9/30/2017:

SELECT "EUR", "GBP", "USD", USD + GBP + EUR TOTAL FROM (SELECT SUM( "EUR Solde" ) "EUR" FROM "soldeEUR" WHERE "Date" <= :INPUTDATE), (SELECT SUM( "GBP Solde" ) "GBP" FROM "soldeGBP" WHERE "Date" <= :INPUTDATE), (SELECT SUM( "USD Solde" ) "USD" FROM "soldeUSD" WHERE "Date" <= :INPUTDATE)

With this you can create a form.

If this answers your question please click on the (upper left area of answer).

[pascale]

Hi @ratslinger, Thank you for your interest, “I feel obligated to again state this could be much better with some careful planning instead of piecing items as you go along.” I’am sorry but I am new to Base, I have read numerous tutorials including “base tutorial” “sheep dog trials” and I am learning as I go along, and I am sorry to say there is probably worse to come.
I have tried your SQL Query but it gives incorrect replies. (I have added an image to the original question.
Regards Paul

[pascale]

Hi @ratslinger, I have looked back at your previous comments and see that you feel the underling problem is the table “Paypal”, I am going to go back to the basics for this table and start again to avoid future problems, any suggestions will warmly welcome.

[Ratslinger]

@pascale First, my apologies for my incorrect statement. Have corrected and re-posted the statement in my answer. It was my fault & didn’t verify totals.

As far as my comments, it’s all part of the learning curve. My best advise here is since this appears to be a business application, keep things as simple as possible and don’t be discouraged by needing to retrace steps to do things differently.

[pascale]

@Ratslinger The updated SQL works great, Thank you. There is never a need to apologize when you are helping somebody. I love learning new things, and I am always ready to listen and eager to learn, Thank you for your kind help
Regards paul