Hope someone can help.

I am building a database to evaluate football team performance. I have a table with results (tblResults) where a team will have records in either the Home Team or Away Team fields depending on the fixture.

I want to include a "Team Form" calculation that selects the last 6 games for each team (both home & away) and allocates points to calculate a recent form stat for comparison with the opposition.

I have tried using the TOP VALUES option in a query, but I can't seem to get it to extract the top 6 in descending date order for every team.

Would be very grateful if someone could help!

Hi -

Take a look at this post which includes an example of a query using Top N. Without your table structure it's hard to say exactly how to proceed but you should be able to modify the example substituting your table and field names.

http://www.access-programmers.co.uk/forums/showthread.php?t=111880

HTH - Bob

Raskew, thanks for the reply. Tried your suggestion but when I changed top 1 to say top 5, it gives me only the top 5 for the first customer using the northwind example.

The abbreviated fields of my table are as follows:

Division
Home Team
Away Team
Date
Home Score
Away Score
Result

The same team can be in the home team or away team fields depending on the fixture. I want to write a query to extract the last 6 games for every team in the league ignoring whether they are home or away games.

All I have been able to do using the top values properties feature and sorting dates or using Max dates is get the last 6 games for Arsenal (the first team alphabetically).

Hope this gives further detail. Thankyou.

seem to have found a workaround for now. By giving every game in the results an id number, I am able to do a greater than the Max less 6 expression.

Thanks for your reply anyway.