Queries, VBA or both for large datasets?

[FatLane]

I have a database that tracks tournament results. I am developing a rankings program in order to rank all players who have participated in past tournaments.

I do not need help with the actual ranking algorithms, I need help retrieving the data that will be used in the ranking algorithms.

In order to produce the rankings, I need the following information.

• List of all players who participated in at least one tournament during the last 36 months
• Each of these players top score from the last 72 months

To make things more complicated, each tournament contains 5 rounds. Each player is ranked separately in each round. So I am looking for the top monthly score for each player, for each round for the previous 72 months.

We have about 5,000 different players who have competed over the last three years, so I am estimating that I need to retrieve about 1.8 million scores.

5,000 players * 72 months * 5 rounds = 1,800,000 scores

Once I have access to this information, I can easily calculate the rankings as desired.

I suspect processing this much data might take some time. I do not know if I can do this using only queries and tables, or if I should use VB code.

Any suggestions or thoughts would be appreciated.

My table structure is set up as follows:

tblPlayers
PlayerID (pk) (AutoNumber)
FirstName (Text)
LastName (Text)
Club (Text)
Country (Text)

tblEvents
EventID (pk) (AutoNumber)
divID (fk) (Number)
StartDate (Date/Time)
EventName (Text)
Venue (Text)



tblResults
ResultID (pk) (AutoNumber)
EventID (fk) (Number)
PlayerID (fk) (Number)
Level (Number)
Round1Score (Number)
Round2Score (Number)
Round3Score (Number)
Round4Score (Number)
Round5Score (Number)

[orange]

The multiple Round x Score fields suggest a normalization issue. You may need an Event_Round table?

Where does divID fit? You have it marked as fk
Can players ever change Clubs? especially if your time frame can be as long as 72 months

You talk Tournaments, but show Events, is that an issue.

Processing may be an issue, but proper table structure and relationships is the bigger concern - in my view.

[FatLane]

The multiple Round x Score fields suggest a normalization issue. You may need an Event_Round table?

I've been thinking about this and if it's going to cause a problem I will have to work it out.

I'm doing it like this because the results are sent to me like this:

ENT	FIRST NAME	LAST NAME	CLUB	COUNTRY	LEVEL	Round 1	Round 2	Round 3	Round 4	Round 5
214	Deanne	Kurtz	Sarry East	USA	10	21.39	56.29	13.10	29.51	54.54
202	Rianna	Simmons	Simmons	USA	10	27.06	73.72	12.68	23.70	60.59
211	Hannah	Priette	Cape Raye	USA	10	17.54	66.88	12.82	21.79	54.77

All I do now is append the results to the Results Table. To do it properly where each result record included a single round would require extra excel processing. Since the rounds will never change from five, I decided to do it like this. But if is going to cause problems, I can work it out.

Where does divID fit? You have it marked as fk

tblDiv is a lookup table that tells us what kind of tournament this is. I didn't include it in my original post. It looks like this:

tblDiv
DivID (pk) (Number)
DivisionName (Text)

Can players ever change Clubs? especially if your time frame can be as long as 72 months

Yes. They change fairly often.

I do not have a clubs table because it is not vital to what we are doing. In addition, there are thousands of clubs and they are not always reported the same. For instance, Sarry East (from the example above) may show up as Sarry's, Sarry, Sarry E, Sarry East -mo.

When we display the rankings, we display the the most recent club for each player.

You talk Tournaments, but show Events, is that an issue.

This is not an issue. We use the term Events. I only used the word tournament here to help clarify what we are actually doing.

Processing may be an issue, but proper table structure and relationships is the bigger concern - in my view.

I really appreciate your feedback.

I am an Excel man. I can do just about anything in Excel. But this project has outgrown Excel and I'm just not yet familiar with Access.