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

TRAP SPP COUNT

trap01 a 3

trap01 b 1

trap01 c 2

trap02 a 4

trap02 c 3

trap03 b 6

trap03 c 1

TRAP SPa SPb SPc

trap01 3 1 2

trap02 4 0 3

trap03 0 6 1

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.

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

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.

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'

oops. here's the attachment...

trialdata.zip

Pete

Are you still working on this?

Galen