Query duplicating values

[avvy651]

I have a utility database which I record my usage and bills etc Some time ago, someone on here helped me with a different problem and created a query which would show bills and usage for 2 utilities. that worked fine up until recently. The said base file developed another problem with one of the forms and again someone on here helped me again after I uploaded my base file. When I downloaded the new or corrected base file, 1 of the queries duplicates values, where it didn’t before.
The said query has 2 tables for 2 different utilities, and it is supposed to show the values from or for each utility side by side, but it doesn’t now.
I tried adding the correct table where it says ‘table’ in the left side of the edit window, for each field, but I get an error:
The said query is called: qryFuelcost

SQL Status: HY000
Error code: 1000

at ./dbaccess/source/core/api/SingleSelectQueryComposer.cxx:109

If I remove the table name and have just ‘no table’ there is no such error but it produces duplicate values.

EONUtilities.odb (95.5 KB)

[RobertG]

You query connects to two different data sources. There are no hints in SQL code which of the data source have to be used.

SELECT "EONElectric"."ID1", "EONElectric"."No Days", 
"EONElectric"."KWH Used" * "EONElectric"."Unit Cost" / 100 AS "Electric Cost", 
( ( "EONElectric"."KWH Used" * "EONElectric"."Unit Cost" / 100 ) + ( "EONElectric"."No Days" ) * ( "EONElectric"."Standing Charge" ) / 100 ) * 0.05 AS "Electric VAT", 
( ( "EONElectric"."KWH Used" * "EONElectric"."Unit Cost" / 100 ) + ( "EONElectric"."No Days" ) * ( "EONElectric"."Standing Charge" ) / 100 ) * 0.05 + ( "EONElectric"."KWH Used" * "EONElectric"."Unit Cost" / 100 ) + ( "EONElectric"."No Days" ) * ( "EONElectric"."Standing Charge" ) / 100 AS "Electric  Total", 
"Abfrage1"."KWH Used" * "Abfrage1"."Unit Cost" / 100 AS "Gas Cost", 
( ( "Abfrage1"."KWH Used" * "Abfrage1"."Unit Cost" / 100 ) + ( "Abfrage1"."No Days" ) * ( "Abfrage1"."Standing Charge" ) / 100 ) * 0.05 AS "VAT", 
( ( "Abfrage1"."KWH Used" * "Abfrage1"."Unit Cost" / 100 ) + ( "Abfrage1"."No Days" ) * ( "Abfrage1"."Standing Charge" ) / 100 ) * 0.05 + ( "Abfrage1"."KWH Used" * "Abfrage1"."Unit Cost" / 100 ) + ( "Abfrage1"."No Days" ) * ( "Abfrage1"."Standing Charge" ) / 100 AS "Gas Total Cost", 
"EONElectric"."Date" FROM "Abfrage1", "EONElectric" 
WHERE "Abfrage1"."ID1" = "EONElectric"."ID1"

This query now contains data source and field name. All content the query shows if there is no data source had been the contents of “Abfrage1”.

[avvy651]

Thanks, but I didn’t write the SQL for this query as I left it how it was done sometime ago when it worked. I don’t know how it became to how it was just lately.
But thanks anyway for fixing it. Maybe I could have done what you did, I don’t know or trusted myself to do so…