OK Duane, here goes:
First the crosstab, with names as row headings, and dates as column headings I put the True/False Availability field as the value. The Access response was:
"You tried to execute a query that does not include the specified expression "Availability" as part of an aggregate function."
Not seeing how I could get round this I tried to sidestep it - and trod straight in it.
BACKGROUND
The database lists all matches played in a season at the club.
There are tables for:
1. tblMembers holds recordID, names, telephone nos, and check box field for each of the competitions members play in.
2. tblDates holds each day of the season (late April - early Sept) with an ID
3. tblOpposition holds an ID field, a linked field to tblDates, opposition team, name of competition/league
From these tables it is easy to set up reports showing when teams play.
Having got that far I wanted to assemble a system to show who could play in specific competitions on any date. The report format would have dates across the top, names down the left and check boxes under each date. (This is the one giving trouble)
The way that I was suggested earlier this year was to set up a linking table between tblmembers and tblOpposition, a linking field to each, with a true/false field to show availability. (This was the original tblComposite, with link fields to tblMembers and tblOpposition, and an Availability True/False field)
So far so good.
When I attempted to produce a form based on those tables it would show me the availability boxes - but not let me amend them.
I finished up with a composite table with each record having complete details of one person and one date. This allowed me to enter their availabilites into the true/false field. This is the one I am trying to use as the report data. and the one the crosstab failed on.
The previous system had each date as a field for each member in tblmembers, this needed massive maintenance each year but was easy to use. Form and report control datasources and labels had to be set up afresh.
I am trying to cut down the maintenance, but the setup is really pushing me.
Any advice? If it would help can I post linking diagrams anywhere?
Telephoto