Results 1 to 6 of 6
  1. #1
    aha121 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    2

    A simple way to query normalized data into long format for reporting?


    Hi

    I'm fairly new to the concept of normalizing my database and have a quick question regarding getting normalized data into long format for reporting. For example, we collect a bunch of different measurements on birds, and what we collect is species dependent. Therefore, I'd like to simply create a table of measurements; the columns would be something like 1) autonumber (primary key) 2) foreign key (to link back to a specific capture event) 3) measurement type (there are probably about 25 different types, but again, only a few that we take for each foreign key [ie capture event]), 4) measurement (the actual length/width/mass/etc numerical measurement), and 5) units (ie was it in mm, cm, g, kg, etc).

    But for reporting, I need to get these into long format. For example, for capture event 001 (ie the foreign key) we measure bill length, head length, and body mass. Those are stored in 3 rows in our Measurements table. For capture event 002 (ie the foreign key), we measure bill length, head length, and tail length. Those are stored in 3 rows in our Measurements table.

    So I'd like to create a query that reports all the measurements in a single row, and the columns for each measurement. I can easily do this by creating a bunch of sub-queries for each measurement type, but this seems overly tedious. Is there a better way?

    Thanks much for any help anyone can offer!!!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Play around with the crosstab query wizard and see if you get the desired result.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    aha121 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    2
    Maybe I'm missing something, but I cant seem to get the crosstabs option to work (I had tried that originally, then tried it again). The key is I dont want compiled info or average, just the raw data, but switched from short to long format.

  4. #4
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Sometimes (frequently) you can use Max or Min with a crosstab to simply display the value "as is" if the grouping of the other fields means it is a unique value.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Minty has probably answered your question, but if you're still stuck perhaps you can attach a sample db here we can play with?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by aha121 View Post
    So I'd like to create a query that reports all the measurements in a single row, and the columns for each measurement
    Will each "Capture event" always have 3 records in the Measurements table?
    You might try writing a UDF to concatenate the readings into a single row for each capture event. Check out Allen Browne's site for Concatenate values from related records

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

Similar Threads

  1. Reporting In Grid Format
    By DiscoverIT in forum Reports
    Replies: 4
    Last Post: 04-25-2017, 10:56 PM
  2. Replies: 7
    Last Post: 09-19-2014, 07:29 AM
  3. Replies: 6
    Last Post: 09-11-2013, 11:32 PM
  4. Replies: 11
    Last Post: 03-26-2013, 05:34 PM
  5. Replies: 6
    Last Post: 04-18-2010, 03:41 AM

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