How to find out if students still signed in?

S

Song Su

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.
 
E

Evi

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
 
S

Song Su

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);


Evi said:
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

Song Su said:
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.
Click to expand...
Click to expand...
 
E

Evi

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



Song Su said:
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);


Evi said:
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

Song Su said:
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
Click to expand...
out,
in
this case cardID 3683 and 3530.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
S

Song Su

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]));


Evi said:
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



Song Su said:
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);


Evi said:
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:

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.
Click to expand...
Click to expand...
Click to expand...
 
E

Evi

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




Song Su said:
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]));


Evi said:
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



Song Su said:
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.
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:

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.
Click to expand...
Click to expand...
Click to expand...
 
S

Song Su

got it. Thanks.

Evi said:
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




Song Su said:
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]));


Evi said:
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
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.
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:

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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

query to sort nos 2
query to sort data 3
Need help with time in a row. 3
Crosstab Query based on Ordinal Values 6
Advanced problem using auto-number and datediff to find readmissio 1
Fiscal year 2
Headache on Outer Join with Crosstab 1
Windows 7 Duplicate files. 1

Top