Results 1 to 9 of 9
  1. #1
    RE_ is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2022
    Posts
    23

    Duplicate values and export them in excel

    Hi,



    In the attached db I have a table showing employees effort on 2 different projects.

    Assuming I am a project manager for project A, I would like to check what's the total effort for employee "10097015" in the database. I would like the effort for employee 10097015 on project B be duplicated and shown "under" the first value (and vice versa for project B where eventually I'll show his effort on project A). This will get repeated for all employees and again repeated if an employee is listed twice on a project (because he is busy on 2+ activities - like employee "10097013" on project A).

    Ideally I would like to export the results in excel and group all duplications under the "original" line.

    The attached spreadsheet shows what I would like to achieve. The million dollar question is: Is it actually achievable?!?!?

    Thanks!
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Most anything is possible with enough code.

    I really don't understand what you mean by 'duplicated'. The worksheet has same records as table. Looks like records are ordered by project with employee 15 first. So what rule determines this?

    BTW, this table structure is not normalized.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    RE_ is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2022
    Posts
    23
    Quote Originally Posted by June7 View Post
    Most anything is possible with enough code.

    I really don't understand what you mean by 'duplicated'. The worksheet has same records as table. Looks like records are ordered by project with employee 15 first. So what rule determines this?

    BTW, this table structure is not normalized.
    Hi June7,

    Sorry, In the spreadsheet I attached previously I grouped together the results that I would like to achieve (under the + symbols on the left) because eventually this is how I would like to present it.

    I have now attached the spreadsheet with the results ungrouped. Basically the white rows are those taken from Query1. Those highlighted in yellow are the additional rows that I would like the db to generate. These yellow rows show the full employee's effort (on other projects and/or same project but different discipline) every time the employee is listed under a project. Hope it makes more sense now.

    Thanks!
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Okay, could have just told me to expand the groupings instead of attaching again. I should have seen that but was tired.

    Nope, logic of these 'groupings' still escapes me. For instance, emp_code 15 has 4 rows with the same weekly data - same 2 pairs of project and discipline just reversed in order in two areas of sheet.

    I very much doubt SQL alone can accomplish and VBA will be needed, perhaps writing records to a 'temp' table and then exporting that table to Excel.

    The worksheet has 13 data rows, consider the following query which generates 13 rows.

    SELECT Query1.emp_code, Query1.Discipline, Query1.Project, tbl_staffing_plans.plans_week1
    FROM tbl_staffing_plans INNER JOIN (SELECT tbl_staffing_plans.emp_code, tbl_staffing_plans.Discipline, tbl_staffing_plans.Project
    FROM tbl_staffing_plans) AS Query1 ON tbl_staffing_plans.emp_code = Query1.emp_code;

    However, I see no way for query to order and 'group' records as shown in Excel. Again, the Excel logic escapes me.

    If you can describe the decision process for manually creating these additional records, that can be codified. So, as you look at each record in table, what evaluations and decisions would you do? In other words, why did you organize worksheet the way you did? I can code steps without understanding the ultimate goal.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    RE_ is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2022
    Posts
    23
    ahah sorry! I'll give you a real-life example:

    We have various Project Managers managing various different projects. Within each project, there are various disciplines.
    For simplicity, let’s say there are only 2 Projects – Project A and Project B, and there are only 2 Disciplines within each Project – Discipline 1 and Discipline 2. Project A is managed by Project Manager A, and Project B is managed by Project Manager B. (In reality, each Project Manager would be managing several different Projects at the same time.)
    I need to create a report that is tailored to each individual Project Manager showing all the employees working on the Project Manager’s projects. The employees within this report need to be ordered first by Project and then by Discipline within each Project.
    Each employee could be working on several different Projects at the same time, and for the same Project, the employee could also be involved in different Disciplines.
    In my report for Project Manager A, I would like to show by default (i.e. in the white rows) all the employees working on Project A, first under Discipline 1 and then under Discipline 2. If an employee is involved in both Discipline 1 and Discipline 2 for Project A, then this employee would be listed twice in this report under each Discipline – this is intentional.
    However, this employee could also be working on Project B at the same time. Because Project Manager A is not responsible for managing Project B, I don’t want to show this employee’s involvement in Project B by default as this would be confusing for the Project Manager. But I would like to give the Project Manager the ability to ‘drill-down’ into each employee within the report if they want to see everything else the employee is working on – and show the employee’s involvement in other Projects in yellow.
    So let’s say there is an employee that is simultaneously working on both Project A and Project B, and is involved in both Discipline 1 and Discipline 2 for both Projects. In the report for Project Manager A, it should show this employee listed under Project A/Discipline 1 and then again under Project A/Discipline 2, both in white coloured rows. When I ‘expand’ on this employee’s listing under Project A/Discipline 1, the report should reveal in yellow coloured rows (below the white row) the employee’s involvement (hours per week) in Project A/Discipline 2, Project B/Discipline 1 and Project B/Discipline 2 respectively. Then, when I ‘expand’ on this employee’s listing under Project A/Discipline 2 within the same report, it should then reveal in yellow the employee’s involvement in Project A/Discipline 1, Project B/Discipline 1 and Project B/Discipline 2 respectively.
    But if I don’t click to ‘expand’ on an employee, the yellow rows should remain hidden within the report.
    Hope the above all makes sense?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    See if this gets you near what you want. Open report and right click Export to Excel.
    Attached Files Attached Files
    Last edited by June7; 07-01-2022 at 01:39 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    RE_ is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2022
    Posts
    23
    Hi June7,
    Thank you so much for getting back to me.
    It’s great how you have created the grouping in excel using this “trick” in the reports – very nice!
    This is getting close but not 100% there yet – ideally I would like this report to be project-centered rather than employee-centered. When I export the results in excel and select the 2nd grouping, I can only see the emp_code and emp_name columns – the Project, Discipline and plans_week1 columns are only revealed when I select the 3rd grouping. Ideally, I would like all of this information (i.e. emp_code, emp_name, Project, Discipline and plans_week1) visible in the same row (basically in a similar format to the one attached in my first post).
    Also – under the ‘A2’ GrpID in your report, Discipline 2 (Project A) should be the one that is shown by default for the employee.
    I really appreciate all your help so far! I would try to figure this out myself but I’m a bit out of my depth here and really don’t know where to start.
    Thanks again.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You can export the ProjectGroups temp table or the ProjectGroupsORDER query to Excel then do what you want to arrange/group/color/collapse rows. Writing code to manipulate Excel to that end is more than I want to tackle - you are on your own there.

    As far as assigning a sort priority to records within each group, try this query statement:

    SELECT ProjectGroups.GrpID, ProjectGroups.EmpCode, tblEmployee.EmpName, ProjectGroups.Project, ProjectGroups.Discipline, tblStaffingPlans.plans_week1
    FROM tblEmployee INNER JOIN (tblStaffingPlans INNER JOIN ProjectGroups ON tblStaffingPlans.RecID = ProjectGroups.PlansRecID) ON tblEmployee.EmpCode = ProjectGroups.[EmpCode]
    ORDER BY ProjectGroups.GrpID, ProjectGroups.EmpCode, IIf([GrpID] Like [ProjectGroups].[Project] & "*",0,1), IIf([GrpID] Like "*" & [ProjectGroups].[Discipline],0,1), ProjectGroups.Project, ProjectGroups.Discipline;


    Unfortunately, the report is now broken. Still looking at but maybe moot since you didn't like the export result.
    Last edited by June7; 07-01-2022 at 03:00 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    RE_ is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2022
    Posts
    23
    Quote Originally Posted by June7 View Post
    You can export the ProjectGroups temp table or the ProjectGroupsORDER query to Excel then do what you want to arrange/group/color/collapse rows. Writing code to manipulate Excel to that end is more than I want to tackle - you are on your own there.

    As far as assigning a sort priority to records within each group, try this query statement:

    SELECT ProjectGroups.GrpID, ProjectGroups.EmpCode, tblEmployee.EmpName, ProjectGroups.Project, ProjectGroups.Discipline, tblStaffingPlans.plans_week1
    FROM tblEmployee INNER JOIN (tblStaffingPlans INNER JOIN ProjectGroups ON tblStaffingPlans.RecID = ProjectGroups.PlansRecID) ON tblEmployee.EmpCode = ProjectGroups.[EmpCode]
    ORDER BY ProjectGroups.GrpID, ProjectGroups.EmpCode, IIf([GrpID] Like [ProjectGroups].[Project] & "*",0,1), IIf([GrpID] Like "*" & [ProjectGroups].[Discipline],0,1), ProjectGroups.Project, ProjectGroups.Discipline;


    Unfortunately, the report is now broken. Still looking at but maybe moot since you didn't like the export result.
    I can definitely format the excel file by myself and I also believe that I can use the last query statement to do the trick. I'll let you know. Thanks!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 05-22-2019, 02:09 PM
  2. How to Upload Excel File with Duplicate Values
    By Senor Penguin in forum Access
    Replies: 12
    Last Post: 09-28-2018, 12:36 PM
  3. Export to Excel results in new decimal values
    By matt704 in forum Import/Export Data
    Replies: 8
    Last Post: 03-10-2017, 10:50 AM
  4. Replies: 1
    Last Post: 05-12-2015, 02:34 PM
  5. export subforms values to excel
    By darwish in forum Access
    Replies: 2
    Last Post: 04-29-2014, 01:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums