Database design- need help relating fields

[sebeckett]

Hello!
We are having trouble designing a relationship within a wildlife observation database. Here's the situation:
When observing a particular animal, we first record all the environmental parameters, then record two different types of observations, so...

Table1 has records of all the environmental variables surrounding the observation.


[Date]
[Animal_Number]
[Weather]
[Precipitation]
[Temperature]

Table 2 has records of the first type of observations. Each row is a single behavioral observation.
[Date]
[Animal_Number]
[Behavior]
[Time (seconds)]

Table 3 has records of a second type of observations. Again, each row is a single behavioral observation.
[Date]
[Animal_Number]
[Behavior]
[Class]
[Position]

Many records from both Table 2 and Table 3 (observations) will be associated with only one record from Table 1 (environment). In other words, one row of environmental data is recorded prior to a a session of observations of a particular animal.

The problem is, there is no single unique identifier field that is common between these three tables. [Date] and [Animal Number] as a compound could be "matched", but we're not sure how to create a relationship drawing from multiple fields.

How do we create one-to-many relationships such that each row in Tables 2 and 3 is associated with its matching environmental record from Table 1?

For what its worth, the end goal will ideally be two queries: One will present each observation (row) from Table 2 with its matching record from Table 1 in the same row. The other query will do the same for Table 3 and 1.

Hopefully this is enough information! Thanks very much.

Sean

[rpeare]

First, don't use reserved words as your field names, DATE is a reserved word and will cause you a lot of headaches if you keep it that way.

Second, in your Table1 have a field called ObservID make it an autonumber field. This will be your unique identifier.

In your Table2 have a field called ObservTypeID make it an autonumber field. This will be your unique identifier for this table, but you also have to include a field ObservID which will be the foreign key to Table1. If you set this up with a form/subform you will not have to enter any of the ID fields, they will be automatically generated or populated by the form/subform link

In Table3 have a field called ObservPositionID make it an autonumber field. Again, this will be your unique identifier for this table, but also include the ObservID from Table1 and set up your data entry forms in much the same way as Table2.

Unique keys should be values that have no relation to your data which is why most people use autonumber, it's a simple way to reference which record you're working on that will not change over time.

[sebeckett]

Thanks very much! This setup seems to be working beautifully for new record additions. HOWEVER, I have a couple thousand existing records that, i assume, will not have their [ObservID]s auto-populated appropriately. Is there a way to automate this, or will i have to do this manually?

Sean

[rpeare]

The hard part for you is going to be the OBSERVID in the two behavior tables, unless you have an original document or some other field that you are not telling us about it's going to be fairly tedious to go back through all of your existing data and relate it to one another. The saving grace MAY be that if you have not modified the layout of your tables at all (sort order in particular) all of the records should appear in the order they were entered so if you modify your Table1 to have an autonumber field you should theortetically be able to go through each of your subsidary tables and see the items in the same order and physically type in the correct ObservID of the subsidiary tables BEFORE you apply the autonumber primary key to them. If you add the autonumber before putting the observID in the subsidiary tables you will mess with the sort order then you'll likely have to do it more painfully.

[sebeckett]

The tables are in matching order, so it will be painful to type in ObservIDs, but not tremendously so. Glad this problem is getting addressed now, and not two years and 50,000 observations from now. Thank you!

[rpeare]

Before you do anything make sure you make a copy of that database to preserve it just in case.