Create Relationships to data imported from .csv or .xlsx files based on date/time

[cpbrin2000lbs]

Hello,

I am doing research tracking fish with a passive array of data logging acoustic receivers. I started this project in excel, which I am fairly familiar with, but as my number of records now approaches 750k and will likely double in size, the data has become too unwieldy for excel to handle smoothly. The output from the receivers is a comma separated values file (.csv) with three columns: Identification code of the receiver, Identification code of the tag on the fish, and Date/time the fish was detected. The records need to relate to tables with tidal stage records and sunrise/sunset records based on the date and time that the fish was detected. In excel I used either an IF function to assign an attribute to the records or a COUNTIF function to summarize the data. Bellow is a sample of the data and the tables it will need to relate to.

Fish detection data:

Date/time	Receiver Identification Code	Fish Identification Code
8/6/2013 4:40	VR2W- 14093	A69-601-28094
8/6/2013 4:41	VR2W- 14093	A69-1601-9005
8/6/2013 4:44	VR2W-114093	A69-1601-9005
8/6/2013 4:53	VR2W-114093	A69-1601-9005
8/6/2013 4:53	VR2W-114093	A69-1601-28094
8/6/2013 5:06	VR2W-114093	A69-1601-28094
8/6/2013 5:07	VR2W-114093	A69-1601-9005
8/6/2013 5:09	VR2W-114093	A69-1601-28094

Tidal stage:

12/24/2013 12:32	High tide
12/24/2013 15:34	Ebb tide
12/24/2013 18:36	Low tide
12/24/2013 21:51	Flood tide
12/25/2013 1:07	High tide
12/25/2013 4:09	Ebb tide
12/25/2013 7:12	Low tide
12/25/2013 10:17	Flood tide

I have been doing some reading and understand how to form the basic relationships such as relating fish ID code to a table with properties of the animal, however I could not find a way to relate the detection times to tide. Here is the method I was using in excel to sort and summarize the data. Is there a way to do the same thing, or get at the same information a different way in access, or would I need to keep this in excel and link to access? My end goal is to be able to produce reports with mean number of detections/time period (e.g. detections/high tide, detections/night) for each fish, receiver and the averages of all the fish and receivers. Additionally I will need to be able to export the data in a form that can be imported into a statistical analysis program such as SAS or R.

Count All Tab

	A	B	C	D	E	F	G
1	Date/Time of Peak Stage	Tide stage CCC	Date/Time of Start of stage		VR2W-101112(Receiver ID; data with times of fish detections on another tab)	VR2W-101401(Receiver ID; data with times of fish detections on another tab)	...
2	12/1/2012 3:18	Low tide
3	12/1/2012 6:24	Flood tide	=(A3-((A3-A2)/2))		=COUNTIFS(('VR2W-101112'!$A:$A),">=" & 'Count all'!$C3,('VR2W-101112'!$A:$A),"<" & 'Count all'!$C4)	=COUNTIFS(('VR2W-101401'!$A:$A),">=" & 'Count all'!$C3,('VR2W-101401'!$A:$A),"<" & 'Count all'!$C4)	...
4	12/1/2012 9:30	High tide	=(A4-((A4-A3)/2))		=COUNTIFS(('VR2W-101112'!$A:$A),">=" & 'Count all'!$C4,('VR2W-101112'!$A:$A),"<" & 'Count all'!$C5)	...	...
5	12/1/2012 12:40	Ebb tide	=(A5-((A5-A4)/2))		...	...	...
...	...	...	...		...	...	...
...	...	...	...		...	...	...

I look forward to your advice,
CPB

[Dal Jeanis]

First, yes, Access can do this easily. You don't need to keep anything in Excel, once you import it to Access.

Second, mathematically, (A3 - (A3-A2)/2) is the same as (A3+A2)/2 - the midpoint between the two times. The second calculation will be easier to determine in SQL.

Third, to make your life easy, your tide table could look like this

tblTideStages
  StagePK     AutoKey
  StageMid    Date/Time
  StageStart  Date/Time
  StageEnd    Date/Time
  StageType   Text

Whenever you add a chunk of new TideStages to the table, you will run a query to update the start and end times for the added stages. You can go ahead and let the database recalculate the start and end for all the Tide stages, or you could set a flag for the ones that had already been calculated. The following code assumes you just let it recalc every time, since the overhead is low.

Here's the SQL to update the mass of records, then the first, then the last. For the first and last tide stages in the table, I used the approximation of 90 minutes from the midpoint.

UPDATE tblTideStages AS TS1, tblTideStages AS tS2
SET TS2.StageEnd = (TS1.StageMid + TS2.StageMid) /2,
    TS1.StageStart = (TS1.StageMid + TS2.StageMid) /2
WHERE TS2.StageMid = 
   (SELECT MAX(TS3.StageMid) 
    FROM tblTideStages AS TS3
    WHERE TS3.StageMid < TS1.StageMid);

