Building a OpenOffice Base query in design view

[zipster1967]

I am trying to build a query in openoffice base using multiple tables and cannot figure out how to enter the where clause in the query design view. I have several talbes in the query related by ID within each table for text fields in other tables. For example I have a table called Expense_Category with the fields Expense_Category_Label which is a text field and the field Expense_Category_ID field which is an integer field and the primary key for the table. I also have a table called Expense_Details with the following fields: Expense_Details_ID which is the primary key and is an integer field, Expense_Detai_Label which is a text field,Expense_Category_ID which is an integer field which I want to use to get the Expense_Category_Label field from the Expense_Category Table. So the query I want to construct would take the Expense_Category_label from the Expense_Category Table and allow me to create a new record in the Expense_Detail table that would have the Expense_Category_ID field of the selected Expense_Category_Labels matching Expense_Category_ID saved in the record under the Expense_Category_ID field of the Expense_Detail table. I am not sure if anyone is having trouble following that explanation so I will give an example.
Say I want to have a form with a Dropdown box that displays the Category Label (I.E. Housing) from the Expense_Category table and saves the corresponding Epense_Category_ID (I.E. 1) from the Expense_Category table in the field Expense_Category_ID in the Expense_Detail table. If anyone can explain how I go about doing this your help would be much appreciated. Do I do this lookup in the form I create or do I do this lookup in the query the form should be based on or do I base the form on one of the involved tables? Please help.

[Villeroy]

Dropdown boxes are belong to forms. There are combo boxes which are nothing more than text fields with auto-complete. A combo accepts any text input for its underlying table column even if the value is not in the auto-complete list.
Then there are 2-column list boxes which let the form user select a list item from a record set in order to put the corresponding primary key into a foreign key field of the form's record set. This is what you are asking for. A typical query to fill a list box with data is:
SELECT "Name Field", "ID" FROM "Table" ORDER BY "Name Field" ASC
where the ID field refers to the table's primary key (typically auto-integer) and sorted name field constitutes the visible list items which could be a concatenation of many fields as in
SELECT "Surname" || ', ' || "Forename" || ', ' || "Day of Birth" AS "Visible", "ID" FROM "Table" ORDER BY "Visible" ASC
It is a good idea to store such list box queries in the queries section and then point one or more list boxes in forms to that query definition (content type=query, content=name of query). You can also store the query string directly in the list box (content type=sql, content=SELECT ... FROM ...)

In the section of Base tutorials and in the "Examples" section therein you will find many tutorials and example files with one-to-many relations, many-to-many relations and their input forms. The form wizard can hardly handle any relations at all except for directly related records in one subform.

[zipster1967]

I am familiar with creating an SQL query but need to figure out how to create the criteria in the design view in open office How do I enter the criteria in the design view to get the correct syntax in the SQL statement?
For instance I have to create a form that allows the user to enter a new expense payment in a form that I believe should be created from a query of my Expense type table which would contain a list of expense types such as Housing, Transportation, Living, Entertainment, Groceries, Insurance, and Miscellaneous those items would be retrieved from the Expense Types table and the Payment table would contain the index number from the Expense type ID field in the Expense type table. There would also be an Expense list table which would contain the list of companies that would receive the payments such as The cable company, the Phone company, garbage collection company, etc. If the type or the expense list did not already contain a certain value that could be entered as a new value and have that new value added to their respective table. I believe I would need a sub form for each of these entries if the combo box control did not allow this to be done in the main form. But I do not know how to go about this in base as I am new to using base. That is what I am trying to figure out how to do. Any suggestions on how ti do this with examples would be greatly appreciated.

[Villeroy]

We have a tutorial section on this forum.
The correct SQL syntax uses double-quotes around names. Everything else follows the SQL standard.
Data definition and data manipulation queries are entered into the "command line" Tools>SQL... where they are sent to your database engine.
Forms and queries use nothing but SELECT.
In direct SQL mode (pass-through) you can use the syntax of your database engine.
The record sets to be used by a form, subform, list box or combo box can be stored in separate queries which can be referenced in forms (content type "Query" and the query name as content). You may also enter SQL directly into the property window of your form, subform, list or combo box. In the latter case you use content type "SQL" and the SQL string as content. The little [...] button calls the query designer.

[Arineckaig]

I am trying to build a query in openoffice base using multiple tables and cannot figure out how to enter the where clause in the query design view.
..............................
How do I enter the criteria in the design view to get the correct syntax in the SQL statement?

The Base Query Design View has its limitations but if care is exercised it can sometimes provide quick and (over)simple answers. Specifically a WHERE clause linking two tables can be added by using the top frame in the Design View: for example, with the mouse drag from the Foreign Key in one table to the Primary Key in the referenced table. The resulting relationship line will have added a WHERE clause to the query SQL.

My reply is directed solely to the specific questions quoted above. The method has its limitations and the resulting SQL should always be checked to ensure the result is the one required: click on the 9th icon on the Design View toolbar to open the frame with the SQL view of the query. More generally Base is quite primitive: it requires an understanding of SQL. More importantly, as indicated by Villeroy, there are the wider issues that need to be considered.