top 16 union

[penguinspeaks]

Hey gang. it's bam again.

I have 2 table in acces DB. one called USA_team and one called world_team. I want to join the tables so that the usa players will line up agains the world players. and only selecting the top 16 of each. so the output result would look like this.

USA world
a e
b f
b g
d h

what I am doing is setting up a Ryder cup style match between 2 teams. Now, I could do this another way, that I think mught be easier. I could have all the names in one table, and have a team column that would be usa or world. If I did it this way, how can i make 2 different columns from the same table. I tried different joins and unions, but couldn't get any to work.
Any ideas on either?
Bam

 

[r937]

how exactly do you want them to "line up"

does a=1, b=2, etc, in one table, while e=1, f=2, etc. in the other?

is there some kind of ranking within each team?

or do you want every player to be matched to every other player (llike in a cross join)?

rudy

http://r937.com/

SQL Consulting

 

[penguinspeaks]

hey. thanks for responding. the matchup doesn't matter at all, as long as they is a pairing. i used letters to make it simple. player "a" from the USA team could play anyone from the world team. as long as the same player doesn't show up twice, all will be well.
I hope this answers

 

[r937]

how often are you going to do this? if it's only once, display both tables, paste the names into excel, then import back into access as your new table

anything else will involve temp tables, autonumbers, and a clumsy join

rudy

http://r937.com/

SQL Consulting

 

[JonFer]

If you are going to store the pairings in a table, create the table first and put unique indexes on the fields that will identify each player and make the fields required. Then write an append query with both tables and no joins to produce a cartesian result and let the unique indexes prevent duplicates. It is somewhat inefficient but not a problem for small tables.

You can't restrict to just 16 rows with the append query (because you don't know that the first 16 will be unique) but you can use "Select Top 16 ..." queries instead of your tables in the append query if you really need to limit it to just 16. This will also allow you to vary the sorting of the players which should affect the pairing.


John

http://www.interautomateconsulting.com