Creating a ‘spreadsheet’ report (A2k)

Topic

New Reply

I’m just looking for suggestions on how to set this up. I have a list of about 60 people (not very many), with 8 possible training events to attend, depending on their rank. I’m able to pull information into two tables, defined by classes that are done in person, or classes that are done online. Each table would list EVERY course completed in the respective way, however I only need to report on four from each table. I’d like to set up a table like this:

NAME	TABLE 1 ITEM 1	TABLE 1 ITEM 2	TABLE 1 ITEM 3	TABLE 1 ITEM 4	TABLE 2 ITEM 1	TABLE 2 ITEM 2	TABLE 2 ITEM 3	TABLE 2 ITEM 4
NYIntensity	X			X	X	X
Jezza	X	X	X	X	X	X	X	X

I need to (1): define the training events I need to track, (2): list them at the top of the report, (3): list all members of the group in the left column, and (4) indicate whether the person has a matching record in one of the tables corresponding to the columns in the report.

Reply | Quote

Replies

Create a query based on Table 1.
Add the person field, the course field and again the course field to the query grid.
Select Query | Crosstab query.
Leave the Total option for the first to columns to the default Group By, but set it to Count for the third column.
Set the Crosstab option for the three columns to Row Header, Column Header and Value, respectively.
Click in an empty part of the upper half of the query window.
Activate the Properties window.
Enter the four courses you want to list in the Column Headings property, separated by commas.
Save the query.

Repeat for Table 2.

Finally, create a query based on the table that lists all persons and the two queries you created. Join the persons table to each of the queries on the person field, double click the join line and specify the option to return all records from the persons table.
Add the person field from the persons table, and the course fields from each of the queries.

Reply | Quote