Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Crosstab query help (A2k)

    I want to use fields as row headings, and field values as column headings. There are only 5 possible column headings. The database is set up for a survey, of which there are 18 questions, and only 5 possible answers for each question (the numbers 1-5). I have the table set up with 18 fields (Q1-Q18), and each field uses combo box selection. I want my crosstab to look like this:


    <table border=1><td> </td><td>1</td><td>2</td><td>3</td><td>4</td><td>5</td><td>Total</td><td>Question 1</td><td>10</td><td>0</td><td>3</td><td>7</td><td>2</td><td>22</td><td>Question 2</td><td>5</td><td>5</td><td>7</td><td>3</td><td>2</td><td>22</td></table>
    And so on... <img src=/S/help.gif border=0 alt=help width=23 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab query help (A2k)

    Nothing?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Crosstab query help (A2k)

    It's an unfortunate design to use a separate field for each question. It would have been much better to use a separate table in which each answer is a separate record:

    <table border=1><td align=center>SurveyID</td><td align=center>QuestionID</td><td align=center>Reply</td><td align=center>1</td><td align=center>1</td><td align=center>5</td><td align=center>1</td><td align=center>2</td><td align=center>5</td><td align=center>1</td><td align=center>3</td><td align=center>2</td><td align=center>1</td><td align=center>4</td><td align=center>3</td><td align=center>1</td><td align=center>5</td><td align=center>1</td><td align=center>1</td><td align=center>6</td><td align=center>3</td><td align=center>...</td><td align=center>...</td><td align=center>...</td></table>
    It would then have been a simple crosstab query. If possible at all, you should switch to this design.

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab query help (A2k)

    Okay, I'm still in the development stage, so it's not a huge shot to my motivation. I'll run with that. Thanks Hans.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab query help (A2k)

    New issue: after creating a table for each question, I'm stuck with 55 tables! When I try to base my form off a query, it says that the query is too complex.

    (Query SQL attached).

    Additional thought: would creating a separate query for each question be feasible, and would it be an effective design?
    Attached Files Attached Files
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Crosstab query help (A2k)

    OK, now for my tu'pence worth. I did a survey database some moons ago where I used option groups to ascertain the correct value 1 through 5 for your example. You just need 18 fields corresponding to the 18 questions you want. As each survey is created then a new record is created.

    In Excel I connected to the database and then extracted/refreshed the data into a worksheet and then created pivot tables against the imported data. This Excel workbook was connected through the network to the people needing the report and each time they opened it the data refreshed.

    I have done similar active datasets for management information via my SQL servers sessions tables to extrapolate data so that I can graph them. By far easier than this somewhat horrendous SQL statement
    Jerry

  7. #7
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab query help (A2k)

    Any ideas to keep it as an Access report? Am I going to run into issues with the amount of data queried? Also; there are some fields that are for comments...I'm going to have to find a way to include those in the report.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Crosstab query help (A2k)

    No sorry, I would still do it in Excel....expedience of service I'm afraid...needs must when the Devil drives... <img src=/S/yadda.gif border=0 alt=yadda width=15 height=15>
    Jerry

  9. #9
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab query help (A2k)

    I understand your "dirty work" <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> and have succumbed to the needs of the devil; however, this time is different...I've got the time to set it up as I please, and on my own schedule.

    Aside: my own schedule = until the single data entry clerk who is *not* a touch typist completes inputting the data in to my first revision of the database. I will parse the data into my final scheme later on. The clerk has about 2000 paper questionnaires to input, and averages (at best) 100 a day.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Crosstab query help (A2k)

    Noooo....!

    You shouldn't create a separate table for each question! See for example <post:=607,991>post 607,991</post:>.

  11. #11
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab query help (A2k)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>

    I suppose my question now is, (since seemingly that table will grow by 50+records each time the survey is taken), when do I have to worry about bloat?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Crosstab query help (A2k)

    The records will be very small - each will take up only a few bytes. So bloat shouldn't be a problem.

    As always, it is a good idea to compact and repair the database from time to time.

  13. #13
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab query help (A2k)

    Hans,

    Any idea of an 'easy' way to transform the data from the 'one table' design into the multiple table design?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Crosstab query help (A2k)

    If you search this forum for normalize you should find some examples. Also see [url=http://www.rogersaccesslibrary.com/download3.asp?SampleName=NormalizeDenormalize.mdb]Normalize/Denormalize/url] on Roger's Access Library site. You'll have to fine tune any code you find for your situation.

Posting Permissions

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