Query with two time periods

[Jen]

Hi,
I am trying to write a query to do the following. I am having a lot of
trouble, because every time I leave Design View, Access modifies my
query. How would you recommend doing the following query...

I have these 3 fields
Date Parameter1 Parameter2

I want to calculate:
1) The average value of Parameter1 and Parameter2 between a time period
(Start1 -> End1 such as 04/01/2006 to 05/01/2006).
2) I then want to calculate the average value of Parameter1 and
Parameter2 between a different time period (Start2->End2 such as
05/01/2006 to 06/01/2006)
3) I want my report to display the difference in those two calculated
values for each parameter. So it would show Parameter1 from
start1->end1 minus Parameter1 from start2->end2. and the same for
Paramater2. See below:

Parameter1 Parameter2
85.2 68.3

When I do this in access, I don't have a column for the date. I use
that as my criteria, however it keeps changing it to add the date
column and then combine my two time periods into one criteria. This
isn't how I want it...Any suggestions?!?!

Thanks ~
Jen

 

[Rob Parker]

Since you haven't posted your query, I've got no idea why Access keeps
changing it. Nor do I see why it would need to - it does that only when you
enter something in the SQL which it cannot display in the query grid, and
there's nothing you need to do that requires that here.

The following query gives the averages for the two date ranges and the
difference in those averages [that's what you ask for with "... those two
calculated values ...", and I presume that's what you mean when you say "...
show Parameter1 from start1->end1 ..." - there will not be a single value of
Parameter 1 across the date range - unless it never changes .]

It assumes your table is named tblParameterData; it prompts for the start
and end dates of the two date ranges. I have named the date field as
MyDate - you should not use Date as a field name; it is a reserved word in
Access.

SELECT Avg(T1.Parameter1) AS AveP1D1, Avg(T1.Parameter2) AS AveP2D1,
Avg(T2.Parameter1) AS AveP1D2, Avg(T2.Parameter2) AS AveP2D2,
Avg(T1.Parameter1) - Avg(T2.Parameter1) AS DiffP1, Avg(T1.Parameter2) -
Avg(T2.Parameter2) AS DiffP2
FROM tblParameterData AS T1, tblParameterData AS T2
WHERE (((T1.MyDate) Between [Start Date 1] And [End Date 1]) AND
((T2.MyDate) Between [Start Date 2] And [End Date 2]));

This query does not - and cannot - return the start and end dates for the
two data ranges. However, you can include them in a report based on this
query. To do so, simply add four unbound text boxes to your report, and set
their control sources to the four parameter strings; they must match
exactly, so in this case, the control sources would be:
=[Start Date 1]
=[End Date 1]
=[Start Date 2]
=[End Date 1]

HTH,

Rob

 

[Jen]

Thanks Rob,
That's exactly what I was looking for...I will try entering that in the
SQL View instead of Design View...I was doing it in Design view and
still am not sure why it was changing it, but your SQL statement is
just what I need!

This is what I was doing in Design View..Is there something wrong?

Column1 Column2 Column3
Column4
AvgP1Start:T1.P1 AvgP2Start:T1.P2 AvgP1End:T1.P1
AvgP2End:T1.P2
T1 T1 T1
T1
Avg Avg Avg
Avg
Don't Show Don't Show Don't Show
Don't Show

The Criteria for Column1 and Column2 was: DateVal Between [Start1] And
[End1]
The Criteria for Column3 and Column4 was: DateVal Between [Start2] And
[End2]

Then I had two more columns that were displayed:
Column4 Column5
AvgP1: AvgP1Start-AvgP1End AvgP2: AvgP2Start-AvgP2End
T1 T1
Group By Group By
Show Show

When I closed the query and came back in, there was a column for
DateVal that said
Column3
DateVal
T1
Don't Show
Between [Start1] And [End1] And Between [Start2] And [End2]

I figure I did something wrong, but just wasn't sure how I should have
done it in query view. Thanks ~
Jen

 

[Rob Parker]

Hi Jen,

It's rather difficult to figure out exactly what you've got there - my
newsreader has inserted extraneous line-breaks, and I'm not really inclined
to disentangle it all. However, it appears that you're trying to get both
sets of data from a single table, and Access has combined your two criteria
columns into one.

The query I posted can be viewed in design mode, so you can see how it's
done. The key is that there are two copies of the table in the query
(aliased as T1 and T2), and a different one is used for each date range.

HTH,

Rob

 

[Jen]

Thanks Rob,
It seems that having an alias for the table each time is the key.
Thank you so much!

I have one other question...The query takes about 20 seconds before it
displays the results...Each average is of 2500 pieces of data...Does
that length of time make sense?

Thanks again ~
Jen

 

[Rob Parker]

Hi Jen,

The time taken for that query is likely to be long, because the average
function used involves calculations - generally, any of the "Totals" queries
will be slower than select queries. You could perhaps speed it up by
removing the Difference fields from the query itself, and doing those
calculations in textbox controls in your report; that should (I use "should"
rather than "will", since Access/Jet may use internal processing that's not
apparent to mere mortals such as I) remove half of the average calculations.

Other than that, if it's too slow you could post a new question asking how
the query performance could be improved. I suspect there's probably no-one
else actively following this thread now.

Rob