 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / April 2008How to find out if students still signed in?
My table look like this: cardID dateTime SwipeResult 2136 4/25/2008 9:07:19AM 0 2136 4/25/2008 9:07:46AM 1 3683 4/25/2008 10:04:14AM 0 3683 4/25/2008 10:25:00AM 1 3683 4/25/2008 11:00:15AM 0 3530 4/25/2008 11:25:10AM 0 SwipeResult 0=signed in; 1=signed out I want a query to list of students who signed in but have not signed out, in this case cardID 3683 and 3530. Thanks. Would it be better to have a SignedIn and SignedOut Date/Timefield. In your form, a button can activate either, putting Now() into the field. You can use code to see if a student tries to sign in when he hadn't previously signed out or tries to sign out when he hasn't signed in and prevent him from signing again unless he has seen someone and if the signedOut field is null, then you know that the student hasn't signed out yet. But given your scenario, In your query add one field which says Signedin: Abs(SwipeResult=0) and one field which says SignedOut: Abs(SwipeResult=1) Create a Totals query based on this query add CardID, SignedIn, SignedOut Your criteria row under SumOfSignedIn will say <>[SumOfSignedOut] or whatever the totals query calls the field Evi > My table look like this: > [quoted text clipped - 11 lines] > > Thanks. Thank you for quick reply. Contractor wrote the program and I cannot modify signin/signout table which saved to SQL server. I can only use query to generate report. I'm almost there. The only thing that I don't know is to put criteria <>[SumOfSignedOut] My query total row is all 'group'. Is that correct? Based on my query, how to put above criteria? SELECT dbo_tblSwipes.cardID, Abs([SwipeResult]=0) AS Signedin, Abs([SwipeResult]=1) AS SignedOut FROM dbo_tblSwipes GROUP BY dbo_tblSwipes.cardID, dbo_tblSwipes.stationID, dbo_tblSwipes.dateTime, Abs([SwipeResult]=0), Abs([SwipeResult]=1); > Would it be better to have a SignedIn and SignedOut Date/Timefield. > In your form, a button can activate either, putting Now() into the field. [quoted text clipped - 40 lines] >> >> Thanks. you can't include the DateTime field in your Totals query I see that you haven't made it a Totals query yet because you should be Summing SignedIn and SignedOut - choose Sum instead of Group By in the row that says Total in the left hand side. If you have it right, you will be left with only the following fields CardID, SumOfSignedIn and SumOfSignedOut (or however your version of Access chooses to rename the summed fields) So each card number will have a total for how many times they signed in and a total for how many times they signed out. They should be equal if the student has signed in the same number of times as they have signed out. Your criteria goes in the Criteria row. In the Query's design view this row says Criteria at the left side. Evi > Thank you for quick reply. Contractor wrote the program and I cannot modify > signin/signout table which saved to SQL server. I can only use query to [quoted text clipped - 56 lines] > >> > >> Thanks. I followed your instruction as below and when I run it, it prompt me for parameter value 'SumOfSignedOut'. I put dateTime field (but uncheck it in 'show') in order to filter out just 'today's data. how to put in criterial for that? SELECT dbo_tblSwipes.cardID, Sum(Abs([SwipeResult]=0)) AS Signedin, Sum(Abs([SwipeResult]=1)) AS SignedOut FROM dbo_tblSwipes GROUP BY dbo_tblSwipes.cardID HAVING (((Sum(Abs([SwipeResult]=0)))<>[SumOfSignedOut])); > you can't include the DateTime field in your Totals query > [quoted text clipped - 85 lines] >> >> >> >> Thanks. In Sql View Change the row that starts with HAVING to this 1 line HAVING (Sum(Abs([SwipeResult]=0))<>Sum(Abs([SwipeResult]=1))) back in the ordinary Design view, under your date field, type Date() in the criteria row. Evi > I followed your instruction as below and when I run it, it prompt me for > parameter value 'SumOfSignedOut'. [quoted text clipped - 96 lines] > >> >> > >> >> Thanks. got it. Thanks. > In Sql View > Change the row that starts with HAVING to this 1 line [quoted text clipped - 114 lines] >> >> >> >> >> >> Thanks. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.