Finding an Average in a Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have tow columns that contain a flow rate for each day. I want to build a
query that will 1) Get all the flow rates for a range of dates 2) and then
get the average of the total of column A and column B.
 
to get the average, try this:

SELECT AVG(ColumnA), AVG(ColumnB) FROM [YourTable]

Take care
Mauricio Silva
 
Larry

Why two columns?

In a spreadsheet, you can easily find sums/averages/etc. "between" columns.

In a well-normalized Access table, you can easily find sums/averages/etc.
"within" a column (i.e., across rows).

It would help us help you do your query, if we knew more about how your data
is structured.
 
I have two tables, Air Stripper 101, and Air Stripper 102, each table has the
same information, date, and flow rate is all I am interested in.

I have a query where I want the flow rates from each table from a range of
dates.

Then I want the average flow rate from 101 and 102 for the same range of
dates.

--
Never give up, the answer IS out there, it just takes a while to find it
sometimes!


Jeff Boyce said:
Larry

Why two columns?

In a spreadsheet, you can easily find sums/averages/etc. "between" columns.

In a well-normalized Access table, you can easily find sums/averages/etc.
"within" a column (i.e., across rows).

It would help us help you do your query, if we knew more about how your data
is structured.
Click to expand...
 
Hi Larry,

Actual table and column names would help us help you.

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT tblFlowDates.FlowDate,
Avg(tblFlowDates.A) AS AvgOfA,
Avg(tblFlowDates.B) AS AvgOfB
FROM tblFlowDates
GROUP BY tblFlowDates.FlowDate
HAVING (((tblFlowDates.FlowDate) Between [Start Date] And [End Date]));

For this to work, your date field better be a Date/Time data type and not
just something that looks like a date in a text field.
 
Sorry I was in a meeting.

OK the Table names are Air Stripper 101 and Air Stripper 102.

The columns are Stripper Flow Rate from each table.

Each table has the information for each stripper every day.
--
Never give up, the answer IS out there, it just takes a while to find it
sometimes!


Jerry Whittle said:
Hi Larry,

Actual table and column names would help us help you.

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT tblFlowDates.FlowDate,
Avg(tblFlowDates.A) AS AvgOfA,
Avg(tblFlowDates.B) AS AvgOfB
FROM tblFlowDates
GROUP BY tblFlowDates.FlowDate
HAVING (((tblFlowDates.FlowDate) Between [Start Date] And [End Date]));

For this to work, your date field better be a Date/Time data type and not
just something that looks like a date in a text field.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Larry G. said:
I have tow columns that contain a flow rate for each day. I want to build a
query that will 1) Get all the flow rates for a range of dates 2) and then
get the average of the total of column A and column B.
Click to expand...
Click to expand...
 
Larry

That you have one table per Air Stripper just reinforces my earlier
impression. It sounds like you have a database table structure that more
closely resembles a spreadsheet than a relational database. This will cause
you (and Access) considerable headache.

If your database had a single table with fields something like:

tblAirStripper
AirStripperRecordID
AirStripperNumber (your 101 or 102)
RecordDate
FlowRate
...

You could do your querying much more easily.

Regards

Jeff Boyce
<Office/Access MVP>

Larry G. said:
I have two tables, Air Stripper 101, and Air Stripper 102, each table has the
same information, date, and flow rate is all I am interested in.

I have a query where I want the flow rates from each table from a range of
dates.

Then I want the average flow rate from 101 and 102 for the same range of
dates.
Click to expand...
 
Back
Top