none
copy data and change part of it RRS feed

  • Question

  • Hello,

    I need help with a Query I'm trying to build.

    I have a SharePoint Site with linked tables. I have a list of Manager Handbook controls, I need to report on who "completed" the controls, some of them are Business Specific and some are Common Controls (meaning they have to be completed by each business unit).

    What I was thinking is to create a Query that will allow me to isolate "Common controls" ("All Executives") and replace(/or add) with 5 entries which would reflect each executives, in order to work with another Query that I made (I need to have each of the "All Executives" counted once for each department).

    Is there a way in access to do that, whether it's through a Query or something on Access or with a workflow on SharePoint, I just need to keep the original Database as it is.

    Thursday, August 17, 2017 4:39 PM

Answers

  • I need to figure out how to change those called "All" and split them in 5 different departments


    To return five separate instances of the 'All' rows, assigning each to a specific department, you can use a Counters table, which is simply a single column table with values from 1 to at least 5.  You can then use UNION ALL operations to return single instances of the other rows and five of each of the 'All' rows, assigning a department name as a constant in each case, e.g. in simplified form:

    SELECT Department, SomeColumn, SomeOtherColumn
    FROM YourTable
    WHERE Department <> "All"
    UNION ALL
    SELECT "Department 1", SomeColumn, SomeOtherColumn
    FROM YourTable, Counters
    WHERE Department = "All"
    AND Counter = 1
    UNION ALL
    SELECT "Department 2", SomeColumn, SomeOtherColumn
    FROM YourTable, Counters
    WHERE Department = "All"
    AND Counter = 2
    UNION ALL
    SELECT "Department 3", SomeColumn, SomeOtherColumn
    FROM YourTable, Counters
    WHERE Department = "All"
    AND Counter = 3
    UNION ALL
    SELECT "Department 4", SomeColumn, SomeOtherColumn
    FROM YourTable, Counters
    WHERE Department = "All"
    AND Counter = 4
    UNION ALL
    SELECT "Department 5", SomeColumn, SomeOtherColumn
    FROM YourTable, Counters
    WHERE Department = "All"
    AND Counter = 5;


    Ken Sheridan, Stafford, England

    • Marked as answer by JessicaL01 Monday, August 21, 2017 6:29 PM
    Monday, August 21, 2017 6:06 PM

