joins based on date range

[broecher]

Hi, I am going to be making a database that will keep information on radio collared animals. Animals get new collars and new frequencies periodically. Then the old collars are refurbished and the go out on new animals with the already used frequency. So basically I will have a table animal ID, frequency dateOn, and dateOff. Then people prefer to record their observations of these animals by their frequency since its less numbers to deal with. So this would be another table with frequency and date. I hope this makes sense I will try to make example tables.



Tbl_CollarHistory
AnimalID frequency dateOn DateOff
1 151.100 1-1-2010 12-31-2010
2 151.100 1-1-2011 12-31-2012
1 150.200 1-1-2011 12-31-2012


Tbl_Sightings
frequency date
151.100 2-3-2010

In a different database I did find a solution for this type of problem. I created a query with the frequencies joined. Then I made a column that checked if the date was in between dateOn and DateOff. Then I hid the records that were false.

That solution worked, but it took a couple minutes to join 20k and 2k records in each table. This new database will be smaller 200 in the collars and 5k in the sightings. Each frequency typically used 1-10 times at the max.

My question for you is do you think this approach is a good idea? Is there some better way to do this? Unlike the other database, this one will have several users and I am a little worried that it will run slow and be annoying . Should I just change the way people record their sightings so they record the animal ID (although I don't want to do this)?

Thanks!!

[June7]

Recommend a unique ID primary key in Tbl_Sightings that can be saved as foreign key in Tbl_CollarHistory. The primary key can be Autonumber datatype, foreign key Number datatype. Use a form/subform arrangement for data entry. Tbl_Sightings would be the main form RecordSource and Tbl_CollarHistory would be the subform RecordSource. The forms would be synchronized by the Master/Child link properties of the subform container. The users would not even have to be aware of the keys. Query joins on the keys should be quite fast.

Alternatively, make Frequency and Date fields a compound key. This means frequency and date will save as foreign keys in Tbl_CollarHistory. Again, with Master/Child links of subform, users need not be aware of this linking. User enters frequency and date on main form and the values will automatically save to fields of the linked subform. All joins would involve both fields.

Also, Date is a reserved word and should not use reserved words as field names. Better would be DateSighting.

[broecher]

June7,

Thanks, I appreciate the reply! I admit that I am not familiar with master-child links. I'll spend some time reading about this... I thought that this type of thing only works with stationary links that don't change (like alabama = 1) You think this would work for me even with the way my frequencies are changing through time?

hmmm... The Tbl_CollarHistory would be entered directly on the database in a form, but the Tbl_sightings comes from a mobile application. It would probably be a linked table or something like that. Does this ruin your suggestion?

I don't understand how joining the frequency-date fields (i wont use reserved word) would work in this case. Since one table contains date ranges which are start and stop dates, and the other contains dates from anywhere inside the ranges, they would never match right?

[June7]

Sorry, think I misunderstood table relationships. Let me try again.

You put collar on critter and record start and end tracking dates in Tbl_CollarHistory. Does each collar have a serial number?

Then Tbl_Sightings records date of a sighting on a particular frequency. I presume there is only one collar for each frequency during a period?

The example data shows Animal 1 has two frequencies during different periods. Is this with the same collar? The example sighting is associated with Animal 1 in its first period.

Maybe this is not as bad as I thought. I tested a query with this small set of data and it was fast. Is this like the query you used:
SELECT AnimalID, Tbl_CollarHistory.Frequency, DateOn, DateOff, DateSight
FROM Tbl_Sightings RIGHT JOIN Tbl_CollarHistory ON Tbl_Sightings.Frequency = Tbl_CollarHistory.Frequency
WHERE DateSight Between [DateOn] And [DateOff];

[broecher]

Yeah the same animal can go through multiple collars through the years, which means multiple frequencies. Also the same frequencies get re-used on different animals but never at the same time.

Thanks for your help in thinking about this!