Creating a Table from a 2-Field Table

[Tim Strickland]

I have an Access 2000 table with 2 fields, (1) Personal ID Number (PersID),
and (2) Achievement ID Number (AchvID). A person can complete multiple
achievements. Multiple persons can complete the same achievement.

PersID AchvID
123456 53
123456 124
234567 124
234567 53
234567 96
345678 96

I'm trying to create a table with a field for PersID and each Achievement
(53, 96, 124) where a 1 corresponds to whether that PersID has completed
that task. In my example, it would look like this:

PersID Achv53 Achv96 Achv124
123456 1 1
234567 1 1 1
345678 1

I'll then either export it to Excel (for members that don't have Access) or
use it as the basis of an Access 2000 Report that has other types of data as
well.

Any support would be greatly appreciated. This is to keep track of the
qualifications of members of our youth based volunteer search & rescue
organization.

Tim

 

[Duane Hookom]

You should be able to create a crosstab query with PersID as the Row
Heading, "Achv" & AchvID as the Column Heading and Count(PersID) as the
Value.

 

[Tim Strickland]

Thank you Duane,

That was the perfect solution. Now, if my hair'll grow back no one will
ever know I was stuck.

Very Appreciatively,
Tim