Jump to content

referencing adjacent records


This topic is 7172 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I have a database that includes multiple records for the same event, each record providing data for a distinct category. I need to convert this so that the new database holds a single record for each event with enough fields to cover each category. (Hope this makes sense!) To illustrate the problem, I've provided two simple datasets below, the first equivalent to what I have and the second being what I'd like to have. If it helps, the traps are light traps that capture flying insects, each trap collects multiple species (spp) and, often, multiple individuals within a species.

I've tried to create a database with a list of unique traps (eg trap01, trap02, etc) and then to link the contents of each spp field to the original dataset, but I think I'm about as lost as these insects, battering themselves inside the traps! Any suggestions as to how to proceed would be appreciated!

Thanks, Pete


trap01 a 3

trap01 b 1

trap01 c 2

trap02 a 4

trap02 c 3

trap03 b 6

trap03 c 1


trap01 3 1 2

trap02 4 0 3

trap03 0 6 1

Link to comment
Share on other sites

No, it does not make sense. Your existing design is correct. You DEFINITELY do not want to create fields Trap01, Trap02 etc. You probaby want to add an Events table which has a single record that relates to each of the event item records.

In reality you appear to be talking about a reporting issue - a cross tab design.

Link to comment
Share on other sites

Clearly, I didn't explain things well enough!

Let me try again...I have multiple records, each providing data for a unique combination of species and trap. The problem is that this means that more than one record is required in order to report all of the species counts for each trap. I need to change the format of this such that a single record provides ALL of data for each trap (species counts). I think that the two sample databases from my original post illustrate this accurately--the first representing what I have, and the second what I need.

And what's a cross-tab design?

TIA, Pete

Link to comment
Share on other sites

You need three tables: 1) Trap 2) Species 3) Join. The Join table associates a trap with a species, one record per association. The Trap table has one record per trap. The Species table has one record per species.

Link to comment
Share on other sites

Well, thanks...intuitively, using three tables makes sense, and I can get them to work such that the Trap table calls up the appropriate count for species a, but not for subsequent species. The way I set things up, FM doesn't appear to search past the first match/mismatch so that, where trap1 in Join = trap1 in Trap only counts for species a are provided...it doesn't go on to find the appropriate counts for spp b and c. (I've also been able to get my files to link such that counts for all 3 spp for trap1 are found, but no counts for subsequent traps.)

I can't imagine this makes much sense on its own, so I've attached a zipped file ('trial data.zip') containing the 3 sample files. Note that here, my Species table is called 'original Converted.fp5'

Link to comment
Share on other sites

  • 1 month later...

This topic is 7172 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.