Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2007
    Posts
    3

    Unanswered: Move Rows to Columns

    Hi there,

    I've got a problem in my access db where I import a list of employee bank deductions and need to output to another format.

    I need to go from multiple lines per employee to one line per employee.

    In the attached sample, there are employees with varing number of deductions. The final output can only show the first 3 deductions.

    I've attached a small example of what I need it to do, either with queries or VBA. The table 'tbl_bank_deductions' is the original data as imported from the pay system. The table 'tbl_bank_deductions_final' is what where I need to get to. The final result can be either a table or query as it will be joined to a much larger query.

    Thanks heaps
    Wattch
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Took a very quick look at your data.

    Why would you want to do this?
    The original table is a much better design than your final table.

    Besides your new design doesn't even cover the small sample of data provided, look at employee #4, they have 4 records.

    I would encourage you to leave the data as it is and rethink your goal.

    Steve

  3. #3
    Join Date
    Aug 2007
    Posts
    3
    Thanks for the reply Steve,

    Why would you want to do this?
    I'm building a data migration tool to automate the conversion between my system (the original table) and what the client requires for their system (final table)

    The original table is a much better design than your final table.
    It sure is, this is only a small sample of the complete table structure and requirements but I can get help to do this part, then I can apply the same principle on a larger scale.

    Besides your new design doesn't even cover the small sample of data provided, look at employee #4, they have 4 records.
    I did this on purpose because in my system, there is no limit to the number of entries an employee could have but for the final result, it will be limited to 3.

    I would encourage you to leave the data as it is and rethink your goal.
    Unfortunately, I'm building this for a client and there specifications are not modifiable.

    I look forward to your response.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Your client should come to you with a problem to which you provide the solution - not the other way round!
    So I agree with Steve on this one - fight your corner.

    There is certainly a way to do what you want but I'm not a fan of pounding a nail: old shoe or bottle?
    George
    Home | Blog

  5. #5
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Part of your job as a developer is to produce the result the client is looking for. If they had the skill set and knowledge to design and develop the system on their own then they would not need you. They know what they want it to do and they have ideas on how you should do it, your job is to show them the correct way of creating the system to produce the results they want.

    If you don't do this and you just blindly follow their advice on how to design the system it will come back to haunt you. For example, when they request a query that cannot be generated due to their faulty design, you will still be expected to make it work.

    Steve

  6. #6
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Wattch, have you looked at crosstab query? Like Steve and George said, you should talk to your client because that final table will get scary after a while.
    Ryan
    My Blog

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    After a while?
    I'd argue that it's too scary to even implement
    George
    Home | Blog

  8. #8
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Gotta give the guy some hope, can't be like you suck, bye!
    Ryan
    My Blog

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by georgev
    There is certainly a way to do what you want but I'm not a fan of pounding a nail: old shoe or bottle?
    Case and point
    George
    Home | Blog

Posting Permissions

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