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!!!