Please help! I am trying to get from a query to a report but I am having lots of problems. I am using MS Access 2000 and I am relatively new to Access and SQL.
My problem:
I need to produce a report that shows the players on the four winning teams on a given date. Sounds pretty simple.
To solve my problem I created a simple "Team Standing" Query that lists all players who played on a given date. The query details Team, Name, Date and Points.
Then I need to determine the four winning teams by totaling the top four player scores on each team. Here is were my problems start.
As an added complication, when two teams are tied, I need to compare the 5th and possibly 6th player scores to break the tie.
First I tried creating another query using "Select TOP 4 " from my "Team Standings" query but I kept running into the problem of duplicate player scores which intermitenly caused 4 or 5 players per team to be selected. I could not be guaranteed only four player results per team were being selected. This problem with duplicates appears to be a documented problem with the "Select Top" statement. Dealing with ties never even got considered.
Not seeing a way around this problem with the "Select Top" statement I decided to write a VBA module which reads the "Team Standing" query and identifies the top four teams and deals with the issue of ties. The module works perfectly.
The problem I am now having is I can not figure out how to take the top four teams I have identified in my VBA module and translate that into report I need to produce????
How do you link modules to queries or reports? How do I run my "Team Standing" query followed by my VBA module, followed by a re-query of "Team Standings" followed by a report? I think I want to re-query my "Team Standings" query and only select data for the four winning teams but I do no know how to get there.
I would appreciate any help.
Kevin .... a newbie from Ontario Canada
My problem:
I need to produce a report that shows the players on the four winning teams on a given date. Sounds pretty simple.
To solve my problem I created a simple "Team Standing" Query that lists all players who played on a given date. The query details Team, Name, Date and Points.
Then I need to determine the four winning teams by totaling the top four player scores on each team. Here is were my problems start.
As an added complication, when two teams are tied, I need to compare the 5th and possibly 6th player scores to break the tie.
First I tried creating another query using "Select TOP 4 " from my "Team Standings" query but I kept running into the problem of duplicate player scores which intermitenly caused 4 or 5 players per team to be selected. I could not be guaranteed only four player results per team were being selected. This problem with duplicates appears to be a documented problem with the "Select Top" statement. Dealing with ties never even got considered.
Not seeing a way around this problem with the "Select Top" statement I decided to write a VBA module which reads the "Team Standing" query and identifies the top four teams and deals with the issue of ties. The module works perfectly.
The problem I am now having is I can not figure out how to take the top four teams I have identified in my VBA module and translate that into report I need to produce????
How do you link modules to queries or reports? How do I run my "Team Standing" query followed by my VBA module, followed by a re-query of "Team Standings" followed by a report? I think I want to re-query my "Team Standings" query and only select data for the four winning teams but I do no know how to get there.
I would appreciate any help.
Kevin .... a newbie from Ontario Canada
