Hello!
I'm trying to reorganize an old database that has gone through many handlers over the years. I would like to relate 2 tables: Location and Logger_Events. We collect climate data and the loggers are unreliable and are frequently being replaced or moved between locations. For this reason, it is helpful to have 2 fields in the Logger_Events table called Old_location and New_location that show where a logger is coming from and going to. For instance, when a logger is deployed at a location 503.2, Old_location is "Office" and New_location is "503.2". When is it retrieved from a location, those data are switched, etc.
I would like to have the primary key "Location_ID" in the Location table be linked to both "Old_location" and "New_location" in the Logger_Event table. That way, I can have a subtable linked to the Location table that shows all Logger_Events records for that location, either in the "Old_location" or "New_location" field.
The only way I've been able to manage it so far is to pick either one or the other, but then I'm missing either the deployment or retrieval. And I've tried a relationship from one to both, but then when I ran a query, it only displayed records where both fields matched the location (missing both the deployment and retrieval).
Any suggestions would be very appreciated. I've spent far too much time messing with this unsuccessfully!
Thanks!