Query not calculating correctly

English
#1

I have created a relationship between 2 tables, and then added them to a new query.
When I add the fields from 1 table and enter the formula for adding, multiplying etc, the query runs great and the calculations are correct.
Then when I do the same for the 2nd table, the calculations are not correct.
In both tables, the field names are the same, and the formula for the calculations are the same, but the 2nd table / query gets it wrong!

Have I done something wrong or not possible, as I can’t understand why this has happened.
thanks

#2

Nobody can answer that as you didn’t tell what you actually did. Best upload and attach (redacted) sample files to your question, one working and another not working. Or at least describe exactly step by step how to reproduce your scenario.

#3

@erAck,
when you say redacted files, I only have the 2 query files and the joined one.
I’m not bothered about the information as it only applies to me, and no good to anyone else.
Do mean the actual dBase file with the tables etc
Otherwise can you please specify

#4

EONUtilities.odb (34.0 KB)

#5

Have downloaded the database file. There was only one query for “Electric”. So I created the second query for “Gas”. Right? Have seen differences in format and fields. Some fields in table for “Gas” will contain 3 decimal places.

Now: Which values aren’t correct? Have had a look at the first row of both and “Total Cost” and didn’t find an error.

#6

@RobertG,
First off, apologies if something is missing.
That is strange as there should be 3 queries:
Electric, Gas and FuelCost
When I run the FuelCost in edit mode, first there is wrong answer for the Total standing Charge for the Gas, as it is the same figure as for the Electric.
Also for the Gas query, the figure for Cost after EPG is wrong. The Gas cost is £187.68, then minus £56.63, it should be £131.05, but the query says £121.83.

Again if both queries are run separately, the figures are correct.

thanks

#7

I’ll try again, and the file size is 37.9kb
EONUtilities.odb (37.0 KB)

#8

You are connecting two queries with the same field names. Don’t know if this is a buggy behavior in the GUI, but when looking at the SQL-code I see:
( "Gas Cost" ) - ( "EPG" ) AS "Cost After EPG"
Which table should Base use for “EPG”? Why is there a second field with the same Alias “Cost After EPG”?.
( "Gas Cost" ) - ( "EONGas"."EPG" ) AS "Cost After EPG Gas"
This will show the result for “Gas”, because the field “Gas Cost” is only available in the table “EONGas”. “EPG” will be read from “EONGas”, not from the first available table “EONElectric”. You have to look for the right code in every calculation, because the table name is missing there.

So: Switch from GUI-view in the query editor to SQL view. Write for every field "Tablename"."Fieldname" to get the right content from the tables.

#9

I read somewhere that when joining queries ? the fieldnames had to be the same, but maybe I misread that.
Do you mean to change every field name in SQL view to “Tablename”.Fieldname"
So instead of EONElectric.ID1, change to Tablename.Fieldname etc ??
That would upset the syntax for the calculations??
Sorry if I’m not with you exactly

#10

I downloaded your original upload and yes it contains just the 1 query. It was easy to add the gas query.
the attachment is your db plus one table “tCalendar” and 4 queries.
when we compose queries we often have to join tables, usually we join the primary key of 1 table with a foreign key in another table.
no relationships have been defined but the use of a calendar table enables us to view data simultaneously for both gas and electric, we join by year and month.
the queries “qElectric” and “qGas” are your queries.
“qUnion” shows all data for both gas and electric sorted by commodity then date, not particularly useful in my opinion.
.
“qTotalsBoth” uses the calendar table and shows the total cost of both commodities, order is by year then month.
this is the query which shows how to join your tables.
you can add as many fields as you wish.
you have 2 gas bills for Sep 2022, this may be an error? if not then the totals should be summed.
.
note: all queries have been saved in direct mode. “qUnion” requires direct mode.
to enable/disable direct mode: right click the query, select Edit, hit the icon ‘Run SQL command directly’
Utilities1.odb (35.0 KB)

#11

[quote=“avvy651, post:9, topic:85877”]
I read somewhere that when joining queries ? the fieldnames had to be the same[/quote]
If fieldnames are the same you could join two queries in MySQL with

… FROM "EONElectric" JOIN "EONGas" USING( "ID1")

But this doesn’t work with internal HSQLDB. So: Field names must be named different in one table. If you are using different tables and same field names in different tables - how should the database knew which field should be chosen?

No, “EONElectric”.“ID1” is a combination of tablename and field name. But in every calculated field the table names are missing. So Base has to decide which field it want to use. Base decides this by this entry:

… FROM "EONElectric", "EONGas" …

If there is no table name first try to find it in “EONElectric”. If it isn’t there try “EONGas”.
Now have a look to a part of the code of your query:

… "EONGas"."EPG", 
( "Gas Cost" ) - ( "EPG" ) AS "Cost After EPG", 
( "No Days" ) * ( "Standing Charge" ) / 100 AS "Total Standing Charge" …

All fields, which appear directly without calculation have got a table name like "EONGas"."EPG". All fields in a calculation won’t have a table name. So Base takes the first table in the code, if there are fields with the same name in both tables.
Base takes ( "Gas Cost" ) - ( "EONElectric"."EPG" ), but you don’t want it. You need ( "Gas Cost" ) - ( "EONGas"."EPG" ) instead.
Base takes ( "EONElectric"."No Days" ) * ( "EONElectric"."Standing Charge" ), but you don’t want it. You need ( "EONGas"."No Days" ) * ( "EONGas"."Standing Charge" ) instead.

