473,326 Members | 2,090 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

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!
May 18 '16 #1
7 1242
PhilOfWalton
1,430 Expert 1GB
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
Expand|Select|Wrap|Line Numbers
  1. Location     Date/Time     Temperature       Rainfall
  2.  
Do you also want to know which logger obtained that data?

Phil
May 18 '16 #2
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_location" and "new_location".
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_location", the subtable will be not display a deployment event. If I link by the "new_location" 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_location" OR "new_location".
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
May 19 '16 #3
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.
May 19 '16 #4
PhilOfWalton
1,430 Expert 1GB
Hi Maureen.
Your location table and logger table look fine. I think it would benefit you to also have an Events Table
Expand|Select|Wrap|Line Numbers
  1. EventID
  2. EventName (No Duplicates)
  3. IsMovement   Y/N
  4.  
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
May 19 '16 #5
NeoPa
32,556 Expert Mod 16PB
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.}
May 19 '16 #6
Thanks for the advice! I'll see if I can make it work with what we have going.
May 24 '16 #7
NeoPa
32,556 Expert Mod 16PB
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.
May 25 '16 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Gordon Burditt | last post by:
What's the easiest way to copy a row from one table to another (with the same schema), without knowing in advance the number and types of the columns? The problem: I have several sets of tables...
5
by: Sami | last post by:
Please bear with me, and if you answer this question, please do it step by step. I am new at Access, not at all sophisticated. I am using Office XP. This will need to be read in Access for...
2
by: Kathy Krizl | last post by:
I'm probably doing something stupid, but I have a make table query. One of the tables I reference has some check box fields in it. Their Data Type is Yes/No, their field property format is Yes/No,...
6
by: Sven Pran | last post by:
Probably the answer is there just in front of me only awaiting me to discover it, but: 1: I want to build a query that returns all records in one table for which there is no successful "join"...
1
by: Gary T. | last post by:
I have a table called tbl_QuoteDetails QuoteNo Prim Key fkeyProductID foreign key Paneltype Length Price Another table called tbl_Product: ProductID Primary Key ProductName
2
by: Deano | last post by:
Problem is that there are lots and lots of fields in an employee record. I specify a form control as the criterion for one of those fields which is the value of the primary key for that record. ...
4
by: tarafinlay | last post by:
Hi all, I am new to access and am finding it a bit unintuitive having worked with SQL server in the past... And I am in a bit of a hurry because my employer wants me to crank something out which...
2
by: mgunnett | last post by:
Howdy, I have an Access 2007 database on a Win Vista x64 that is going to help me keep track of donations to my Clan. I currently have 3 Tables with the following Columns: User - UserID is unique...
8
by: Shannon Mathews | last post by:
Ok trying to explain... 1. I have a tblMain that holds the master list of all my checks. 2. I'm currently using a make table query "qryCheckHoldingListCreateTbl" to get a list of all the checks...
8
by: Sandra Walsh | last post by:
Hello - I have a local table in my database called t_CompanyData that holds a subset of the fields in another table called dbo_INT_AUX_LISTING. dbo_INT_AUX_LISTING is a linked table via an...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.