J
John Johndon
Hello everyone;
I posted a question about a query I was trying to get running and John
Vinson (sp?) gave me some good advice and it appeared to be on the right
track but it needs some tweaking so I ask for help again.
I have a table, populated with members of two teams, A and B and other
attributes for each player.
The list has an overall ranking ie from 1 to 100 and each player is assigned
to a position ie 1 to 100
I want to write a query that goes through the list of team members from
team A and finds the nearest player from Team B.....the team B player can
have a higher number or a lower number, and of course it is possible that
there are cases where Team A player #56 has team B players at #55 and #57,
but I only want the one with the highest number (#55).
I also want to be able to grab the details of the team B player at the same
time ie I want to get his employee number, position and other stats.....
Here is a sample of the table
1 A John 12345 103 045
2 A George 23456 105 045
3 B Paul 34567 110 067
4 B Ringo 45678 089 123
5 A Mick 56789 067 156
6 B Keith 67891 123 145
7 A Charlie 78912 156 067
8 A Mick 89123 134 089
9 B Bill 91234 122 099
So I want to publish a report based on a quesry that goes through all the A
team members and figures out the nearest team B player.
John Vinson suggested that I create a calculated field Nearest Opposite
Team: Abs([table1].[team_number]-table1_1].team_number]), then set the Top
Values to 1 of the query, and order the field in ascending order.
I modified this a bit by Nearest Opposite Team: Abs
([table1].[team_number]-[selected_team_B].[team_number]) ......where the
selected_team_B is a quesry that simply selected all the members of team
B....
This all seemed to work ok, except I noticed that the query only produced
team A members whose difference from a Team B player was 1.....ie if there
was two people between them, they didn't show up in the result......
Any help would be appreciated.
TIA
JJ
I posted a question about a query I was trying to get running and John
Vinson (sp?) gave me some good advice and it appeared to be on the right
track but it needs some tweaking so I ask for help again.
I have a table, populated with members of two teams, A and B and other
attributes for each player.
The list has an overall ranking ie from 1 to 100 and each player is assigned
to a position ie 1 to 100
I want to write a query that goes through the list of team members from
team A and finds the nearest player from Team B.....the team B player can
have a higher number or a lower number, and of course it is possible that
there are cases where Team A player #56 has team B players at #55 and #57,
but I only want the one with the highest number (#55).
I also want to be able to grab the details of the team B player at the same
time ie I want to get his employee number, position and other stats.....
Here is a sample of the table
1 A John 12345 103 045
2 A George 23456 105 045
3 B Paul 34567 110 067
4 B Ringo 45678 089 123
5 A Mick 56789 067 156
6 B Keith 67891 123 145
7 A Charlie 78912 156 067
8 A Mick 89123 134 089
9 B Bill 91234 122 099
So I want to publish a report based on a quesry that goes through all the A
team members and figures out the nearest team B player.
John Vinson suggested that I create a calculated field Nearest Opposite
Team: Abs([table1].[team_number]-table1_1].team_number]), then set the Top
Values to 1 of the query, and order the field in ascending order.
I modified this a bit by Nearest Opposite Team: Abs
([table1].[team_number]-[selected_team_B].[team_number]) ......where the
selected_team_B is a quesry that simply selected all the members of team
B....
This all seemed to work ok, except I noticed that the query only produced
team A members whose difference from a Team B player was 1.....ie if there
was two people between them, they didn't show up in the result......
Any help would be appreciated.
TIA
JJ