Auto Assign Referees To Fixtures

[Dannyboy11]

Hi

I have a junction table of fixtures (FixtureID, HomeTeamID, AwayTeamID, KickOffId, RefereeId, Date, VenueId, Mark)

All of the fields are filled in except for RefereeId and Mark. I have a table of referees.

I have a query that shows me which referees are available to referee on a particular date, and I also have a query that will show me the distance between a referee and a venue, based on the two post codes.

What I would like to be able to do is for a query to auto assign / suggest, which referee should be assigned to which fixture based on the criteria that they are available on the fixture date, and the miles between the referee and the fixture venue is less than 10. (I'd like it more advanced than simply hard wiring 10 into it but this will do for now as I just need the basic concept for the time being)

Is there any way to do this? Your input would be greatly appreciated, thanks

 

[jdraw]

What info do you have for post codes? Post codes represent areas, not points generally. So if you have the centroid of the post code area (representing the central point within the post code area), you could calculate the distance to another point (fixture here).

Don't know how much this helps, but maybe it will give you an idea.