Query within date range

  • Thread starter Thread starter Steven P via AccessMonster.com
  • Start date Start date
S

Steven P via AccessMonster.com

Hello,
I am attempting to query between date ranges and having problems. My overall
query is based on 3 seperate select queries. I am using the average function
in the select queries in an attempt to bring all the records forward for the
final query. The final query is not working as I hoped, the average function
arrives at the same average regardless of date specified. it is probably
doing as it should but I don't know how to do it any other way. I have
attached the SQL, can someone try to help me PLEASE!

SELECT DISTINCT [INPUTS Query].MACHINE, [INPUTS Query].WORKCENTER, Avg(
[AVAILABILITY QUERY].AVAILABILITY) AS AvgOfAVAILABILITY, Avg([PERFORMANCE
QUERY].PERFORMANCE) AS AvgOfPERFORMANCE, Avg([QUALITY QUERY].QUALITY) AS
AvgOfQUALITY, Avg([AVAILABILITY]*[PERFORMANCE]*[QUALITY]) AS OVERALL
FROM (([INPUTS Query] INNER JOIN [AVAILABILITY QUERY] ON [INPUTS Query].
MACHINE = [AVAILABILITY QUERY].MACHINE) INNER JOIN [PERFORMANCE QUERY] ON
[AVAILABILITY QUERY].MACHINE = [PERFORMANCE QUERY].MACHINE) INNER JOIN
[QUALITY QUERY] ON [PERFORMANCE QUERY].MACHINE = [QUALITY QUERY].MACHINE
GROUP BY [INPUTS Query].MACHINE, [INPUTS Query].WORKCENTER, [INPUTS Query].
EmployeeID, [INPUTS Query].[TODAYS DATE]
HAVING ((([INPUTS Query].WORKCENTER)="103-210") AND (([INPUTS Query].
EmployeeID)>0) AND (([INPUTS Query].[TODAYS DATE]) Is Not Null And ([INPUTS
Query].[TODAYS DATE]) Between [START DATE] And [ENDING DATE]));
Thanks,
Steven
 
Disregard,
I added date range criteria to preceeding queries before final query and it
appears it fixed the problem-thanks anyway!!
Steven

Steven said:
Hello,
I am attempting to query between date ranges and having problems. My overall
query is based on 3 seperate select queries. I am using the average function
in the select queries in an attempt to bring all the records forward for the
final query. The final query is not working as I hoped, the average function
arrives at the same average regardless of date specified. it is probably
doing as it should but I don't know how to do it any other way. I have
attached the SQL, can someone try to help me PLEASE!

SELECT DISTINCT [INPUTS Query].MACHINE, [INPUTS Query].WORKCENTER, Avg(
[AVAILABILITY QUERY].AVAILABILITY) AS AvgOfAVAILABILITY, Avg([PERFORMANCE
QUERY].PERFORMANCE) AS AvgOfPERFORMANCE, Avg([QUALITY QUERY].QUALITY) AS
AvgOfQUALITY, Avg([AVAILABILITY]*[PERFORMANCE]*[QUALITY]) AS OVERALL
FROM (([INPUTS Query] INNER JOIN [AVAILABILITY QUERY] ON [INPUTS Query].
MACHINE = [AVAILABILITY QUERY].MACHINE) INNER JOIN [PERFORMANCE QUERY] ON
[AVAILABILITY QUERY].MACHINE = [PERFORMANCE QUERY].MACHINE) INNER JOIN
[QUALITY QUERY] ON [PERFORMANCE QUERY].MACHINE = [QUALITY QUERY].MACHINE
GROUP BY [INPUTS Query].MACHINE, [INPUTS Query].WORKCENTER, [INPUTS Query].
EmployeeID, [INPUTS Query].[TODAYS DATE]
HAVING ((([INPUTS Query].WORKCENTER)="103-210") AND (([INPUTS Query].
EmployeeID)>0) AND (([INPUTS Query].[TODAYS DATE]) Is Not Null And ([INPUTS
Query].[TODAYS DATE]) Between [START DATE] And [ENDING DATE]));
Thanks,
Steven
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date Query 2
2 Identical queries, 1 works...... 3
Help to merge 2 access queries 1
BUILDING UNION QUERY 9
How do you ignore entire tables in an Inner Join Query? 13
minimize repeating query criteria & generating monthly YTD totals 1
Using Between Dates in a Query 4
Query/Date Question 5

Back
Top