And if all this has been corrected it calculates what you want, but the field names are the same for many fields. So you wont know which “EPG”, “Cost after EPG” … is shown. You could only decide it by the position of the field in the query. So the better way would be to write an alias like …"EPG" AS "EPG_Gas"…

#12

@cpb
thank you for the work you have done for me as I would not have known how to do it.
Yes there are 2 bill for Sept 2022, each for 15 days; thanks, I’ll sum the 2 totals.

I don’t know what ‘direct mode’ is, so could you explain.
I notice that there is only sql view for the edit selection and not the design view.
I have tried to add another line to the qUnion / Electric section, but it give an error about connectivity.

( “Electric Cost” ) - ( “EPG” ) + ( “No Days” ) * ( “Standing Charge” ) / 100 + ( ( “Electric Cost” ) - ( “EPG” ) + ( “No Days” ) * ( “Standing Charge” ) / 100 ) * 0.05 AS "Electric Total Cost "

I am guessing the error is something tom do with syntax, but I don’t know where to start in sql.

Thanks again

#13

@RobertG
Thank you for a very good explanation about field names etc as I am more or less a newbie with dBase.
As a precaution, I have prefixed every field name where calculations are, with the name of the table. Now the calculations are exactly right.
Thanks again.

1 Like
#14

@avvy651,

direct mode
.
google ‘sql UNION’ for info on the UNION operator.
.
UNION:
the column count and type must be equal for each SELECT statement.
the sql you wish to add can be refined as:
("Electric Cost" - "EPG" + "No Days" * "Standing Charge" / 100) * 1.05 AS "Total-EPG"
and
("Gas Cost" - "EPG" + "No Days" * "Standing Charge" / 100) * 1.05
add these lines to ‘qUnion’ before the relevant FROM clause, electric to electric, gas to gas.
don’t forget to append the previous line with a comma.
.
gas and electricity are totally different commodities.
the bills we receive are similar, date, units used, unit cost, standing charge etc.
there are a lot of columns to display and it seems impractical to try and show both commodities in the same query/report, it’s best to keep them separate.
you only need to show both when viewing the monthly spend i.e. gas, electric, gas + electric.
‘qTotalsBoth’ is the query that does that.
here’s the updated code which sums the individual costs and shows the combined total.

--qTotalsBoth
select
	to_char(c."Date", 'MON YYYY') "Date",
	e."ElectricTotal",
	g."GasTotal",
	coalesce(e."ElectricTotal", 0) + coalesce(g."GasTotal", 0) "CombinedTotal"
from
(
	select
		"Date", year("Date") || right('0' || month("Date"), 2) dte
	from "tCalendar"
	where "Date" <= current_date
) c
left join
(
	select
		year("Date") || right('0' || month("Date"), 2) dte,
		sum(("Electric Cost" + "No Days" * "Standing Charge" / 100) * 1.05) "ElectricTotal"
	from "EONElectric"
	group by dte
) e
	on c.dte = e.dte
left join
(
	select
		year("Date") || right('0' || month("Date"), 2) dte,
		sum(("Gas Cost" + "No Days" * "Standing Charge" / 100) * 1.05) "GasTotal"
	from "EONGas"
	group by dte
) g
	on c.dte = g.dte
where "ElectricTotal" > 0 or "GasTotal" > 0
order by c."Date"
#15

@cpb
thanks for the explanation, but I will google ‘sql UNION’
Also thanks for the updated sql code to give the sum of both utilities.

Is there a way I can add the same line to both the qUnion and qTotalsBoth queries.
I tried it but both gave the same error;
column count does not match in statement [-qUnion

thanks
Thanks

#16

It is not simply “add a line”. As a result-set is a table the fields must fit as the error reads.

#17

I have added the query “qTotalsBoth2”, same as “qTotalsBoth” plus requested field.
it has 7 columns which enables you to verify accuracy.
when you are happy then you may wish to restrict output.
you probably only need to display the columns “Date”, “ElectricTotal”, “GasTotal”, “CombinedTotalLessEPGTotals”.
to hide a column you can prefix a line of SQL using a valid comment string i.e. ‘–’ (2 dashes).
e.g. to hide the column “EPGElectricTotal”:
-- e."EPGElectricTotal",
.
remember:
we are dealing with calculated values which may provoke rounding errors i.e. 1.05 will be rounded up to 1.10.
tax @ 5% has been added to EPG totals.
.
qUnion:
added the lines:
("Electric Cost" - "EPG" + "No Days" * "Standing Charge" / 100) * 1.05 "TotalLessEPG"
and
("Gas Cost" - "EPG" + "No Days" * "Standing Charge" / 100) * 1.05
.
I have uploaded the attachment because the code over-fills the comment limit.
Utilities2.odb (36.3 KB)

#18

@Wanderer
As it is about 25 years since I last used sql, I have forgotten quite a bit since then, and I don’t know what you mean by this:
‘As a result-set is a table the fields must fit as the error reads.’

#19

@cpb
thank you for doing that, and it is just what I want now.
Could I just ask a couple of things:
First , in sql, how do you get the text formatted in a clear order for each item / field. IE going vertically in order down the page like it is in the queries you created.
I tried it with both an existing and a new query but it didn’t save even tho’ I did save it, and reverted back to horizontal lines.

Also, when you created the queries, there is no design view mode and it is greyed out in the sql window. Can design view be turned back on, or would that upset something.

Thanks

#20

Look at the last line givven by @cpb where he added the sequence to obey:
order by c."Date"
.
You can add further fields there.
.
25 years ago it was mayby not so easy, but try google with

sql union sorting

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.