Add Individual Rows Across Tables 1

[vvicin01]

I am trying to add up individual rows across several tables. I have stats
on individual performers and would like to add all the performers up and list the results. For example, I have stats on hockey players (goals) across three tables
(scrimmage, tournaments and league). I would like to combine the three tables,
get the sum of each row and list in order the leaders of goals with all three tables
together. I appreciate your help.

Thanks
Vic

 

[THoey]

If the tables are in the same format, the only difference being that they contain data for scrimmage, tournaments and league matches, why not put all the data into one table and just add a "GameType" column that could have one of three values (scrimmage, tournaments and league)? This will save you all the pain of doing what you are trying to do...

If there is a reason why you have it set up this way, post again and someone will help...

Hope that helps... Terry M. Hoey

 

[JamesMeadlock]

I'd reiterate what Terry said... but if you must keep your current tabledesign, what I'd do is make a procedure to make the kind've table Terry is talking about every time you need to print your list... something like:

Public Sub MakeTempTable()

' Run a maketable query to make you a blank table for all your data
' Run an append query to append your scrimmage data to the temp table
' Run an append query to append your tournament data to the temp table
' Run an append query to append your league data to the temp table

' Then run a report based on your temporary table


End Sub

HTH
James

 

[jacksonmacd]

You could even use a UNION query, and forgo the creation of the temporary table.

 

[THoey]

Would a sum work across a UNION? I have never tried and I am not sure it would. Terry M. Hoey