I am attempting to acheive a form displaying statistic totals from another table. Below are the related objects and fields:

Match Table (with 100+ records)
- MatchID
- OppositionID
- CompetitionID
- ResultID

Opposition Table (with 20 records)
- OppositionID
- OppositionName

Competition Table (with 4 records)
- CompetitionID
- CompetitionName

Result Table (with 3 records)
- ResultID
- ResultName

Pretty straight forward at this point, there are obvious links from the Match Table to the others.

I have created a form based on the Opposition Table and within this I want to display how many times the OppositionID is located in the Match Table, whilst taking into account the CompetitionID and ResultID.

For example,

How many times OpponentID (displayed on form) is used in Match Table when CompID is 1 and ResultID is 1
How many times 01 (OppID) is used in Match Table when CompID is 1 and ResultID is 2

Etc, etc.

I have managed to create SELECT queries for each instance (x20), create a small form with the single text box based on the query and add this to the form as a subform, but this means creating 20 seperate queries and 20 seperate subforms and adding them to the original form, making the form design very difficult to work with.

I found a way of doing it using the DCount function but it is ridiculously slow. Is there another way of doing this in less than the 20 queries / 20 subforms method?

Thanks

Elcey

One query that takes arguments will do it.

Select OppositionID, CompetitionID, ResultID, Count(*) As ResultCount
From tblMatch
Where OppositionID= Forms!YourForm!OppositionID AND CompetitionID = Forms!YourForm!CompetitionID
Group By OppositionID, CompetitionID, ResultID;

This will produce a count of each different result between the two selected IDs. If there are 20 unique results, there will be 20 counts returned.