table for only multiple entries

[jlwood44]

I am using Access 2000. I am a chess club sponsor. I have a table with last
year's results, name wins, losses, etc. And, I have a table with this year's
current results. I would like to look at the changes from last year to this
year. Each table has about 100 records, with about 30 being from each year.
I am assuming that I put the 2 tables together and work with a report. I
have listed the year for each person's play. I would like to print:

Name Wins Losses Draws Pct Year
Bryce Douglas 50 40 10 .550
2005
70 22 8
..740 2006
total 120 62 18

I'm sure that I can use grouping and summary to get this print, but how do I
only get those that have played more than one year?

Question #2: How can I sort by total number of games won?

 

[Bill Mosca]

There is no reason to create a separate table for each year. That's more like
something you would do in Excel.

But since you do have them separated, you can use a UNION query to combine
the 2 tables into one. Unfortunately, Access cannot show UNION queries in the
design window so you will have to write the SQL.

Open a new query in design view. Close the table selector. Click on View/SQL
view.

You will get a blank window. The SQL you need will be something like this:

SELECT Name, Count(Win) as Wins, Count(Loss) as Losses, Count(Draw) as
Draws, 2005 as Year
FROM tbl2005Scores
UNION
SELECT Name, Count(Win) as Wins, Count(Loss) as Losses, Count(Draw) as
Draws, 2006 as Year
FROM tbl2006Scores

Note: the number and position of fields must be the same for both select
queries.