How can I import Bird Survey data into a join table?

[wlegard]

Hi. I've set up a simple database for recording bird surveys. A screen capture of the "Relationships" display is attached below. Here's a description of the three tables:

1) BIRDS = a list of bird species known to be in this area;

2) SURVEY = a survey description;

3) RESULTS = a join table used to link the the BIRDS and SURVEY tables and record the birds seen on each survey.

I have a large number of previous bird surveys stored on Excel spreadsheets, each basically a survey description (which I can manually enter into the SURVEY table) and a column of the birds seen (names that match the bird_name field in the BIRDS table).

Is there an easy way to import a column of bird names to fill the foreign key ( _kf_birds_id) in my RESULTS table with the appropriate linked value (_kp_birds_id) from the BIRDS table? Or should I just import the bird column into an empty IMPORT table, calculate the foreign key value there, then import from the IMPORT table into RESULTS?

Thanks in advance for any suggestions.

[dansmith65]

I think your second idea will work (or something along those lines). Since you need to create primary key values that don't exist in or source data - this needs to be a multi-step import process that will probably require some scripting.

What format is the "column of the birds seen" in? comma separated list? If you can convert that to a return separated list in a temporary field/table, I think it will be easier to work with

[wlegard]

Thanks for the suggestion dansmith65. The "birds seen" list is a column in Excel, with 1 row per bird.

Following your suggestion, I created an IMPORT table linked to BIRDS with a bird_name field and included a reference to the BIRD table's key field (_kf_birds_id) that fills in automatically with the import,

Then I import the appropriate BIRD table key field values from the IMPORT table into my RESULTS table. Not very elegant, but it works.

Thanks again!