All replies

  • Hi Jessica,

    Are you saying you have SharePoint Lists linked in Access? Are you creating the query in Access? What does your other query look like and how do you want to combine it with this new query you want to create?

    Thursday, August 17, 2017 4:42 PM
  • Yes SharePoint list linked in Access, yes Creating the Query in Access.

    to provide a bit more context,

    - I have multiple SharePoint Surveys that are copied in to a Custom list thanks to a workflow.

    - I need to keep track of the results of said survey (Satisfactory, Requires improvement) and if it wasn't answered (which means there would not be any entry for that business/control)

    - I was able to create queries that provides me with each of the departments' results, however I have an extra department (All Executives) and because the survey has not been completed it doesn't count whoever didn't complete it as a "No answer". it only counts under the "All executives" dept which isn't a dept.

    I'd like to be able to report which business didn't complete it. so far the results that are showing on there (satisfactory and Requires improvement) have the correct Business Unit under the Organizational chart. however if a department does fill out the survey I wish to be able to say "This dept didn't complete the common control Survey"

    hope this makes sense

    Thursday, August 17, 2017 4:50 PM
  • Hi Jessica,

    Thank you for the clarification. Sounds like you should be able to use the Find Unmatched Query Wizard for this.

    Give it a shot and let us know if it doesn't work.

    Cheers!

    Thursday, August 17, 2017 5:07 PM
  • Hello,

    Does Find Unmatched Query Wizard work?

    If it doesn't work, could you please share detail repro steps here?

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 21, 2017 6:04 AM
    Moderator
  • For what I can see (If I'm doing this right) it gives me all the controls that have no survey answered however I need to figure out how to change those called "All" and split them in 5 different departments. I actually loose the control if any department has completed it.

    Unfortunately I'm unable to duplicate the entries in the SharePoint Database, that's why I was hoping I could find a way to work around it.

    Basically, I have 27 of the controls that are "Common control", (needs to be done by all departments) and I'd like to be able to report the Common control to a specific department. but the Original database's departments include one called "All" I tried a couple of different unmatched and I can't seem to make it work.

    is there an expression I can use to split any control called "common Control" and have 5 different common controls with the same control #?

    Monday, August 21, 2017 5:32 PM
  • I need to figure out how to change those called "All" and split them in 5 different departments


    To return five separate instances of the 'All' rows, assigning each to a specific department, you can use a Counters table, which is simply a single column table with values from 1 to at least 5.  You can then use UNION ALL operations to return single instances of the other rows and five of each of the 'All' rows, assigning a department name as a constant in each case, e.g. in simplified form:

    SELECT Department, SomeColumn, SomeOtherColumn
    FROM YourTable
    WHERE Department <> "All"
    UNION ALL
    SELECT "Department 1", SomeColumn, SomeOtherColumn
    FROM YourTable, Counters
    WHERE Department = "All"
    AND Counter = 1
    UNION ALL
    SELECT "Department 2", SomeColumn, SomeOtherColumn
    FROM YourTable, Counters
    WHERE Department = "All"
    AND Counter = 2
    UNION ALL
    SELECT "Department 3", SomeColumn, SomeOtherColumn
    FROM YourTable, Counters
    WHERE Department = "All"
    AND Counter = 3
    UNION ALL
    SELECT "Department 4", SomeColumn, SomeOtherColumn
    FROM YourTable, Counters
    WHERE Department = "All"
    AND Counter = 4
    UNION ALL
    SELECT "Department 5", SomeColumn, SomeOtherColumn
    FROM YourTable, Counters
    WHERE Department = "All"
    AND Counter = 5;


    Ken Sheridan, Stafford, England

    • Marked as answer by JessicaL01 Monday, August 21, 2017 6:29 PM
    Monday, August 21, 2017 6:06 PM
  • I think this is exactly what I need!! Thank you!

    I know you pretty much detailed it for me but I have a couple of questions.

    1* A counters table, is that just a regular table where I put 1 to 5 in the first column, I end up with 2 (ID + Field 1)?

    2* Where do I do the Union all? is that from a new query?

    3* What am I suppose to put where you indicate "SomeColumn" or "SomeOtherColumn" ?

    Monday, August 21, 2017 6:28 PM
  • I need to figure out how to change those called "All" and split them in 5 different departments


    To return five separate instances of the 'All' rows, assigning each to a specific department, you can use a Counters table, which is simply a single column table with values from 1 to at least 5.  You can then use UNION ALL operations to return single instances of the other rows and five of each of the 'All' rows, assigning a department name as a constant in each case, e.g. in simplified form:

    SELECT Department, SomeColumn, SomeOtherColumn
    FROM YourTable
    WHERE Department <> "All"
    UNION ALL
    SELECT "Department 1", SomeColumn, SomeOtherColumn
    FROM YourTable, Counters
    WHERE Department = "All"
    AND Counter = 1
    UNION ALL
    SELECT "Department 2", SomeColumn, SomeOtherColumn
    FROM YourTable, Counters
    WHERE Department = "All"
    AND Counter = 2
    UNION ALL
    SELECT "Department 3", SomeColumn, SomeOtherColumn
    FROM YourTable, Counters
    WHERE Department = "All"
    AND Counter = 3
    UNION ALL
    SELECT "Department 4", SomeColumn, SomeOtherColumn
    FROM YourTable, Counters
    WHERE Department = "All"
    AND Counter = 4
    UNION ALL
    SELECT "Department 5", SomeColumn, SomeOtherColumn
    FROM YourTable, Counters
    WHERE Department = "All"
    AND Counter = 5;


    Ken Sheridan, Stafford, England

    I think I made it work, however I have no idea what I'm to do now, how do I use it?

    Monday, August 21, 2017 7:49 PM
  • I think I made it work, however I have no idea what I'm to do now, how do I use it?
    You said initially that you needed to report, so you can use the query as a report's RecordSource property, and then design the report to an appropriate layout to present the data.  If you need to bring in values from other tables, then you can join the UNION ALL query to those tables on the appropriate columns and return columns from both in the final result table.

    Ken Sheridan, Stafford, England

    Monday, August 21, 2017 9:57 PM
  • Hi Ken

    I just noticed that since I tried the Union All query my Organization unit field on all my other queries is blank, I deleted my Union All Query hoping it would bring that back but it didn't and now I'm freaking out. I'm a total novice in this stuff and I'm not that familiar/comfortable with SQL stuff. I am learning through research but I have no idea how to fix this.

    I have about 7 different queries using that information, can you help me?

    I really appreciate all your help here!! Thank you!


    Edit: I was able to fix the missing Organizational Units, however I still don't understand how to use that query
    • Edited by JessicaL01 Tuesday, August 22, 2017 1:58 PM update
    Tuesday, August 22, 2017 12:48 PM
  • I was able to fix the missing Organizational Units, however I still don't understand how to use that query
    If the query returns all the information you need for a report you can use the report wizard to create a report based on the query.  The wizard allows you to do things like grouping by one or more columns, so you don't get unnecessary repetition, and to control the order in which the data is returned.

    If the query does not return all the information you need for a report, you should be able to join it in another query to one or more other tables to bring in data from those tables, and then base a report on that query.

    More than that I can't say without knowing a lot more detail about the structure of your database and what you want to include in a report.


    Ken Sheridan, Stafford, England

    Tuesday, August 22, 2017 3:50 PM
  • Hi Ken, question for you.

    is it possible that my doing the "Union All" thing on 1 access report may affect the SharePoint database list it's linked with?

    I'm asking because since I've done this we are having issues with that list.

    when we try creating a report it's saying that the database is busy or being modified, However nobody is in it.

    if I am responsible, how can I revert that? I noticed that erasing the "Union All" query doesn't fix it.

    Thank you!!

    Wednesday, August 23, 2017 7:00 PM
  • is it possible that my doing the "Union All" thing on 1 access report may affect the SharePoint database list it's linked with?
    I'm afraid you'll have to address that question to someone else.  My knowledge of SharePoint is comparable to my ability to walk on water.  I've never used it, nor do I expect to.

    Ken Sheridan, Stafford, England

    Wednesday, August 23, 2017 8:09 PM