Open the form for editing.
Open the form navigator (menu:Form> Form Navigator).
- The grey boxes on top are just indicators for the date range that is stored in the “Data” table. They belong to the main form “Form_MiniMax”.
- The second main form “Filtering_MainForm” with yellow boxes is linked to the following query:
SELECT * FROM "Filter" WHERE "FID"=1
This filtering form is only for entering criteria values in one particular record (where FID=1), therefore we disallow everything except “Allow modification”. The user must not tab into any other record and we do not need any navigation bar for a single record.
- The filtering form has a “Filtered_SubForm” (green grid) which is linked to the parameter query “qFiltered_Input” which goes like this:
SELECT "PID", "CID", "D", "V", "ID" FROM "Data"
WHERE ( "PID" = :paramP OR :paramP IS NULL )
AND ( "CID" = :paramC OR :paramC IS NULL )
AND ( "D" >= :paramD1 OR :paramD1 IS NULL )
AND ( "D" <= :paramD2 OR :paramD2 IS NULL )
The parameters paramP, paramC, paramD1 and paramD2 are substituted by their respective parent fields INT1,INT2,D1 and D2 in the parent form. Because this query selects everything from a single table “Data”, including the primary key, the resulting record set is editable. Two listboxes in the grid represent the person names and the category names for the corresponding person IDs and category IDs.
- The filtered subform has its own subform “Aggregate_Form” with white boxes. This form is linked to query “qFiltered_Aggregate” which goes like this:
SELECT SUM( "Value" ) "Sum", COUNT( "Value" ) "Count", MIN( "Value" ) "Min", MAX( "Value" ) "Max", AVG( "Value" ) "Average"
FROM "qFiltered"
and “qFiltered” goes like this:
SELECT "D"."D" AS "Date", "P"."N" AS "Person Name", "C"."N" AS "Category", "D"."V" AS "Value", "D"."V" * 0.15 AS "VAT"
FROM "Categories" AS "C", "Data" AS "D", "Persons" AS "P", "Filter" AS "F"
WHERE "C"."ID" = "D"."CID" AND "P"."ID" = "D"."PID"
AND "F"."FID" = 1
AND ( "D"."PID" = "F"."INT1" OR "F"."INT1" IS NULL )
AND ( "D"."CID" = "F"."INT2" OR "F"."INT2" IS NULL )
AND ( "D"."D" >= "F"."D1" OR "F"."D1" IS NULL )
AND ( "D"."D" <= "F"."D2" OR "F"."D2" IS NULL )
which merges the data table, the filter criteria in the filter table (where FID=1) and the names from the categories and persons in one query. This record set merged from all tables is not editable like the parameter query from a single table. It is used for the calculated aggregation (sum, count averages) and you may create a report from this query showing the exact same records as the green subform grid in a printable layout.
The key to databases is SQL not Basic.
P.S. One more thing regarding the 2 push buttons (see form navigator window):
btnCancel belongs to the yellow filtering form and resets the form to its stored state before editing.
btnRefresh (the OK button) belongs to the green filtered form. When you click it it takes away the focus from the filtering form. A modified form losing the focus is stored automatically. Then the button refreshes the filtered form it belongs to.
