|
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
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
products.ProductID,
products.ProductName,
products.SupplierID,
products.CategoryID,
products.UnitPrice,
categories.CategoryName
FROM products
INNER JOIN categories ON
products.CategoryID = categories.CategoryID
Note:
it's recommended to use aliases for fields from joined tables to
avoid confusion when two from different tables have the same name.
To add calculated fields use empty grid below all field names:
SQL query:
Select ProductName,
Quantity,
UnitPrice,
UnitPrice*0.05 as Tax
from Products
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 customers where country='UK'
For more complicated
queries wrap condition by brackets:
select *
from customers where (customertype='A'
or customertype='B')
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 ProductName,
CategoryID,
SUM(UnitPrice)
from Products
GROUP BY ProductID, ProductName, CategoryID
ORDER BY ProductName
What
is not supported
If your query
for some reason doesn't work create view/query in your database and
use this query as a datasource in ASPRunner.
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 ASPRunner
and use qryNewQuery
as datasource table.
|
