How to average values from multiple tables in a query

[Tom]

Hello,

Thanks in advance to anyone that can help.

I am trying to figure out how to perform an average calculation.

I have a PRIMARY Table called GAMES that lists TITLES of games. I then have
three other tables: STORE1, STORE2, STORE3. In these tables I have TITLES of
games and VALUE for each game listed.

The GAMES table lists ALL GAMES. The STORE1, STORE2, STORE3 will only list
GAMES in stock at that store.

I am trying to run a query that pulls all of the TITLES from GAMES and
provides an average VALUE for each one (ignoring any $0.00 or null values)

I am trying to do this in a query. I can't figure it out. Any help would be
appreciated.

Thanks.

 

[John Spencer]

You should have all the data for stores in ONE table with a field that
tells you which store this row of data applies to.

You can use a UNION query to combine the three Store tables into one and
then use the UNION query as the source for your aggregate query. Union
queries can only be built using the SQL view.

SELECT "Store 1" as Store
, GameTitle
, IIF(GameValue>0,GameValue,Null)
FROM [Store 1]
UNION ALL
SELECT "Store 2" as Store, GameTitle
, IIF(GameValue>0,GameValue,Null)
FROM [Store 2]
UNION ALL
SELECT "Store 3" as Store, GameTitle
, IIF(GameValue>0,GameValue,Null)
FROM [Store 3]

Save that as qAllStores.

Now create a new query with the GAMES table and the union query.
-- Join the GameTitle fields in the table and the query by dragging from
one to the other
-- Double Click on the join line and select option 2 or 3 (all records
from games and matching records from qAllStores
-- Add the GameTitle field from the Games table
-- Add the GameValue field from the query
-- SELECT View: Totals from the menu
-- Change GROUP BY to AVG under the GameValue field

Run the query
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Jerry Whittle]

Immediate fix: Create a Union query to join all the store tables.

Correct fix: Rebuild your database properly. Having a table for each store
is wrong. What happens to all your queries, forms, and reports when you add a
4th store?

I highly recommend getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.