Converting Excel Spreadsheet to Access: Need to find way to create COMBINATIONS

[DonEB]

I have a complex spreadsheet which does a lot of computations. There are way too many functions and it is time to try and convert it to an Access Database.

At the center of the spreadsheet application is the use of the COMBINATION function (see below):
1. {=Combinations(A3:A14,4)}
2. A3:A14 ... contain all the possible numbers that would be used
3. The "4" represents how many numbers are within a combination. Example: 1,2,3,4 is one combination, however, 4,3,2,1 is the same set of numbers and should not be repeated.

I have been unable to identify any function within Access that performs this type of calculation.

Let's assume I have a table called tblPlayer and has fields in it called PlayerNo and Status.
1. PlayerNo will contain numbers from 1 to 20
2. Status will contain either "Y" or "N" to reflect if the players are available to play a particular week or not.

Only those players who are available would be included within the calculation to determine the different combination of who could be playing with/against who.

Might anyone have an idea on how to resolve this problem?

Thanks for any ideas you might have.

Don

 

[welshgasman]

In Access that would be done with the correct data structure.
You would have a table for players, a table for weeks and a table that links players to weeks.
Then you just query that last table for the weeks in question.

 

[Micron]

I defer to anyone who has an answer that works since I don't really follow this sports team calculation stuff.

FWIW, I found out a while back that some Excel functions can be referenced from Access if you have the Excel library referenced in your project (i.e. as one of project references that you set in the vb editor). I've had limited need to use any of them, but I'd try that approach first, but I think your variables would have to be defined as (for example) 49 taken 7 at a time and not refer to a range. I suppose you've considered that already since if you want to eliminate Excel, you wouldn't have a range to work with. Access is not to be confused with or compared to Excel, really. They are different tools and you might want to consider using both. Access can 'pull in' or link to a computed value from a workbook if need be.

 

[Micron]

I had to try it

Setting a reference to Excel and putting this in a standard module and running it gave me an answer.

Sub TestExcelFunction()
MsgBox WorksheetFunction.Combin(49, 7)
End Sub

= 85900584