[Solved] Subtraction in a simple query

[Babayaga]

I thought this was possible but I I'm stumped. I have a query with a simple result set: From 2 tables i get results from a query;

Hole 1 Actual
Hole 1 Par
Hole 2 Actual
Hole 2 Par

My mission is to subtract Hole 1 Actual - Hole 1 Par

I tried entering Hole 1 Par - Hole 1 Actual to no avail. (Hole 1 Par)-(Hole 1 Actual)
Results in an error: Invalid expression, field name '(Hole 1 Par)-(Hole 1 Actual)' does not exist

Any ideas how to make this work, my sql skills are weak. Thanks

[UnklDonald418]

Try "Hole 1 Actual" - "Hole 1 Par"
Including spaces in field and table names will cause all sorts of grief, either eliminate them (Hole1Actual) or replace them with an underscore (Hole_1_Actual)

[keme]

Try "Hole 1 Actual" - "Hole 1 Par"
Including spaces in field and table names will cause all sorts of grief, either eliminate them (Hole1Actual) or replace them with an underscore (Hole_1_Actual)

Very good advice! Make a habit to not use typical separator, language specific or punctuation characters in object naming, even when the software allows it. In the future you may need to work with, perhaps even interface your old system with, some software which doesn't allow (or even misinterpret) those characters.

To have nice headings in your queries you can use the "as" modifier to alter the field name (and column heading) which is output from the query.

[Babayaga]

I tried and get another error. I thought this would be simple but I'm wondering if Base can't do what I want. I can make it work in Excel, but I wanted to try another tool to see what else I can do with the data.

Query Data Sources: tbl_GolfCourse_Actuals, tbl_GolfCourse_Par joined on Course Name

FIELD in query: Hole #1 Actuals - Hole #1 Par
No Table selected in query window
Invalid expression, field name 'Hole #1 Actuals - Hole #1 Par' does not exist.

[Babayaga]

So I took a different approach to try and solve. I want to try and build a table from the querys mentioned above. No the issue is I get an error where running the make table query from the Tools menu SQL. The query Test Scores has the Actual Scores and the Par scores in one data set. My theory was to get the data into a table and see if I can then build query to subtract actual from par...

Any help would be most welcome. Thanks

Here's my sql code;

CREATE TABLE MG
AS
SELECT *
FROM Query_Test_Scores;

Error = Unexpected token AS, requires ( in statement [CREATE TABLE MG
AS]

[UnklDonald418]

What type of database do you have? Look along the bottom of the main Base window. Might be something like Embedded database HSQL database engine.

[Babayaga]

Embedded Database HSQLDB Embedded is what I have...

[UnklDonald418]

With HSQL you need to enclose all but the simplest field and table names in double quotes.
Also verify that the both the fields are the same type, i.e. Integer, Decimal, Numeric or Float. Unlike spreadsheets databases use strong type checking which might explain why the calculation works in Excel but not in Base

CREATE TABLE MG
AS
SELECT *
FROM Query_Test_Scores;

As you found that will not work, however Views appear to be tables but the data is supplied by queries. Right click on Query_Test_Scores and select Create as View
When you save that it will appear in the Tables area of the database and will work much like a table.

[Babayaga]

That did the trick. Learned something new this AM.
Thanks !!!