|
|
Query Designer gives you the ability to use the simple graphical
interface to construct SQL statements.
 Note: if you
modify default SQL query, make sure that key column(s) are included
into fields list. This is required to provide edit/delete/view
functionality.
To switch between tables use Tables list panel on the left.
What is
supported
|
·
|
Inner joins, outer
joins
|
To add join click on 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 on
the line between tables, select foreign keys on the Table link properties dialog in both
tables and choose join type.
More info on 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.
To add calculated fields use empty grid below all field
names:
SQL query:
|
SELECT
id,
Make,
Model,
YearOfMake,
Picture,
Horsepower,
EPACity,
Price,
color,
features,
price*0.1 AS discount
FROM carscars
|
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 condition by brackets:
|
SELECT *
FROM carscars
WHERE ( YearOfMake =2004 OR YearOfMake =2005 )
|
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.
To add GROUP BY clause click Group By button and select one of
groupping function in Group
By column.
SQL query:
|
SELECT
Make,
Model,
AVG (YearOfMake)
FROM carscars
GROUP BY id, 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. |
|
