RichardPrewitt
IS-IT--Management
I have a report in Access that uses a query named qaverage that generates an average of completed tasks between two dates and then displays the tasks that are late. It actually uses two queries combined to accomplish this. Here is the SQL for the query qaverage:
Now it does exactly what it is supposed to do but When the user has compelted all of their tasks ontime for the week it displays no records. The data that the query qavg pulls is exactly what I want displayed if there are no late tasks. This query gets the average of completed tasks for each machine. Here is the SQL for the query qavg:
The other query that I using in the qaverage query returns all tasks that are late based on the same specified date range that the average is based on. The SQL for query qbetween is:
This has been a very complicated query to get to work but now that I have it working, I need to make it produce data from the qAvg query even if the rest of the fields in the qaverage query are blank.
Basically the finction that is accomplished by each query:
qavg,
Takes two dates that the user inputs and generates an average of ontime completed tasks for each spot number. So it returns one record for each spot number with the percent of ontime completed task based on the specified dates.
qbetween,
Takes the same two dates as above and pull all tasks that were completed late or are not completed still (late) and pulls all of the records.
qaverage,
Basically combines the two about queries and is used by the report.
I need the information from qavg to display regardless if qbetween returns no records. qavg allways has data as long as their completed tasks for the date range. qbetween can return zero records if all of the tasks are completed on time. I need this instance to still return the data from qavg.
Any help would be greatly appreciated.
Thank you,
-Richard
Code:
SELECT qbetween.actuallast, qbetween.plantnumber, qbetween.seconddate, qbetween.firstdate, qAvg.AvgOfontime, qAvg.spotnumber, qbetween.tensionperm, qbetween.tasknumber, qbetween.intervalnumber, qbetween.taskdescription, qbetween.actualnext
FROM datetable, qbetween INNER JOIN qAvg ON qbetween.spotnumber = qAvg.spotnumber
WHERE (((qbetween.actuallast) Between [datetable].[firstdate] And [datetable].[seconddate]));Now it does exactly what it is supposed to do but When the user has compelted all of their tasks ontime for the week it displays no records. The data that the query qavg pulls is exactly what I want displayed if there are no late tasks. This query gets the average of completed tasks for each machine. Here is the SQL for the query qavg:
Code:
SELECT history.spotnumber, Avg(history.ontime) AS AvgOfontime
FROM history
GROUP BY history.spotnumber;The other query that I using in the qaverage query returns all tasks that are late based on the same specified date range that the average is based on. The SQL for query qbetween is:
Code:
SELECT datetable.firstdate, datetable.seconddate, corptasktable.actuallast, corptasktable.cvsp, tension.spotnumber, tension.plantnumber, corptasktable.tensionperm, corptasktable.tasknumber, corptasktable.intervalnumber, corptasktable.taskdescription, corptasktable.actualnext
FROM datetable, tension INNER JOIN corptasktable ON tension.tensionperm = corptasktable.tensionperm
GROUP BY datetable.firstdate, datetable.seconddate, corptasktable.actuallast, corptasktable.cvsp, tension.spotnumber, tension.plantnumber, corptasktable.tensionperm, corptasktable.tasknumber, corptasktable.intervalnumber, corptasktable.taskdescription, corptasktable.actualnext
HAVING (((corptasktable.cvsp)="S") AND ((corptasktable.actualnext)<=[datetable].[seconddate]));This has been a very complicated query to get to work but now that I have it working, I need to make it produce data from the qAvg query even if the rest of the fields in the qaverage query are blank.
Basically the finction that is accomplished by each query:
qavg,
Takes two dates that the user inputs and generates an average of ontime completed tasks for each spot number. So it returns one record for each spot number with the percent of ontime completed task based on the specified dates.
qbetween,
Takes the same two dates as above and pull all tasks that were completed late or are not completed still (late) and pulls all of the records.
qaverage,
Basically combines the two about queries and is used by the report.
I need the information from qavg to display regardless if qbetween returns no records. qavg allways has data as long as their completed tasks for the date range. qbetween can return zero records if all of the tasks are completed on time. I need this instance to still return the data from qavg.
Any help would be greatly appreciated.
Thank you,
-Richard
