closing tag is in template navbar
timefactors watches



TZ-UK Fundraiser
Results 1 to 17 of 17

Thread: Database Folk - Sanity Check

  1. #1
    Master
    Join Date
    Jan 2010
    Location
    Coming Straight Outer Trumpton
    Posts
    7,095

    Database Folk - Sanity Check

    I wonder if anyone familiar with databases could give me a sanity check?

    I have a excel document with around 100 columns the data is from a survey tool, I need to get multiple answer columns into one, I also need to make the adjacent column equal the question posed which is the column label in the source xsls.

    I have a manual way to do this but is a pain as there are 20 + columns and there not contiguous.


    As we are a mac household I have been considering using mysql but before committing the time to learn this I wanted to check if its posable?


    The output I'm after would look like:
    Column1 some demographic data,
    Column2 some demographic data,
    Column3 Answer,
    Column4 Question (This is the column name from source of the previous column),
    Column5 Question # (I'm assuming a second table and look up Column4 and return its question #),


    Ideally I could have a single query that would do this for multiple answer columns, e.g. columns 4,6,9,12,23,24 all are free form text responses, hence the adjacent report column is the question so it can be evaluated by a human.

    I'd then export this back to excel for someone else to analyse and mark as relevant or not.


    db's aren't my thing (obviously) so apologies if I've asked a clunky question or used the wrong terminology.

    TIA for any and all replies.

  2. #2
    Its difficult for me to picture. Is it 20 columns and 100 rows, or actually 100 columns with data on.a single line? If your data table is 3rd normal form, you should be able to do something with a pivot table. If your data isnít, then the data model is probably questionable.
    If you can send over a sample, I could take a look. I expect that there are others in here more knowledgeable who could help you.

  3. #3
    Master
    Join Date
    Jan 2010
    Location
    Coming Straight Outer Trumpton
    Posts
    7,095
    so I hit my first issue which is our Mac’s are not on the right version of macOS to allow MySQL and current we are tied to current os due to other software compatibility.

    When I get the full data set it would be aprox 90 columns that are question fields, the questions are a mix of quantitative and qualitative responses.
    In this case I’m expecting there will be ~50 respondents so ~50 rows.

    I need to split the quantitative and qualitative answers and handle them differently.

    For the qualitative data I need the first two columns as these are demographic data, then the first qualitative column, then the header cell of the first qualitative column as this gives the question, finally the question number.
    This gives the first 50 rows of the qualitative report, then the next 50 rows would be the same thing but the second qualitative column And associated question and question number.

    In the end the report would be 5 columns by ~1000 rows

  4. #4
    Master Xantiagib's Avatar
    Join Date
    Mar 2010
    Location
    Gibraltar
    Posts
    2,482
    can you do a smaller table with similar examples of the manipulation you want? I don't quite understand what you need.

    Should all be possible with just excel using HLOOKUP and VLOOKUP (it can span different sheets in different files too)

    or consider microsoft access

  5. #5
    Grand Master Griswold's Avatar
    Join Date
    Feb 2005
    Location
    Yorkshire, England
    Posts
    16,395
    Quote Originally Posted by Xantiagib View Post
    can you do a smaller table with similar examples of the manipulation you want? I don't quite understand what you need.

    Should all be possible with just excel using HLOOKUP and VLOOKUP (it can span different sheets in different files too)

    or consider microsoft access
    I was just about to suggest that too. It's a much more qualified database manager than EXCEL. VBA and SQL can be better used in ACCESS as can Queries across multiple linked tables.
    Best Regards - Peter

    I hate being bipolar, its brilliant.

  6. #6
    Quote Originally Posted by Griswold View Post
    I was just about to suggest that too. It's a much more qualified database manager than EXCEL. VBA and SQL can be better used in ACCESS as can Queries across multiple linked tables.
    AFAIK Access isnít available on Macs (unless running Windows but thatís further complication).

    Hard to see exactly what is required but canít OP start another sheet and have functions to drag in the columns you want in the correct order etc.

  7. #7
    Grand Master Griswold's Avatar
    Join Date
    Feb 2005
    Location
    Yorkshire, England
    Posts
    16,395
    Quote Originally Posted by Kingstepper View Post
    AFAIK Access isnít available on Macs (unless running Windows but thatís further complication).

    Hard to see exactly what is required but canít OP start another sheet and have functions to drag in the columns you want in the correct order etc.
    I wasn't aware of that, not being a Mac user.

    It is possible to use VBA behind the scenes within EXCEL but it is not a straightforward as it is with linked tables and Queries in ACCESS. Pivot Tables, as suggested by someone earlier may be a possible solution; but in the end I don't think EXCEL is the best solution for what the OP is trying to achieve. Is there an equivalent DB program to ACCESS in the Mac world?
    Best Regards - Peter

    I hate being bipolar, its brilliant.

  8. #8
    Quote Originally Posted by Griswold View Post
    I wasn't aware of that, not being a Mac user.

    It is possible to use VBA behind the scenes within EXCEL but it is not a straightforward as it is with linked tables and Queries in ACCESS. Pivot Tables, as suggested by someone earlier may be a possible solution; but in the end I don't think EXCEL is the best solution for what the OP is trying to achieve. Is there an equivalent DB program to ACCESS in the Mac world?
    Not Mac user either but looked into this when thinking of switching. Think there may be one with OpenOffice.

  9. #9
    Master
    Join Date
    Jan 2010
    Location
    Coming Straight Outer Trumpton
    Posts
    7,095
    Thanks for all the replies I havenít worked through them all yet but they are appreciated and Iíll dig into them over the weekend.

    After a aborted attempt to use awk (Iíd forgotten how much a pain moveing files from csv and Linux/macOS cod line and EOL markers can be...) Iíve fallen back to writing a macro in vba and its a touch klunky but does the job.

    I still suspect that running this via a db would be better long term but my short term need is covered.

  10. #10
    Master
    Join Date
    Dec 2008
    Location
    Surrey, UK. OdiŠxere or somewhere in-between
    Posts
    7,683
    Blog Entries
    19
    I can’t quite fathom what you are wanting - I have many years experience administering databases of various types - both structured and unstructured data. If you did want a simple relational database like MYSQL you can download the community edition (for free) from the website - you can run it on macOS or Linux or windows.

    https://dev.mysql.com/downloads/mysql/

    if that is not compatible with your macOS (saw your comment above ).

    One we used with a lot of success is this one:

    https://www.postgresql.org/download/macosx/

    Excellent product and will run on 10.7 and above (from their literature).

    designing your database is key to success.

    i don’t quite follow you saying Col 4 heading is a data value - please explain more?

    marty.
    Last edited by MartynJC (UK); 6th December 2019 at 22:11.

  11. #11
    Craftsman
    Join Date
    Jan 2019
    Location
    Ireland
    Posts
    340
    ill add to the request of an example of what you require using just 3 or 4 records, its difficult to visualize with the current descriptions, and i cant be sure what i am visualizing is what you are trying to articulate.

  12. #12
    I'm not a db programmer but I have a few db's (mysql) running on my Linux servers for various tasks.

    When I had to put together my own 'personal' db a year or so ago to handle roughly 2,000 rows by 200 columns I just used sqlite3 which is found as standard I believe on OS X. As far as I know it is SQL compatible with mySQL/mariaDB but you won't need to install it - it's probably plenty quick enough for your task.

  13. #13
    Master
    Join Date
    Jan 2010
    Location
    Coming Straight Outer Trumpton
    Posts
    7,095
    To respond to those who are interested in visualising my data set here are some images and Iíve also made the files available on google drive if your really interested.


    Sample Source File
    The source file (Sample-survey-export.xlsx) is truncated in both row and columns but should give you a jist of the source data. The actual data set runs around 90 columns by 55 rows.

    Column Description:
    A = ID
    B = Demographic Question
    C = Demographic Question
    D = Quantitative Question
    E-H = Qualitative Questions
    I = Quantitative Question
    J-M = Qualitative Questions

    Handling and extracting the Quantitative answers is easy, the qualitative answers are the harder set.

    Sample-survey-export.xlsx



    Heres the qualitative answer report I generate via an macro in excel.

    Column Description:
    A = Count Column so I can return to original sorting if needed
    B = Demographic Answer
    C = Demographic Answer
    D = All the qualitative answers in a single column
    E = The question posed to the answer in column D
    F = Question Number
    G = Include
    H = Possibly Include

    This report allows a human to review the responses to each question and mark those that should be included in a report and those that might be of interest but need more though.



    Heres the google drive link for those really interested.
    https://drive.google.com/open?id=1JP...GVOieHuNJ0Jakk


    Hopefully that will allow you to visualise the challenge if not let me know and Iíll try and clarify

  14. #14
    Master
    Join Date
    Jun 2007
    Location
    Berkshire, UK
    Posts
    3,805
    You should be able to find a version of MySql for your version of MacOs here: https://downloads.mysql.com/archives/ if that is a path you still want to pursue.

    Just installed a version compatible with High Sierra on my Mac.

  15. #15
    Craftsman
    Join Date
    Dec 2014
    Location
    The East
    Posts
    848
    Blog Entries
    1
    Can't you just pivot it but leave the sum/count off? It may be a bit repetitive dragging the feilds but shouldn't take too long.

    Otherwise I think it may be possible anyway using to index & indirect functions.

    I don't mind having a look if you like but I am away at the moment so may not be able do anything until the middle of next week.
    95% of my working life in the last 15 years has been solid excel use but I am still learning new tricks.

  16. #16
    Craftsman
    Join Date
    Dec 2014
    Location
    The East
    Posts
    848
    Blog Entries
    1
    Also, you mentioned a hundred columns, but how many rows?

  17. #17
    Master
    Join Date
    Jan 2010
    Location
    Coming Straight Outer Trumpton
    Posts
    7,095
    Quote Originally Posted by jmitch View Post
    Can't you just pivot it but leave the sum/count off? It may be a bit repetitive dragging the feilds but shouldn't take too long.

    Otherwise I think it may be possible anyway using to index & indirect functions.

    I don't mind having a look if you like but I am away at the moment so may not be able do anything until the middle of next week.
    95% of my working life in the last 15 years has been solid excel use but I am still learning new tricks.
    around 90 columns by 55 rows.

    No need to as I said Iíve written a macro to do it now, Iím thinking of a dB for future runs, but we might also move the survey to a different platform so I may well wait until thatís settled. Thanks for the offer though.

Posting Permissions

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