Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / July 2006

Tip: Looking for answers? Try searching our database.

Query with two time periods

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Jen - 27 Jun 2006 01:47 GMT
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 - 27 Jun 2006 03:18 GMT
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

> Hi,
> I am trying to write a query to do the following.  I am having a lot of
[quoted text clipped - 25 lines]
> Thanks ~
> Jen
Jen - 28 Jun 2006 00:49 GMT
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 - 28 Jun 2006 05:53 GMT
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

> Thanks Rob,
> That's exactly what I was looking for...I will try entering that in the
[quoted text clipped - 38 lines]
> done it in query view.  Thanks ~
> Jen
Jen - 05 Jul 2006 20:08 GMT
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 - 06 Jul 2006 10:31 GMT
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

> Thanks Rob,
> It seems that having an alias for the table each time is the key.
[quoted text clipped - 6 lines]
> Thanks again ~
> Jen
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.