Hello,
I have two tables, WindVector (wind data) and DOH_FC88to07 (water quality samples). The WindVector table has the following fields:
Date (short date)
Time (short time)
WindSpeed
WindDir
WindDirRads
V
U
The DOH_FC88to07 table has the following fields:
Station
Date (short date)
Time (short time)
FC
I am able to successfully calculate the average wind speed and direction for a given (user defined) date and time range (see SQL below)
SELECT Avg(WindVector. V) AS VAvg, Avg(WindVector. U) AS UAvg, Sqr(([VAvg]^2)+([UAvg]^2)) AS R, atan2((-[VAvg]),(-[UAvg]))*57.29577951 AS Wdir
FROM WindVector
WHERE (((WindVector.T ime) Between [Forms]![frmParameters]![WindStartTime] And [Forms]![frmParameters]![WindEndTime]) AND ((WindVector.DA TE)=[Forms]![frmParameters]![SampleDate]));
What I'd like to do now is calculate the average wind speed and direction for a user defined time period (e.g. four hours) preceding a water sampling event. There are multiple sampling events on the same day but at different times.
Here's what I've tried thus far...but the query doesn't return any results.
SELECT DOH_FC88to07.St ation, DOH_FC88to07.Da te, DOH_FC88to07.Ti me, DOH_FC88to07.FC , Avg(windvector. V) AS VAvg, Avg(windvector. U) AS UAvg, Sqr(([VAvg]^2)+([UAvg]^2)) AS R, atan2((-[VAvg]),(-[UAvg]))*57.29577951 AS Wdir
FROM windvector, DOH_FC88to07
WHERE (((windvector.D ATE)=[DOH_FC88to07]![Date]) AND ((windvector.Ti me) Between DateAdd("h",-4,[DOH_FC88to07].[time]) And [DOH_FC88to07].[Time]))
GROUP BY DOH_FC88to07.St ation, DOH_FC88to07.Da te, DOH_FC88to07.Ti me, DOH_FC88to07.FC ;
Any help would be greatly appreciated (this is fairly urgent)
MS Access 2003
Windows XP Pro Version 2002 Service Pack 2
1 3534 MMcCarthy 14,534
Recognized Expert Moderator MVP
You need a relationship between WindVector and DOH_FC88to07 other than Date and Time. Have a look at this tutorial on Database Normalisation.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Jeff Blee |
last post by:
I hope someone can help me get this graph outputing in proper order.
After help from Tom, I got a graph to display output from the previous
12 months and include the average of that output all in the one graph.
The output was in the order of the months, but after unioning with the
averages SQL code, the order is lost. Below is the full sql code that
is the data source for the graph:
SELECT (Format(.,"mmm"" '""yy")) AS Month,...
|
by: David |
last post by:
This might be a report question but I think it is more a query issue
than a report. I am a novice at bothAccess reporting and queries and
have been struggling to build the following. (Note using Access2000)
I have three tables (that are part of a different program so I can't
change their structure). They are Units, Streams, and _Strms. Units
contains info about various units.
The fields within Units that I am interested in are ID,...
|
by: Adam Louis |
last post by:
I would like help resolving this problem. I'm a novice who's been
hired to query a hospital database and extract useful information,
available to me only in a dynamically generated, downloadable .mdb.
The query below query runs correctly and without error, but any
attempt to save it causes Access to crash without a message, leaving
the .ldb file. Opening the DB reveals it saved a blank "query1".
I've upgraded to Jet SP 8, and I'm running...
|
by: Simon Bailey |
last post by:
I currently have a query calculating the gap in days between two
dates. The fields being "DateLookedAt" and "DateResolved" plus the
calculated field "TimeTaken".
I am looking to add another field to my report (or does this have to
be created in the query?) that calculates the average of all the data
in the "Time Taken" field.
SQL code or some tips would be much appreciated.
|
by: Pea |
last post by:
Is it possible to get the average in a query where I have multiple
criteria?
Here's an abbreviated example of the query in design view:
Fields: USERID DATE TIME ID_TYPE
Criteria: <>JoeSchmo <>*2003 <7AM <>this and <>that
Criteria: <>JoeSchmo <>*2003 7-9AM <>this and <>that
Criteria: <>JoeSchmo <>*2003 9-11AM <>this and <>that
Question: is it possible to get a total count...
| |
by: Tina |
last post by:
My employer tracks productivity/performance of clinicians (how much
they
bill) each week, its averages for the month, and the 6 months.
These averages are compared to their expected productivity.
However, the expectation changes - it may be 60% for a while, then
change to
50%. Initially, I was averaging the expectation, along with the
productivity, but what I'm being asked is to look at the average
productivity/performance compared to...
|
by: flumpuk |
last post by:
Hi
My job currently requires me to enter data from 300+ forms a month.
The system which we used in Excel was slow , and theprevious guy had
three workbooks for this job .
I have created a table in Access with four fields
|
by: Monroeski |
last post by:
I am using Access 2003. I am setting up a form where a user selects a Clinic or Ward, beginning date, and ending date, and the form outputs the averages of a few fields based on that criteria. I have come up with this so far for the query -
Dim strSQL As String
strSQL = ""
If chkClinic.Value = -1 Then
strSQL = "Select avg(Courtesy), avg(Timeliness), avg(Confidence), avg(Privacy), " & _
"avg(Sensitivity),...
|
by: PtotheC |
last post by:
I have a problem that I need to solve in MS Access 2000. I know how to do this in Excel. However this needs to be in Access due to a customer’s requirement.
I have a list of events and what the out come was on a particular date. To give you some idea, the event is basically number given to a day and the outcome is all possible things that “could happen” and that did happen (so not all 160 possible Outcomes are used all the time).
So it is...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |