|
Query Designer gives you the ability to use the simple graphical
interface to construct SQL statements.
 Note: if you
modify a default SQL query, make sure that key column(s) are
included into the fields list. This is required to provide
the edit/delete/view functionality.
To switch between tables use Tables list panel on the left. Note
that all fields marked with a tick in the Output column are added to the SELECT
clause.
What is
supported
Inner joins, outer joins
To add join, click the Add
Table button, select table and then drag and drop any field
from first table to the joined table. To setup join type click the
line between tables, select foreign keys on the Table link properties dialog in both
tables and choose join type.
For more information about using JOIN SQL queries, see
Using JOIN SQL queries.
SQL query:
|
SELECT
carsmodels.id,
carsmodels.model,
carsmodels.make
FROM carsmodels
INNER JOIN carsmake ON carsmodels.make = carsmake.make
|
Note: It's
recommended to use aliases for fields from joined tables to avoid
confusion when two fields from different tables have the same
name.
Calculated fields
To add calculated fields use empty grid below all field
names:
SQL query:
|
SELECT
Make,
Model,
YearOfMake,
Picture,
Price,
color,
zipcode,
features,
Price*0.1 AS Discount
FROM carscars
|
WHERE clause
You can add where clause in the Filter column. If you need to add two
or more conditions use Or... columns.
SQL query:
|
SELECT *
FROM carscars
WHERE YearOfMake =2004
|
For more complicated queries wrap the condition by
parentheses:
|
SELECT *
FROM carscars
WHERE ( YearOfMake =2004 OR YearOfMake =2005 )
|
ORDER BY clause
If you'd like to specify default sorting order on the list page
(ascending or descending) select Sort Type in the corresponding column
for necessary fields.
GROUP BY clause
To add GROUP BY clause, click the Group By button and select one of
grouping function in Group
By column.
SQL query:
|
SELECT
Make,
Model,
AVG (YearOfMake)
FROM carscars
GROUP BY Make, Model
ORDER BY Make
|
What is not
supported
|
·
|
Stored procedure calls (Workaround: create
a view that calls stored procedure and use this view as a
datasource)
|
|
·
|
Update/Delete/Insert/Create queries
|
Note: If your
query for some reason doesn't work create view/query in your
database and use this query as a datasource in ASPRunnerPro.
| 1.
|
Run MS Access and create new query. Switch
to SQL view.
|
| 2.
|
Type your SQL query there.
|
| 3.
|
Save this query as qryNewQuery. |
| 4.
|
Run ASPRunnerPro and use
qryNewQuery
as datasource table. |
|
