UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query To Count Interactions, Access 2003    
 
   
paulgreen
post Oct 9 2019, 09:39 AM
Post#1



Posts: 92
Joined: 9-February 05
From: Wales, UK


Hi all, I'm in the process of preparing a query to ensure our staff are doing what they're supposed to be, and I'm a bit stuck - I'm convinced this is very simple and staring me in the face. What I want to achieve is:

TabSites
SiteID
SiteName

TabCheckpoints
CheckpointID
CheckpointName
SiteID

TabHits
HitID
HitTimeStamp
HitCheckpointID
HitSiteID

Throughout the course of a shift, the staff will record their hits as they walk around a site, so a site may have 20 checkpoints, and throughout a shift could have 100 hits. What I want to achieve is to look at the TabHits table and see how many checkpoints have been hit during that time so we can pick up on which staff are regularly not hitting all checkpoints through the course of a shift. Our tables are obviously more involved, and we do log when the checkpoint was last hit elsewhere, but I'm hoping to be able to include this in an at-a-glance query I'm writing that shows various areas of compliance.

Any pointers in the right direction would be greatly appreciated, as I've been staring at a blank query for the last 20 minutes.

--------------------
PG
Go to the top of the page
 
GroverParkGeorge
post Oct 9 2019, 09:59 AM
Post#2


UA Admin
Posts: 35,885
Joined: 20-June 02
From: Newcastle, WA


Sorry, but sometimes I get distracted by details. What is a "hit"? What is a "checkpoint"?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
paulgreen
post Oct 9 2019, 10:04 AM
Post#3



Posts: 92
Joined: 9-February 05
From: Wales, UK


Haha, I run a small security company and the checkpoints are NFC tags which are dotted around various sites. My guards have a mobile phone app which when they tap an NFC tag sends an SMS which is picked up by our server using some voodoo (ok, an API) and stored as a hit.

What I'm essentially looking to able to do is show a list of all of Bob's shifts and see that he's regularly missing x number of tags, allowing us to nip it in the bud before clients notice.

--------------------
PG
Go to the top of the page
 
GroverParkGeorge
post Oct 9 2019, 10:12 AM
Post#4


UA Admin
Posts: 35,885
Joined: 20-June 02
From: Newcastle, WA


Thanks.

I don't see where those tables capture the identity of the person doing the checking. How do you relate that to this data?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Oct 9 2019, 10:24 AM
Post#5


UA Admin
Posts: 35,885
Joined: 20-June 02
From: Newcastle, WA


Here's a starter sample. It'll need some tweaking, but it might give you some ideas.

Attached File  HitCount.zip ( 20.53K )Number of downloads: 3

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
paulgreen
post Oct 9 2019, 11:25 AM
Post#6



Posts: 92
Joined: 9-February 05
From: Wales, UK


Thanks George, that is indeed a good starting point. What that's giving me is a count of how many times each tag has been used throughout the course of the shift, which upon playing with it having a sub-datasheet with this info might be a better way to present the data and will show us a lot more by way of patterns.

Appreciate you help smile.gif

--------------------
PG
Go to the top of the page
 
arnelgp
post Oct 9 2019, 12:05 PM
Post#7



Posts: 1,472
Joined: 2-April 09
From: somewhere out there...


can you not group and count the hits

select tabcheckpoints.checkpointname, count(tabhits.hitid) as HitCount from
tabhits inner join tabcheckpoints on tabhits.hitcheckpointid = tabcheckpoints.checkpointid
group by tabchechpoints.checkpointname;

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th October 2019 - 04:30 PM