SQL Query How to write SQL to acomplish different things in a form?

[speakerender1]

I have a form that takes user inputs in the test point and observed columns and finds the percentage difference between the two. I’m trying to display in the Pass/Fail Text box a Pass or a Fail depending on if any of the %differences are higher than the allowed repeatability. I’m having difficulty putting that code into the existing code. I always seem to get syntax errors when adding additional code. It seems like I might need to make multiple queries however the Form itself is created from a query so I’m not sure what the best way to go about adding additional SQL code to the existing.

EDIT:
Sure I added a copy of what I have. Thanks for the help. The existing SQL code I have is included in the picture. The additional code I thought might solve the problem I put in the text below. I was getting basic syntax errors. I was just trying to put my additional SQL code in the one line created in the query design view. The pass fail on the left below the 10 % Difference columns should be calculated by comparing the % difference for each of the ten columns against the user inputted allowed repeatability if anyone of the % difference columns is higher than the allowed repeatability it should display fail, if they are all within the % error it should display pass.

EDIT2: Sorry for any confusion. Below underlined in yellow is the text box where a user can input the allowed repeatability. The red underline is where the pass or fail goes. In order to determine the pass and fail. Each of the numbers within the blue box will be compared individually to the repeatability (yellow). If any one of the numbers in the blue box is higher than the yellow it should display Fail if all are below it should display Pass.

UPDATE TableName
SET PassAF = (CASE WHEN DIFAF1 > AllowRepeatErr THEN “Pass” ELSE "

Copy of the file
Measurementsbackup.odb

[Ratslinger]

Hello,

Can you add a sample .odb? It is difficult to deal with screen shots of SQL which cannot be copied or formatted to be readable. Please be a bit clearer - which is ‘that code’ and which is ‘existing code’? What error(s) are you getting? What are the UPDATE statements at the end of the question? What determines Pass/Fail - see four different areas on screen?

[speakerender1]

The code I intend to add (hopefully it will work)
UPDATE “Measurement Input” ###Table name
SET PassAF = (CASE WHEN DIFAF1 > AllowRepeatErr THEN “Pass” ELSE “Fail” END)

[Ratslinger]

I’m sorry but it makes less sense now. You state:

The pass fail on the left above the 10 columns should be calculated by comparing the % difference for each of the ten columns against the user inputted allowed repeatability.

but what does that mean? “User inputted allowed”? An example of your expected output is best here. Again, there are FOUR different Pass/Fail fields in this screen and many more ‘% difference’ than that and some are not even used.

Note: Just got your latest edits.

[Ratslinger]

Hello,

OK, here is what I see. You are treating a relational database as if it were a flat file. That is, everything is grouped into one long record and space is allocated for data even when it is not going to be used. Two or even three tables should be used here. Possibly 1) Company/Location; 2) Visit information; 3) Test Collection/Results. Especially in #3, tests would occupy only what is needed. It would be flexible allowing 1 to X tests to be done.

Instead of all the individual fields on the form, some could be put in table controls saving space and allowing flexible number of results.

This form is for display purposes only. Input must be on another form. There are many reasons for this with some being the Query where the data comes from & that some data is calculated. To input data & display calculated data will require a lot of changes with possibly some of the table changes mentioned previously.

Now for some actual results. First you are selecting a couple of fields twice - “TP2” & “OBAF2”. Doesn’t create a problem or errors but is unnecessary and can add confusion. Your Pass/Fail result can be determined using a ‘CASE WHEN’ statement. But this requires the result of the calculation be done already or duplicating this calculation. In choosing the former, see the result in posted sample in Query3. This places all your info in a second select - Select From Select - where the Case statement can access the calculated results.

The sample has your form modified only in the fact in now uses Query3 instead of Query1. Result was provided for left section only and record #2 was deleted from table as it was empty. Placed data in ‘AllowRepeatErr’ field to get test results.

Sample - Measurements.odb

[speakerender1]

I’m very new to databases and to Libreoffice Base so I appreciate the Guidance. I think I will start over and take you advise to create multiple tables to represent the data. My goal would be to create a form that could take input and display calculations. Thank you for the example that helps with understanding the SQL formatting.

[Ratslinger]

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

[Ratslinger]

@speakerender1 It would probably help if you viewed the LO documentation for Base & some of the samples. See → LibreOffice Base Handbook. Lower on the page is a tutorial on database creation.

[speakerender1]

I’m looking that handbook over and reading over information on planning out a database. Most resources I see are telling me relational databases and form display don’t really go well together. Im putting the data into different tables and experimenting using subforms to make sure I can query different parts of the form separately. Much more involved than I was hoping as it is more just a program to help a friend update his outdated quatropro database file for his one man business.

[Ratslinger]

Not sure what resources you may be looking at but Base forms are meant to be used with relational databases. Have many such forms with some a bit complex. Base & databases are not something you learn overnight. It can take some time. You don’t Query anything on or from a form. You query a table and results go on a form. It is a large task for someone with no experience with the product.

[speakerender1]

Maybe what I meant was the Form Content was the Query as opposed to being a table. The Query still references the table though. I was trying to separate out the form into different parts or subforms were the content could have different SQL code applied to different parts. Yeah much more complex than I thought. I will keep at it thanks.

[Ratslinger]

Stick with table data on form as much as possible.