combining queries.

[Phoenix_fiames]

In my database i am using for my pool league i have 15 seperate tables (1 for
each team) which - along with a few other fields - have the player, team and
the points they have scored so far.

What i am looking to do is create a query which lists all the players from
the league - the team they are on and the points they have scored.

Can someone tell me how to do this?

NOTE:- the tables are not linked in anyway by relationships.

 

[Douglas J. Steele]

You need to reconsider your design. You should not have 15 separate tables:
you should have a single table, with an additional field that identifies the
team.

In the meantime, you may be able to create a Union query that simulates what
your "correct" table will look like:

SELECT 1 AS Team, Field1, Field2, Field3
FROM Team1Table
UNION
SELECT 2 AS Team, Field1, Field2, Field3
FROM Team2Table
UNION
SELECT 3 AS Team, Field1, Field2, Field3
FROM Team3Table
UNION
....
UNION
SELECT 15 AS Team, Field1, Field2, Field3
FROM Team15Table

and then base your other queries on that query.

 

[Daniel Pineault]

Why did you create a seperate table per team???

You should create a single table 'players' in which you have a field 'Team'
to identify the team they belong to. Then you could pull the info you are
looking for now with one simple select query.

I would urge you to revise your base design before you move forward. If you
need help, post a question here, we'll help you out!
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Phoenix_fiames]

the reason i have seperate tables is because the other fields i mentioned are
different for each player. the reason being that they are individual
performance tables. i.e. to keep a record of which games the player has won.

what i will do is take the points out of the ip and just make a seperate
table and list all the players again for the points. i no this is not
efficint but it will work i guess

 

[John W. Vinson]

the reason i have seperate tables is because the other fields i mentioned are
different for each player. the reason being that they are individual
performance tables. i.e. to keep a record of which games the player has won.

what i will do is take the points out of the ip and just make a seperate
table and list all the players again for the points. i no this is not
efficint but it will work i guess

You're using a relational database. USE IT RELATIONALLY!

You need at least three tables: Teams; Players; Games. Each Team consists of
multiple Players, each Player plays multiple Games. The won/lost calculations
should be done on the Games table; there should not be any won/lost
information stored in the table of players, as you apparently are doing.