Use same list for multiple field names

[MichaelN86]

I'm relatively new to MS Access (2013) and I'm trying to make a database that can be used to input data from transect surveys.

To lay out my design hopes: I have a form that has spaces for 50 observations (an observation made every 0.5 meters along a 25 meter transect). Each observation can only have one option and must be restricted to a list of only 15 choices. At the moment, I have a table design that has 50 field names (Obs1, Obs2....Obs50). I was going to just have the data type be a 'lookup' (of the available choices from another table 'tblBottomType' that can still be customizable if more options need to be added) but this seems very redundant since its creating 50 separate relationships. Any suggestions on how to simplify this?

 

[Micron]

Looks like you are thinking of this with a spreadsheet bias. Your observations should be in rows, not columns. That's the concept of database design. It might be a good idea for you to research relational database design before you get too deep, but I will offer suggestions to get your thoughts on track (using your clues). My name choices might not be the best.
I think you should have tblObservations, tblChoices and tblTransects to start with. tblChoices holds your 15 rows - if you add/subtract in future, your form control would pick up the changes automatically and your related tables would not be impacted if set up properly. tlbTransects needs some sort of meaningful ID that you use to identify the transect (test). An autonumber ID field would be optional, but I don't usually use them. I prefer to use a primary key (TransectID?) - your choice as to whether or not you make this a unique primary or index, depending on whether or not you duplicate the measurements on the same transect in another 'test'. tblObservations: holds the observationID and transectID on each row, along with the observation value in the next field. This means that if there are 10 observations, the transactID would be repeated 10x (but each row would have a different observationID), along with the observed value. Some might advise to put 15 fields and one row per record, but that is not truly normalized, I think. IF the situation was that some tests had 5 and some 15 observations, many of the record fields would be blank. Again, the db is supposed to be row based as opposed to field (column) based. Anyway, that's how I see it at a glance, although I might not have written it accurately enough. If your research on relational db's doesn't help enough, I'm sure I or others would be able to expound on this. Perhaps even layout some table/field suggestions if need be.

 

[MichaelN86]

Thanks Micron! This is a great start. I knew better..but wasn't thinking...that I should be working in rows, not columns. The end goal is to have it exported to excel which may be why I didn't connect the dots realizing I was in the wrong mindset!

 

[Micron]

You're welcome. If you will be charting or creating pivot tables with this data, exporting to Excel makes sense. If just using simpler aggregate summaries (count, total, min, etc) then maybe not. You may find that you will need to transpose data from rows to columns for Excel purposes (and I've never liked creating crosstab queries!).