Crosstab Query

[millwheal]

I have a membership database which contains [amongst other things, and in a variety of tables], members names, offices held, and the 'from' & 'to' years for each office held.
I set up a crosstab query to enable me to produce a tabular report showing all the offices each member had held and when they held them. The query results/report look like this:

Person Office-1 Office-2 Office-3 ... Office-n

Name1 98 99-00 08
Name2 01-03 01-

etc.

The query produces the data as above, combining all the data into a single row for each member, which was just what I wanted. However, I then realised it was incomplete. If a member holds an office for more than one period [which is very likely], only the latest period is shown.

Is there any way of creating results that give a second row for a member when there is more than one input for that member for the same office?

 

[khawar]

enter from to year in your query row field

 

[millwheal]

I am a novice as far as Crosstab queries are concerned, but if I understand the suggestion properly, it doesn't work.
The 'From-To' field is a field calculated in the query, from two fields [From and To] and is the only 'Value' field in the query.
If I change 'From-To' to a Row Heading, I get an error message because I have no Value fields in the query. If I try to add another field as a Value field, I get further error messages.

 

[khawar]

Can you give some detail about table and field names

 

[millwheal]

The crosstab query extracts data from three tables.
The main table contains a record for each Office that any Member has held: 'Member Name', 'Surname', 'Office Held', 'From Year', 'To Year'.
Data is brought in from two other tables: 'Date Joined' [when the Member joined] from a Members table and 'Printing Sequence' from an Offices table.
'Date Joined' is simply for information on the output; 'Printing Sequence' is the order across the page in which the Office details of the Member are to be organised.

The query has five columns:

'Name' from the Main table: 'Group By', Row Heading;
'Printing Sequence' from a secondary table: 'Group By', Column Heading;
'Surname' from the Main table: 'Group By', Row Heading, Sort = ascending;
'Date Joined' from a secondary table: 'Group By', Row Heading;
'From-To' which is a conditional concatenation of 'From Year' and 'To Year' from the Main table: Expression, Value

The output from the query is [almost] just what I want: a single record showing all the Offices held by each Member, and the period for which they held that office. However, when a Member has held the same office for more than one period (which is quite a common occurrence), one of the input records is effectively ignored. (I suspect the second occurrence actually overwrites the first.)