Results 1 to 6 of 6
  1. #1
    TonyMartin is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2020
    Posts
    3

    How to calculate cumulative operational days across multiple machines at different sites?

    Hi, I'm seeking some help, please, with a query that's beyond me. It's to do with the 'effort' accumulated by numerous trailcams (automatic wildlife cameras) across different waterside sites: Here's the situation:



    1. In a Table (named 'Deployment') I have one record per camera deployment. That record includes the start date, pickup date and location.
    2. In a Table named 'Site' I have one record per location, including sitename, county, river.

    I want to be able to discover the effort (defined as the number of camera nights) accumulated across all cameras between date A and date B in, say, County A or River B. And I'd like to know how many cameras were active and therefore contributed to that effort total.

    I'm familar with how to set up a parameter query to ask the question; what I can't work out is how to use the data in my Deployment Table (including deployments with no pickup date because the camera is still active at that location) to give me the info I need.

    I'd be hugely grateful for guidance, please.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    For clarity, you record the operational time for each camera at each location. A camera's operational time starts on StartDate(and time) and ends at ??? the Pickup Date (and time).
    This only applies when a camera has a pickup time? Or the current Date and time if there is no Pickup Date recorded for that camera?

    Sometimes not being intimately familiar with the set up/environment makes for guessing or misinterpreting the detail.

    Sounds like you want to Sum the operational times of cameras in a specific location where the camera Location is X and the operational times are Between StartDate and PickupDate.

    What exactly do you want from a camera that does not have a Pickup date, but is within your location and start date criteria?

  3. #3
    TonyMartin is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2020
    Posts
    3
    Many thanks indeed for the prompt response, and apologies for the lack of clarity.

    1.Yes, the operational time ends at the PickupDate (and time, though the time is not important because the unit of effort is camera night, and the location visits are only ever made in daylight).
    2. If no PickupDate is entered in the record, it means that the camera is currently operational at that location (so, in effect, the PickupDate is today as far as the calculations on a particular day are concerned)
    3. I want to sum the operational nights of all cameras active between Date A and Date B in County C on River D, where A,B,C,D are provided by Parameter Queries. And I'd like it set up such that if no county is entered for C, then all counties are included by default. Similarly, if no river is entered for D, then all rivers are included. A river may pass through multiple counties.

    Hope that helps

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tony,

    I mocked up a sample for consideration. It is meant as a starting point. Hope it is helpful. You can remove some of the fields from the query as necessary to get the SUMs you need.
    Although you mentioned parameter queries, and my sample deals with a Parameter query, I think in day to day operations you may want to investigate using a form to allow parameter values to be selected and then modify some template sql, then click a button to execute a query. But you know your needs and environment better than readers.
    Good luck with your project.
    Attached Files Attached Files

  5. #5
    TonyMartin is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2020
    Posts
    3
    Thanks so much. I'll spend time on this today, and will get back to you with the outcome. I'm blown away by your kindness in helping me with this.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 04-25-2019, 01:29 PM
  2. Replies: 1
    Last Post: 06-17-2018, 02:12 AM
  3. Replies: 1
    Last Post: 03-29-2017, 08:55 AM
  4. How To Calculate Days Between Dates
    By DigitalAdrenaline in forum Access
    Replies: 3
    Last Post: 08-22-2016, 10:08 PM
  5. Replies: 2
    Last Post: 10-19-2012, 02:21 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums