(Solved) Form with Multiple Queries
Sun, 04 Jan 2015 - 3:47 PM
I have a form and I am pulling in three different queries. Each query have a date entry for it coded as
:Please_Enter_Date
I was wondering if there was away in the form to have a date entry box and then a submit button so I don’t have to enter the date three times?
Sun, 04 Jan 2015 - 4:38 PM
#2
Would this still work?
My form is using three different queries that use Group by name to do sums on the data. It seems these examples are just filtering down tables and are not doing any math. Would this still work?
Sun, 04 Jan 2015 - 7:15 PM
#3
How to use the filter table
venoom27:
In your queries, you would need to replace the parameter clause…
:Please_Enter_Date
…. with a SELECT sub-clause like….
SELECT "date_choice" FROM "date_chosen" WHERE "id_date_choice" = '0'
We assume that your FILTER TABLE (storing the chosen date) is called date_chosen with columns id_date_choice and date_choice. The first column stores the ID (always ‘0’), the second column stores the date.
Note: If you need more help, please give the full SQL query which includes the “:Please_Enter_Date” parameter.
Sun, 04 Jan 2015 - 10:36 PM
#4
I tired getting it to work
I tired getting it to work but the query just sums the total time and does not limit it to one day. Here is the code for the query that I am trying to change.
SELECT "Employee"."First Name", SUM( "Input"."Coding Errors" ) + SUM( "Input"."Punching Errors" ) + SUM( "Input"."Misc Errors" ) "Total Directing Errors", SUM( "Show"."Hours" ) * .5 "Directing Hours", SUM( "Input"."Coding Errors" + "Input"."Punching Errors" + "Input"."Misc Errors" ) / SUM( "Show"."Hours" ) "Directing Error Rate", SUM( "Input"."Master Hours" ) "Master Hours", SUM( "Input"."Master Errors" ) "Master Errors", SUM( "Input"."Master Errors" ) / SUM( "Input"."Master Hours" ) "Master Control Error Rate" FROM "Input", "Employee", "Show" WHERE "Input"."EmployeeID" = "Employee"."EmployeeID" AND "Input"."ShowID" = "Show"."ID" AND "Input"."Date" = :Please_Enter_Date GROUP BY "Employee"."First Name" ORDER BY "Directing Error Rate" ASC
Thanks for your help
Mon, 05 Jan 2015 - 7:11 AM
#5
Is your FILTER TABLE setup properly?
venoom27:
Have you setup your FILTER TABLE (with table and column names as I suggested)? The SQL clause I gave pre-supposes that.
If so…
SELECT "Employee"."First Name", SUM( "Input"."Coding Errors" ) + SUM( "Input"."Punching Errors" ) + SUM( "Input"."Misc Errors" ) "Total Directing Errors", SUM( "Show"."Hours" ) * .5 "Directing Hours", SUM( "Input"."Coding Errors" + "Input"."Punching Errors" + "Input"."Misc Errors" ) / SUM( "Show"."Hours" ) "Directing Error Rate", SUM( "Input"."Master Hours" ) "Master Hours", SUM( "Input"."Master Errors" ) "Master Errors", SUM( "Input"."Master Errors" ) / SUM( "Input"."Master Hours" ) "Master Control Error Rate" FROM "Input", "Employee", "Show" WHERE "Input"."EmployeeID" = "Employee"."EmployeeID" AND "Input"."ShowID" = "Show"."ID" AND "Input"."Date" = (SELECT "date_choice" FROM "date_chosen" WHERE "id_date_choice" = '0') GROUP BY "Employee"."First Name" ORDER BY "Directing Error Rate" ASC
… should work. Post a screenshot of the FILTER TABLE listing if you are in doubt.
Mon, 05 Jan 2015 - 7:33 AM
#6
That Worked!!
Thanks for your help I was not coping it over directly (SELECT “date_choice” FROM “date_chosen” WHERE “id_date_choice” = ‘0’) I was piecing it together. Wow this has wide applications.
Thanks so much
Yes. You could store your date in a one-line dedicated FILTER TABLE (pick-a-date using the data control), then use that date in your queries (using a sub-clause SELECT) to filter your result-sets by date. Post again if you need any additional guidance.