Trouble creating what I perceive to be so simple!!

[cms]

In my head this project is simple. That being said I realize my skill sets are lacking in this world.
I am trying to report Photo Eye counts for a production line and include an option for recording downtime issues.



I have a RaspberryPI running a Python Script that is capturing the photo eye activities. That data is stored in a local SQLite db. I am linking to that table from Access using ODBC driver.

Here is a sample of what I am getting in the SQLite table tbl_Main





The logic goes like this.
The photo eye is polled 2 times a second. An item passing in front of the eye will trigger a "high" with a date and time stamp and an integer "count" of 1. The idea is that I can figure out line performance by adding the 1's then I can figure out the pace by diving the sum of 1's by the time differential.
If the eye records nothing for 60 seconds or a count of 120 (2 per second) then I am recording downtime starting at 1 minute until I receive a High trigger again.


That part is fairly easy. i am struggling getting the queries to pull the groupings I want though. Basically over the whole time from the first time stamp until the last time stamp tell me how many High Triggers I get so Can figure out our line speed. Then I need to query the same data to group Low by count 120 until the next "high" so that I can compile all the downtimes showing how long they are over the same time period.

I know this can be done but I am having a blank moment trying to get there right now.

[June7]

Provide example of expected result from the sample given.

I would like to see query for the 'fairly easy' part.

If you want to provide db, follow instructions at bottom of my post.

[Cottonshirt]

Basically over the whole time from the first time stamp until the last time stamp tell me how many High Triggers I get

start by determining the time frame you want to cover. an AGGREGATE query GROUP by trigger with the criteria "High" and Min(timedate) and Max(timedate) will give you this.

in a real world situation you might want to run a preliminary query restricting records to those with today's date, or those with a timestamp on Wednesday afternoon, or whatever. then use that as the record source for the AGGREGATE query.

that will give you something like this:

SELECT tbl_transaction.trigger, Min(tbl_transaction.timedate) AS timestart, Max(tbl_transaction.timedate) AS timeend
FROM tbl_transaction
GROUP BY tbl_transaction.trigger
HAVING (((tbl_transaction.trigger)="High"));

then you want to count how man high triggers you had in that time frame, so you SELECT records between timestart and timeend, and Count(High) on those records. this last part has to be done in two steps because you can't (or at least I could not) both SELECT the records in that time frame and Count them in one query. so SELECT them first, then count them:

SELECT tbl_transaction.trigger, tbl_transaction.timedate, tbl_transaction.count
FROM tbl_transaction, qry_periodlength
WHERE (((tbl_transaction.trigger)="High") AND ((tbl_transaction.timedate) Between [qry_periodlength].[timestart] And [qry_periodlength].[timeend]));

save this as qry_recordsrequired, then do this:

SELECT qry_recordsrequired.trigger, Sum(qry_recordsrequired.count) AS SumOfcount
FROM qry_recordsrequired
GROUP BY qry_recordsrequired.trigger;

good luck with your project,


Cottonshirt