Adding a query in Design View

[rob1grie]

If I create a query in Design View, I’m given the option to “Add Table or Query”. I’m not finding any tutorials that describe using a query there instead of a table. Can anyone point me to a good example?

What I’m actually trying to do is query one table with a given set of its columns, adding those columns to a sum of a one-to-many related table’s values that may or may not return NULL. What I’m finding is that if that sum from the related table is NULL, the sum returned by the expression is NULL instead of just the sum of the other fields.

I’m using a MariaDB database connection in Base, but maybe I’m wrong in assuming that the SQL in that Base DB will use MariaDB syntax. I’ve found the MariaDB syntax that will let me do what I need, but it’s using user defined variables … something I’ve definitely not been able to do in Base. So I’m trying to find out if I can use the result of a query as a field in another query … or something like that.

[Ratslinger]

Hello,

In most cases, using a query in a query is simply using the query name instead of the table name:

MyQuery:

Select Name, Address From MyTable

Query from Query:

Select City, Name From CityTable, MyQuery

Now it does not appear this is what your problem is or what is needed. You need to eliminate the NULL fields. You really need to avoid letting numeric fields have a NULL (or empty) value. For your situation in your SQL you can use COALESCE. See → SQL Server COALESCE() Function

Edit:

To add a query in Design View it is like adding a table with correct selector:

You get this dialog from the Add Table or Query icon on the toolbar, F7, or from the menu Insert.

There is little difference in using data from a Table, Query or a View in a Query.

[rob1grie]

Thanks very much! You’ve made it sound so simple, and I’m betting that it is. This is sort of a convoluted project, but I’m getting dBase data into a MariaDB database using an import utility I wrote in Java. What I need to do during import, instead of leaving the value null for a given date/shift, I’ll insert a zero, which will probably eliminate all my problems.
Thanks again!!