For a crosstab, your Occurence Letter field should only have Uabs or Adherence, you have Written with Adherence in the comments. If you do not have these two entries then the crosstab will produce extra columns based on the other words in the field.
Then use an append query to append your crosstab to your table.
Awesome that works great.... Now I have another problem.. I have two cross tab queries.. One to count the number of Uabs and Adherence for each Lawson. And one to show the last date from Occurence for each Lawson.
When I append the two together I get over 7000 results rather then 86..
The occurence field has 8 possible values. So for "Written - Uabs" I need to know when it was entered for that Lawson and then put that date in the summary table.
Correct me if I am wrong for each Lawson you need to know the last date when the relevant Occurence was entered? So for Lawson 121393 you need to know the last date of entry for Adherence, UABS etc. keeping in mind there maybe 8 possible values.
Create a query with the following fields Lawson, Occurence and Date.
From the View menu select the Totals and the query will change to a totals query.
Leave the Group by for the Lawson and Occurence columns BUT change the Group by in Date to DMAX.
The above will give you the Last Date for each Occurence value for each Lawson.
Then use this query to update Date field in the table that currently stroes the results of your crosstab count of Occurences.
If need assistance, please send a copy of your Attendance table and the table that you are appending the crosstab. Also include your crosstab queries.