Daily average query from multiple tables

[Ian]

Hi guys,

I am making a query that pulls data from several tables. Example tables are
below:

Table 1 (one entry per day):
Date
Batch # (unique ID)
Sample #
Yield

Table 2 (multiple entries per day):
Batch # (linked to Table 1 - multiple lines with this #)
Process Datapoint

The idea is that I'm trying to take an average of all the process data
points for each day and have it show up in a query that shows the following

Query 1
Date
Batch #
Sample #
Yield
Daily average of process data

How do I go about doing this?

TIA
Ian

 

[John Spencer]

SELECT A.[Date]
, A.[Batch #]
, A.[Sample #]
, A.Yield
, (SELECT Avg(Process)
FROM [Table 2]
WHERE [Table 2].[Batch #] = A.[Batch #]) as TheBatchAverage
FROM [Table 1] as A

If you want the DAILY average for ALL batches then you will need to change the
subquery to match on the date field instead of the batch number

, (SELECT Avg(Process)
FROM [Table 2]
WHERE [Table 2].[Date] = A.[Date]) as TheDailyAverageForAllBatches

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Michel Walsh]

SELECT a.[date], a.[batch#], LAST(a.[sample#]), LAST(a.yield),
AVG(b.datapoint)
FROM table1 AS a INNER JOIN table2 AS b
ON a.[batch#]=b.[batch#]
GROUP BY a.[date], a.[batch#]


I assumed there is only one record, in table1, for each pair { date,
Batch# }

I assumed there is only one date per [batch#] in table2 : a given batch#
does not span multiple dates, because table2 has no date field which will
allow us to identify which date each table2 record belong to, if there is
more than one date per batch#.





Vanderghast, Access MVP

 

[Ian]

With both responses, I get the following error:

"You tried to execute a query that does not include the specified expression
'Date' as part of an aggregate function'

Here is what I used:

Select
Drying_Daily_greenware.Date,
last(Drying_Daily_greenware.[Slip#]),
last(Drying_Daily_greenware.Product_Wt),
AVG(Drying_Dryer_data.Nozzle_Pressure)
From
Drying_Daily_greenware INNER JOIN Drying_Dryer_data

SELECT a.[date], a.[batch#], LAST(a.[sample#]), LAST(a.yield),
AVG(b.datapoint)
FROM table1 AS a INNER JOIN table2 AS b
ON a.[batch#]=b.[batch#]
GROUP BY a.[date], a.[batch#]


I assumed there is only one record, in table1, for each pair { date,
Batch# }

I assumed there is only one date per [batch#] in table2 : a given batch#
does not span multiple dates, because table2 has no date field which will
allow us to identify which date each table2 record belong to, if there is
more than one date per batch#.





Vanderghast, Access MVP

Hi guys,

I am making a query that pulls data from several tables. Example tables
are
below:

Table 1 (one entry per day):
Date
Batch # (unique ID)
Sample #
Yield

Table 2 (multiple entries per day):
Batch # (linked to Table 1 - multiple lines with this #)
Process Datapoint

The idea is that I'm trying to take an average of all the process data
points for each day and have it show up in a query that shows the
following

Query 1
Date
Batch #
Sample #
Yield
Daily average of process data

How do I go about doing this?

TIA
Ian

 

[Michel Walsh]

You forgot the GROUP BY clause:

GROUP BY Drying_Daily_greenware.Date


Vanderghast, Access MVP

With both responses, I get the following error:

"You tried to execute a query that does not include the specified
expression
'Date' as part of an aggregate function'

Here is what I used:

Select
Drying_Daily_greenware.Date,
last(Drying_Daily_greenware.[Slip#]),
last(Drying_Daily_greenware.Product_Wt),
AVG(Drying_Dryer_data.Nozzle_Pressure)
From
Drying_Daily_greenware INNER JOIN Drying_Dryer_data

SELECT a.[date], a.[batch#], LAST(a.[sample#]), LAST(a.yield),
AVG(b.datapoint)
FROM table1 AS a INNER JOIN table2 AS b
ON a.[batch#]=b.[batch#]
GROUP BY a.[date], a.[batch#]


I assumed there is only one record, in table1, for each pair { date,
Batch# }

I assumed there is only one date per [batch#] in table2 : a given batch#
does not span multiple dates, because table2 has no date field which will
allow us to identify which date each table2 record belong to, if there is
more than one date per batch#.





Vanderghast, Access MVP

Hi guys,

I am making a query that pulls data from several tables. Example tables
are
below:

Table 1 (one entry per day):
Date
Batch # (unique ID)
Sample #
Yield

Table 2 (multiple entries per day):
Batch # (linked to Table 1 - multiple lines with this #)
Process Datapoint

The idea is that I'm trying to take an average of all the process data
points for each day and have it show up in a query that shows the
following

Query 1
Date
Batch #
Sample #
Yield
Daily average of process data

How do I go about doing this?

TIA
Ian

 

[Ian]

oh... Der... yeah, that works a bit better. Thanks... now on to tweak it a
bit.