Relating a primary key to two fields in another table. Can I then query either?

[Eastside newbie]

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_I D" in the Location table be linked to both "Old_locati on" and "New_locati on" 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_locati on" or "New_locati on" 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!

[PhilOfWalton]

I'm a little bit hazy of what is wanted, so need a few answers.
I assume a logger holds information such as Date/Time Temperature, rainfall etc.
Does the Logger have an Id (serial Number or something that can be identified)
Am I correct in presuming the final result you want is

Location     Date/Time     Temperature       Rainfall

Do you also want to know which logger obtained that data?

Phil

[Eastside newbie]

Hi Phil, thanks for responding. This database doesn't contain any of the climate data. That's somewhere else. The database essentially has 3 main tables:
1) Location: this is a tree that has a logger mounted on it. The location ID is the primary key and then there's UTMs, elevations, forest type, etc.
2)Logger: The logger ID is the primary key. Other fields include serial number, logger type, notes.
3)Logger event: This table tracks any activities with the logger. It has an auto-generated primary key, date, logger_ID, logger event (deployment, launch, download, retrieval) and "old_locati on" and "new_locati on".
What I want is to create a sub-table in my location table that lists all of the logger events for each location. The problem is the old location and new location fields in the logger event table. And it might be that this is a fundamental problem with the design of the database. But we've been using this for several years and at this point, I'm trying to just work with what we have rather than doing monumental overhauls.
For the logger event types 1)deployment and 2)retrieval, old_location and new_location are different. The logger had been taken from somewhere and placed somewhere else. If I relate the location and logger event table using "old_locati on", the subtable will be not display a deployment event. If I link by the "new_locati on" field, the subtable will not display a retrieval event. Does that make sense? I'd like the subtable to find all logger events that match the location to EITHER "old_locati on" OR "new_locati on".
I guess I'm wondering if this is something that Access can do. I haven't been able to find answers in other discussions. It seems like the easiest solution to my database problem. Else I'll be doing some redesigning.
Thanks again for taking the time to think about my problem!
Maureen

[Eastside newbie]

Still not sure if I answered your question. What I need is a handy list showing all the events at a location so I can quickly see the history of the location and be able to reference all the activity associated with it.

[PhilOfWalton]

Hi Maureen.
Your location table and logger table look fine. I think it would benefit you to also have an Events Table

EventID
EventName (No Duplicates)
IsMovement   Y/N

This would simply hold things like
deployment, launch, download, retrieval.

Now we come to the more interesting table LoggerEvents
I suggest
LocationID Long
LoggerID Long
EventDate Date
EventID Long
The first 3 fields are a combined primary key.

So what does this achieve
If a query based on this is sorted by Location, then Date, you will get what event happened on which date at each location (what I think you want)
Conversely, if you sort it by Logger & date you will find at which location an event happened. Possibly just as useful is that you can track a logger's location at any date.
So you don't need Old & New location.

In the event table, I added a field IsMovement, in case you just want to look at events other than placing the logger at the location and retrieving it.

Hope this helps.

Phil

[NeoPa]

Have you considered a table that links locations with loggers using a date range?

One record for each location. One record for each logger. One record for joining them for each time they are located - with start and end dates for each. New records would have the end date value left blank (Null) until the logger moves on.

{I cross-posted with Phil. His answer seems similar but not quite the same. I leave you to consider what's here.}

[Eastside newbie]

Thanks for the advice! I'll see if I can make it work with what we have going.

[NeoPa]

That's your prerogative Maureen, but I can't say I'd advise it. Sometimes you have to recognise that people don't give advice without reason.

It's always your choice in the end though. Good luck whichever path you take. I'm afraid you might need some.