Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Wilmington, North Carolina, USA
    Thanked 0 Times in 0 Posts

    Creating a 'spreadsheet' report (A2k)

    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:

    <table border=1><td>NAME</td><td>TABLE 1 ITEM 1</td><td>TABLE 1 ITEM 2</td><td>TABLE 1 ITEM 3</td><td>TABLE 1 ITEM 4</td><td>TABLE 2 ITEM 1</td><td>TABLE 2 ITEM 2</td><td>TABLE 2 ITEM 3</td><td>TABLE 2 ITEM 4</td><td>NYIntensity</td><td>X</td><td></td><td></td><td>X</td><td>X</td><td>X</td><td></td><td></td><td>Jezza</td><td>X</td><td>X</td><td>X</td><td>X</td><td>X</td><td>X</td><td>X</td><td>X</td></table>

    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.
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Creating a 'spreadsheet' report (A2k)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts