+ Reply to Thread
Results 1 to 11 of 11

Can Relationships and Tables Solve this Data Management Challenge?

  1. #1
    Registered User
    Join Date
    10-26-2013
    Location
    Denver, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Can Relationships and Tables Solve this Data Management Challenge?

    Hi, I'm the director of a nonprofit working in India and I have the following situation:
    • About 6,000 social workers in around 200 different organizations
    • Each social worker oversees 1-5 villages.
    • There are also senior leaders who oversee groups of these social workers and their respective villages.
    • We provide resources and educational programs in these villages and need to track which villages we conducted what program in when.
    • We also want to track numbers of people assisted, numbers of children participating in the educational programs, etc. in any given year.
    We currently have all the data entered but no way to work with it. For example each social worker has multiple rows, one for each village he oversees and there is really no way to work with the data properly or to show who the main coordinator is over certain social workers and villages. Is there is a way to format Excel to handle this kind of data? If not, what kind of a database do you recommend?
    thank you
    Leanna
    Last edited by Leanna; 10-26-2013 at 10:57 PM. Reason: title not specific enough

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,391

    Re: Can Relationships and Tables Solve this Data Management Challenge?

    Thanks for the title change

    Now, would it be possible to see a (clean) sample of the data you are working with, along with some examples of what your expected outcome would look like (and how you arrived at that, if is it not blatantly obvious lol)?

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    10-26-2013
    Location
    Denver, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Can Relationships and Tables Solve this Data Management Challenge?

    Thanks. I've prepared a snippet from some of our current data. There are many blank fields because much of the data will be collected once we figure out a proper way to handle it. I hope you can see from this that we first of all need a way to relate the Senior Leader/Supervisor column with his respective Social Workers so we don't need to re-enter the Supervisor info for every Social Worker's record. Each Supervisor of course will need his own record too.
    You'll also note that we need a way to record multiple resources provided to the social workers and the date it was given, and also multiple training programs attended by the social worker and the date.
    Please see attachment. Thank you VERY MUCH for your help!
    Leanna
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,391

    Re: Can Relationships and Tables Solve this Data Management Challenge?

    Thanks for the file. If you were to do this manually, what would the outcome look like? Perhaps you would walk me through what you want, and how you would do it (manually)?

  5. #5
    Registered User
    Join Date
    10-26-2013
    Location
    Denver, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Can Relationships and Tables Solve this Data Management Challenge?

    Ok I've tried to envision a few filters, please see attachment. Items match the columns of the spreadsheet I sent.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    10-26-2013
    Location
    Denver, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Can Relationships and Tables Solve this Data Management Challenge?

    Hi FDibbins, I haven't heard from you for some time. I would really appreciate if you can make some suggestions based on the file I had attached.
    thanks...

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,391

    Re: Can Relationships and Tables Solve this Data Management Challenge?

    Sorry, I had not been monitoring this thread I am out of time right now, but should be able to get back to it in a few hours (Posting this so it will show up in my list)

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,391

    Re: Can Relationships and Tables Solve this Data Management Challenge?

    Maybe I am just having 1 of those days, but I just cant seem to make head-way on this, so I have asked the other experts to take a look as well

  9. #9
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Can Relationships and Tables Solve this Data Management Challenge?

    Actually, as per your post (#5), why not make it(them) a table(s), or pivot table(s), then use the built in filters to get the info you want?
    It's not "impossible" formulaicly, just easier to use the built in features that are available
    Also why is the second Document a database rather than an excel sheet?
    I can definitely understand FDibbins feelings of confusion, as you do not seem to be sure what you want here...
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  10. #10
    Registered User
    Join Date
    10-26-2013
    Location
    Denver, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Can Relationships and Tables Solve this Data Management Challenge?

    Hi dredwolf, Actually we have a team member - Maneesh - who is pretty sharp with databases and he's the one that will be developing this spreadsheet. I'm going to ask him to share more specifics here as to what we envision. It may be a few days because he's in India and Diwali, the "festival of lights" is going on - I think second biggest holiday of the year. So maybe Tuesday we'll post again... thanks so much for your help and patience with us!

  11. #11
    Registered User
    Join Date
    11-02-2013
    Location
    India
    MS-Off Ver
    Excel 2013
    Posts
    3

    Re: Can Relationships and Tables Solve this Data Management Challenge?

    Hello All!,
    I'm Maneesh that Leanna was talking about. I've been asked by her to drop-in to this forum and take it from here.
    Actually, the real problem with our spreadsheet at the moment is, its got a lot of social workers who do not yet have an ID of some sort and its all in the same spreadsheet too. If we were to use Excel 2013's beefed up built-in capabilities and powerful tools for Data Analysis and Visual Representation, I think we can manage to extract the data and analyse it for our needs. Like for example, we could put all the social workers into a separate table and all the other information relating to that particular social worker into a separate table and then Relate the 2 tables based on a UNIQUE SOCIAL WORKER ID field (One-to-Many relationship), then this will both be manageable and workable.

    Then we can drill down and use Excel 2013's new Power View with Pivot Tables and Pivot Reports/Charts and do all the analysis we want!
    Now, the technical glitch is: -------->>>

    1. Our social workers change every year. They may or may not be the same person every year. So, if we give them an ID for this year and say there're gone the next year, how will we keep a track of their work ??

    2. The second glitch is there is really no way in Excel 2013, to uniquely define a Social Worker ID with a unique number. Meaning to say how can we be sure these ID's are not duplicated ?? I know Access is a database specific application and will never allow a duplication with ID's but with if we want to stick to Excel spreadsheets at the moment, we would have to strictly ensure (manually) that there are no IDs in there that get duplicated rather than Excel doing it for us.
    This is both manual and error prone. Can anyone help us understand how we should do this in Excel 2013 ???
    Greatly appreciate all help.
    ~~Maneesh~~

  12. #12
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Can Relationships and Tables Solve this Data Management Challenge?

    Well, easiest I can think of off the top of my head is a separate table for the worker IDs...ie cloumn a = worker Id number, Column B worker name, column C( if needed) social worker Id #,
    this gives you 3 points of reference to uniquely ID a particular worker

    Hope this helps

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1