Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    64

    Unanswered: combine multiple rows into one

    I have a table that has multiple rows for one employee.
    I would like to combine them into one row.

    For example

    EmployeeID, Firstname, Lastname, plan description, plan Category
    ID1, John, Smith, Delta dental, Single
    ID1, John Smith, Life insurance, Basic
    ID1, John Smith, Long term disablity, 2/3Salary
    ID2, Mary, Olson, Delta dental, family,
    ID2, Mary, Olson, Preferred one, Single.

    I would like to put each employee into one row.
    For example:
    EmployeeID, Firstname, Lastname, plan description1, plan Category1,plan description2, plan Category2, plan description3, plan Category3


    ID1, John, Smith, Delta dental, Single, lifeinsurance, Basic, longterm disablity, 2/3 salary.

    How can I do it?

    Thanks a lot

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It's a very bad idea because it's against Data Normalization, see:
    The Relational Data Model, Normalisation and effective Database Design

    It would be better to create several tables, one for the Employees, one for the Plans (with a PlanID) and a third that would link Employees and Plans and would only contain pairs of EmployeeID - PlanID.

    With your system, what happens when an employee has more plans than your table has columns to store them?

    Have a nice day!

  3. #3
    Join Date
    Mar 2004
    Posts
    64

    not for storage, just for data manupulation

    This is not for database design and not for storage.

    I would like to combine them into one row for each employee in order to use mail merge.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Why not use a report / subreport for your mail merge?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Mar 2004
    Posts
    64

    thanks

    thank you, I don't know there is a subreport in word mail merge.

    But a good reminder though, I suddenly remember all this can be handled easily in crystal report.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    There isn't a subreport in a WORD mail merge... well actually there might be but I am unaware of it... I was referring to ACCESS reports and subreports. There is almost no reason to use Word to do a mail merge with an Access database when Access reports do essentially the same thing by default design.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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