Results 1 to 6 of 6
  1. #1
    Michael.S90 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2017
    Posts
    2

    How to make a proper crosstab query?

    Hi guys,



    My question is the following:

    - I have a hughe database of about 5 thousand records. It is for a research I'm doing. I have 5000 patients, 2500 are controls who received placeboand 2500 are the intervention group who received painmedication.
    -I did measurement for both groups at t=3 and t=0 (0 means before start of the research and t=3 means 3 months after they got placebo or painmedication)
    -I have one questions in this database: 1. Severity of pain (0-10)

    My question is: what is the best way to calculate the differences for each person during t=3 en t=0? Should I make crosstab query to do this easily or should I do something else?
    I'm kind of stuck here and I hope someone can help. So for example: Person X had had placebo medication and at t=0 he scored his pain with 10 and at t=3 he scored it with a 5, so I want to be able to calculate the difference easily with use of Access and Excel. In this case the difference is -5 (pain went from 10 to 5). How can I calculate this easiest? I really hope someone can help.

    Thanks for reading,

    Michael

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So the 10 score is in one record and the 5 score is in another. You need a query that will find the previous record. Review http://allenbrowne.com/subquery-01.html#AnotherRecord

    FYI, that is an extremely small db.
    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
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You could make a 'report' table.
    person, t0, t3

    make queries to append to it,
    qaT0, to append data to the report table for T0.
    qaT3, to append data to the report table for T3.

    then a query, qsResults, to sum the columns (by person) and calc the difference.

  4. #4
    Michael.S90 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2017
    Posts
    2
    Thanks for your reply.
    I still don't really understand the concept of a report table. Do you have any links so I can figure this out?

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Another name for this is 'temp' table. Table is permanent but records are temporary. I have used this technique for complex data output that can't be managed with queries alone. I delete records from the 'temp' table prior to each use. If db is split, temp table goes in frontend.

    But if your data is identified by a T attribute, meaning there is a field that has a 0 or 3 value, then might not need either a 'report' or 'temp' table or even Allen Browne's nested query.

    Does each patient have only 1 pair of records?

    Provide an example of your data. Maybe it is already compatible with CROSSTAB or GROUP BY.

    Here is more guidance from Allen about crosstabs http://allenbrowne.com/ser-67.html
    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.

  6. #6
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    You can also look at this thread: https://www.accessforums.net/showthread.php?t=66158

    The db attached is for getting the mileage used between gas fill-ups. But the query for that would be the same concept for the changes in pain tolerance.

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

Similar Threads

  1. Proper Grouping Of A Totals Query
    By Tambe257 in forum Queries
    Replies: 1
    Last Post: 03-24-2017, 04:29 PM
  2. Replies: 3
    Last Post: 10-24-2014, 12:15 PM
  3. Replies: 1
    Last Post: 07-10-2013, 12:43 PM
  4. Replies: 1
    Last Post: 07-30-2010, 10:28 AM
  5. make a temp table from crosstab query
    By stigmatized in forum Programming
    Replies: 0
    Last Post: 07-26-2010, 03:01 PM

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