Creating Tables via Calculations from a Query

[TroxlSha]

Since beginning of this project everyone here has helped greatly, and now I am standing at yet another road block. I would like to first thank you for the time and effort utilized to help get this project running properly!



Anyway, the database consist of multiple tables that are designed to track laboratory test readings throughout a day so that a report can be printed each month with the raw data. What I am looking for is a way to utilize the data from each of these tables and update a new table with calculated values to be utilized for a different report that must be submitted to the state. These values will consist of sums, averages, and totals.

Gathering the Sum seems to be a simple enough task, and I believe I can make that portion work through some trail and error as it is always going to be the addition of two fields then moved into another table. At this point I am stuck on gathering the Averages for a day and having them placed into a field that corresponds with the correct date.

For example: During a day (lets say today) the records would show as follows

DATE | TIME | Reading 1 ...
6/20/13 08:00 5
6/20/13 10:00 8
6/20/13 12:00 4
6/20/13 16:00 10
6/20/13 20:00 6


What I need is to grab the average of those readings for 6/20/13 and place transfer them into another table that would show:

DATE | AvgReading 1
6/20/13 6.6
... and so on for each day.


Once again, I thank you for your help and time!

[rpeare]

so you just want a data point that says

Date       AvgReading1
6/20/2013  6.6

you do not want to append results to a table, you can do all these kinds of calculations in a query

let's say your table is named tblTest

Your query would be

SELECT DATE, SUM(Reading1)/COUNT(Reading1) AS AvgReading1
FROM tblTest
GROUP BY DATE

(just a word of warning DATE is a reserved word and you will likely have trouble with it unless you're just using it for the purpose of this example and not in real life)

[TroxlSha]

The reason I'd like to append these results is that I have numerous tables to pull data from then a complete some further calculations from there to generate the report. It is more so as this:

Flow Data : Table
dateStamp | timeStamp | RWF GPD Plt 1 | RWF GPD Plt 2 | RWF GPD Plt 3 | RWF GPD Plt 4
**From here I need to take the sum each date of RWF GPD 1 and RWF GPD 2 to divide into the data from:

Chemical Addition : Table
dateStamp | timeStamp | Polymer Loop 1 ml/min | Polymer Loop 2 ml/min
**The above would be divided into the Avg from Polymer Loop 1 ml/min

Granted this is the first of plenty more similar to this although from other fields and/or other tables. Should I have made the entire thing one table rather than have multiple tables perhaps?

[Dal Jeanis]

You can use temporary tables like that to simplify your life, but you don't actually have to. Every Query can be treated as if it is a table, so you can use a second query to analyze the results of a first query.

Am I correct in guessing that you have several different readings on each record? reading1, reading2, reading3 and so on? That not the preferred way to do it, but it can work in a limited database.

So you have a query, call it qryAvgTest like this:

SELECT ReadingDate, 
  AVG(Reading1) AS AvgReading1, 
  AVG(Reading2) AS AvgReading2, 
  AVG(Reading3) AS AvgReading3 
FROM tblTest
GROUP BY ReadingDate;

To put that into a second table you'd create an insert query (qryInsAvgReading) like this:

INSERT INTO tblAvgTest
(ReadingDate, AvgReading1, AvgReading2, AvgReading3)
(SELECT ReadingDate, 
  AVG(Reading1), 
  AVG(Reading2), 
  AVG(Reading3) 
FROM tblTest
GROUP BY ReadingDate);

Of course, the second time you ran that query, you'd have to either clear the table out before you ran that query, or add a WHERE clause that limited the new records by ReadingDate.


After the update had run, you can use tblAvgTest or qryAvgTest as the source for another query or report, and they should give the exact same result.

In practice, I only use temporary tables when they vastly simplify a query that was getting overcomplex.

[rpeare]

if you can give an example of your raw data and what you want your output to be (include formulas if they aren't apparent). we can see if a query would do what you want. As Dal pointed out, the reasons to use temp tables are few and far between and do not always simplify your process the way you want.