Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Apr 2003
    Location
    Kansas City, Missouri
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Looking up second column in a report (Access 2003)

    I have a report in Access that displays the value of a column (called Course Code). I would like for it to instead display the associated value (called Course Name). My database is structured as follows:

    [Course Library] - For information purposes. The first field is [Course ID], which is unique. The second field is [Course Name].
    [CEU Form] - There is a field called [Course Name]. This field has a Lookup relationship with the [Course Library] table. It only shows the first column, but I do want the second column for reporting purposes.

    I have a SQL statement that I inherited. I'm not that well-versed in SQL, so I'm not exactly sure how to display the second field in my SQL statement. The SQL statement is:

    <code>SELECT Trainer, [Course Code], [Date of Course], "Trainer explained the learning objectives" AS CEUCommentType, <trainer(s) explained the learning objectives> AS CEUScore, [Comment: Trainer(s) explained the learning objectives] AS CEUCommentText
    FROM [CEU Form]
    WHERE ([Date of Course] Between [Enter the starting date] And [Enter the ending date]) and [Comment: Trainer(s) explained the learning objectives] Is Not Null
    UNION ALL
    SELECT Trainer, [Course Code], [Date of Course], "Class accomplished the stated objectives", [This class accomplished the stated objectives], [Comment: This class accomplished the stated objectives]
    FROM [CEU Form]
    WHERE ([Date of Course] Between [Enter the starting date] And [Enter the ending date]) and [Comment: This class accomplished the stated objectives] Is Not Null;</code>

    Ultimately, I'd like the [Course Code] to display the [Course Name] from the [Course Library] table. I can generate this in a simple SQL statement, but I'm totally lost on this one. Is anyone able to push me in the right direction?

    Alternatively, I'd be just as happy changing the report itself rather than the query. I don't think I can, but I'd love to be proven wrong.

    Kevin Elmore

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looking up second column in a report (Access 2003)

    Do you have a text box on the report that displays the Course Code? If so, right-click it (while in design view) and change it to a combo box. If you already had a combo box, that step is not necessary, of course.
    Set the properties of the combo box as follows:
    In the Format tab of the Properties window:
    - Column Count: 2
    - Column Widths: <code>0";1"</code>
    In the Data tab:
    - Control Source: the Course Code field from the table
    - Row Source Type: Table/Query
    - Row Source: Course Library
    - Bound Column: 1
    Post back if that doesn't help.

  3. #3
    Star Lounger
    Join Date
    Apr 2003
    Location
    Kansas City, Missouri
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking up second column in a report (Access 2003)

    Now that I see it before me, this does look a bit familiar. I'm not too savvy in report manipulation either.

    But it works like a charm. And I don't have to manipulate a SQL statement. That's a load off my mind!

    Thanks again, Hans.

    Kevin Elmore

Posting Permissions

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