Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Report grouping (Access 2007)

    We have a table containing data with the following structure; i.e., the Level A/B/C fields contain subfields delimited by commas (although other delimiters could be used) ("-" = blank).

    Employee | Level A | Level B | Level C
    Tom | Sewing, Drilling | - | Cutting
    Dick | Cutting, Painting | Drilling | -
    Harry | Drilling | Cutting, Sewing | Painting

    Is it possible to create a report to list these data in the following format or similar; i.e., with grouping by subfield?

    Skill | Level A | Level B| Level C
    Cutting | Dick | Harry | Tom
    Drilling | Harry, Tom | Dick | -
    Painting | Dick | - | Harry
    Sewing | Tom | Harry | -

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

    Re: Report grouping (Access 2007)

    I strongly recommend that you normalize the structure of your database, i.e. organize the data differently. You have a many-to-many relationship between employees and skills; you'd need to use three tables:

    tblEmployees, with fields EmployeeID (AutoNumber, primary key), LastName, FirstName etc.

    tblSkills, with fields SkillID (AutoNumber, primary key), SkillName and perhaps other fields.

    tblEmployeeSkills, with fields EmployeeID (number, long integer), SkillID (number, long integer) and Level (number, long integer, 1=A, 2=B etc)

    The combination of EmployeeID and SkillID would form the primary key of tblEmployeeSkills. This table would contain a separate record for each employee an each skill that he/she has.

    Once you have normalized the database, it becomes easy to summarize the data in different ways.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Report grouping (Access 2007)

    Thanks for your reply. It is not possible to reorganize the data structure and I wondered whether it was possible to report on it as is.

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

    Re: Report grouping (Access 2007)

    It would be very difficult. I'd print the data as they are in the table.
    I'm sorry, but that's the consequence of using such an unfortunate data setup.

Posting Permissions

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