UPDATE tblTideStages AS TS1
SET TS1.StageStart = DateAdd("n",-90,TS1.StageMid)
WHERE TS1.StageMid = 
   (SELECT MIN(TS3.StageMid) 
    FROM tblTideStages AS TS3);

UPDATE tblTideStages AS TS2
SET TS2.StageEnd = DateAdd("n",90,TS2.StageMid)
WHERE TS2.StageMid = 
   (SELECT MAX(TS3.StageMid) 
    FROM tblTideStages AS TS3);

After you do that, you have a table with the start and end of each time zone for each tide, and your query against your sightings becomes almost trivial.


Note: I made the simplifying assumption that all your sensors are in the same tidal zone. My ancient science courses tell me that the time of high tide changes by about a minute per 25 kilometers in longitude (east-west movement). If this isn't significant for your purposes, then that's great. If the sensors were spread over a wider area, then you might need to have a "time offset" for each sensor (easiest) or have separate records on the tide table for each sensor (more complicated).

[Dal Jeanis]

Just to give an example, if your sightings table looked like this -

tblSightings
  SensorID   FK to tblSensors
  FishID     FK to tblFish
  SightDate  Date/Time

Then you can get a count for the fish detected at each sensor during each tide with something like this:

SELECT TS.SensorID, TS.StagePK, First(TS.StageMid), First(TS.StageType), Count(TF.SightDate)
FROM tblTideStages AS TS, TblSightings AS TF
WHERE  TF.SightDate < TS.StageEnd 
AND   TF.SightDate >= TS.StageStart
GROUP BY TS.SensorID, TS.StagePK;

You will also be able to view the data lots of other ways, using crosstabs or other queries. You can code queries to find out how many different fish caused readings at each sensor, and so on.

[cpbrin2000lbs]

Thanks for the help,

Does the term "tblTideStages AS TS" dictate to access that TS is a shortcut for tblTideStages? I have two groups of sensors that will need different tide times. I was originally assuming it would be simpler to have two records on the tide table or two tables for the tide values. If I chose to take this route, would I just need to add another condition such as "AND TF.locationID = TS.locationID"? The location information for each sensor would be stored in the sensor table, what would I need to do to reference that information in the query?

[Dal Jeanis]

Yes, the "AS xxx" is called an "alias" for the table. Saves typing, makes the result more readable, and so on. In Access, when you use the query design screen, after you have added a table, you can right-click that table, open the properties pane, and "rename" the table in the top properties box to give it an alias. Or, you can learn the SQL syntax and do it manually. I do about half each way - design screen for easy queries, manual for anything complicated.

Regarding your two groups of sensors - is your tide-time differential a consistent offset, or do the tides vary in significant ways?

Actually, never mind. Even if your current sensors were a consistent offset, your (future) third and fourth sets might not, so I wouldn't build a structural defect into the database. What I would do is give each sensor a zone ID, AND a time offset, and add the Zone ID to the tblTideStages table. That would look something like this -

tblTideStages
  StagePK     AutoKey
  StageZone   Number 
  StageMid    Date/Time
  StageStart  Date/Time
  StageEnd    Date/Time
  StageType   Text 

UPDATE tblTideStages AS TS1, tblTideStages AS tS2
SET TS2.StageEnd = (TS1.StageMid + TS2.StageMid) /2,
    TS1.StageStart = (TS1.StageMid + TS2.StageMid) /2
WHERE TS2.StageMid = 
   (SELECT MAX(TS3.StageMid) 
    FROM tblTideStages AS TS3
    WHERE TS3.StageMid < TS1.StageMid
    AND TS3.StageZone = TS1.StageZone )
AND TS2.StageZone = TS1.StageZone;

UPDATE tblTideStages AS TS1
SET TS1.StageStart = DateAdd("n",-90,TS1.StageMid)
WHERE TS1.StageMid = 
   (SELECT MIN(TS3.StageMid) 
    FROM tblTideStages AS TS3
    WHERE TS3.StageZone = TS1.StageZone);

UPDATE tblTideStages AS TS2
SET TS2.StageEnd = DateAdd("n",90,TS2.StageMid)
WHERE TS2.StageMid = 
   (SELECT MAX(TS3.StageMid) 
    FROM tblTideStages AS TS3
    WHERE TS3.StageZone = TS2.StageZone);

That way, if you have a set of sensors that are 25 kilometers west from another set of sensors, and have no other special situation that changes the tide/times, then the two groups can use the same tide records with a plus-one-minute offset for the farther west group. On the other hand, if there's a group near a geological feature that causes something other than a straight offset, then you can set up a separate zone for them.

By the way, you shouldn't adopt my naming conventions unless they are totally comfortable to you. Use whatever makes sense to you, since you're the guy who has to support the thing. I WOULD strongly urge you not to include spaces in any field names or table names - they are allowed by the syntax, but make the code harder to read and write, and cause you to require braces [].