Tardyscans query

[ncdavis25 via AccessMonster.com]

Hi, I'm having difficulty with the sql listed below. I'm trying to produce
daily duplicate dates and times. The database tardyscans has a field called
datetime in it that shows the date and time the student came in, but there is
no separate field for date and time. When I run the query is asks me to
enter the parameter value for the date, then for tardyscans.date and then for
tardyscans.time. I'm not sure where I went wrong. Can anyone help me.

SELECT TardyScans.SID, TardyScans.Date AS Expr1, TardyScans.Time AS Expr2,
TardyScans.Reason, TardyScans.Resolution
FROM TardyScans
WHERE (((TardyScans.SID) In (SELECT [SID] FROM [TardyScans] As Tmp GROUP BY
[SID],[Date] HAVING Count([SID])>1 And Format([Date],"mm/dd/yy")= Format(Date
(),"mm/dd/yy"))) AND (([TardyScans].[Date])=Format(Date(),"mm/dd/yy")))
ORDER BY TardyScans.SID, TardyScans.Date, TardyScans.Time;

 

[Michel Walsh]

Try using DateValue and TimeValue with a date time value as argument:

SELECT sid, DateValue(dateTime), TimeValue(dateTime), reason, resolution
FROM ...



You have to use the real field you have in your table for the 'dateTime'
field I used here up.




Vanderghast, Access MVP

 

[KARL DEWEY]

If it has a single field has a field called datetime in it that shows the
date and time the student came in how are you expecting to select
TardyScans.Date AS Expr1 and TardyScans.Time AS Expr2?

It appears that you want to see if a student was tardy more than once in one
day. Is this correct?

 

[John Spencer]

Well the first thing is to reduce the confusion by not using Date as a
field name or as an alias for a field name. You said you had one field
containing both the DATE and theTime. Assuming that you are looking to
find SID where there are 2 or more records on a specific date (today)
then the query might look like

SELECT TardyScans.SID
, DateValue(TardyScans.DateTime) AS TheDate
, TimeValue(TardyScans.DateTime) as TheTime
, TardyScans.Reason
, TardyScans.Resolution
FROM TardyScans
WHERE TardyScans.SID In
(SELECT [SID]
FROM [TardyScans] As Tmp
WHERE DateValue(Tmp.DateTime) = Date()
GROUP BY [SID]
HAVING Count([SID])>1)
AND DateValue(DateTime) = Date()
ORDER BY TardyScans.SID
, DateValue(TardyScans.DateTime)
, TimeValue(TardyScans.DateTime)

If that is not what you want then you might try describing in words what
you do want.

If you are looking for records for a student that are exact duplicates
in date and time then that is possible. It would actually be simpler
then the above and you could use the find duplicates query wizard to
build the basic query for you.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[ncdavis25 via AccessMonster.com]

yes, I want to know how often the student is tardy in one day

If it has a single field has a field called datetime in it that shows the
date and time the student came in how are you expecting to select
TardyScans.Date AS Expr1 and TardyScans.Time AS Expr2?

It appears that you want to see if a student was tardy more than once in one
day. Is this correct?

Hi, I'm having difficulty with the sql listed below. I'm trying to produce
daily duplicate dates and times. The database tardyscans has a field called

[quoted text clipped - 10 lines]

(),"mm/dd/yy"))) AND (([TardyScans].[Date])=Format(Date(),"mm/dd/yy")))
ORDER BY TardyScans.SID, TardyScans.Date, TardyScans.Time;

 

[ncdavis25 via AccessMonster.com]

Hi John,

Thank You so much, that helped resolve the issue. I would like to know also
about the find duplicates wizard, how do I activate that?

Well the first thing is to reduce the confusion by not using Date as a
field name or as an alias for a field name. You said you had one field
containing both the DATE and theTime. Assuming that you are looking to
find SID where there are 2 or more records on a specific date (today)
then the query might look like

SELECT TardyScans.SID
, DateValue(TardyScans.DateTime) AS TheDate
, TimeValue(TardyScans.DateTime) as TheTime
, TardyScans.Reason
, TardyScans.Resolution
FROM TardyScans
WHERE TardyScans.SID In
(SELECT [SID]
FROM [TardyScans] As Tmp
WHERE DateValue(Tmp.DateTime) = Date()
GROUP BY [SID]
HAVING Count([SID])>1)
AND DateValue(DateTime) = Date()
ORDER BY TardyScans.SID
, DateValue(TardyScans.DateTime)
, TimeValue(TardyScans.DateTime)

If that is not what you want then you might try describing in words what
you do want.

If you are looking for records for a student that are exact duplicates
in date and time then that is possible. It would actually be simpler
then the above and you could use the find duplicates query wizard to
build the basic query for you.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi, I'm having difficulty with the sql listed below. I'm trying to produce
daily duplicate dates and times. The database tardyscans has a field called

[quoted text clipped - 10 lines]

(),"mm/dd/yy"))) AND (([TardyScans].[Date])=Format(Date(),"mm/dd/yy")))
ORDER BY TardyScans.SID, TardyScans.Date, TardyScans.Time;

 

[ncdavis25 via AccessMonster.com]

Hi Michael,
I appreciate you taking out the time to assist me.

Thank you

Try using DateValue and TimeValue with a date time value as argument:

SELECT sid, DateValue(dateTime), TimeValue(dateTime), reason, resolution
FROM ...

You have to use the real field you have in your table for the 'dateTime'
field I used here up.

Vanderghast, Access MVP

Hi, I'm having difficulty with the sql listed below. I'm trying to
produce

[quoted text clipped - 16 lines]

(),"mm/dd/yy"))) AND (([TardyScans].[Date])=Format(Date(),"mm/dd/yy")))
ORDER BY TardyScans.SID, TardyScans.Date, TardyScans.Time;

 

[John Spencer]

Click New Query and select Find Duplicates Query Wizard in the dialog.

If you are using Access 2007, I have no idea if it works the same.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[ncdavis25 via AccessMonster.com]

Thanks John I found it.
I'm having some troubles with a tardy database I'm creating, I created a
detention append query where I want to add all of the student tardies to
determine if they should receive a detention or not but it is not working.
when I run it, it request for me to enter a parameter value for Forms!
ScanEntry!PassSID, Forms!ScanEntry!PassDateTime, Forms!ScanEntry!
PassnMaxUnExc and then it says invalid use of Null. Where am I going wrong?

INSERT INTO DT ( SID, DTDateTime, ServeByDate, TotalLateCount )
SELECT DISTINCT Pass.SID, Max(Forms!ScanEntry!PassDateTime) AS Expr2, DLookUp
("[ServeByDate]","Dates - DT","[DTDate] = #" & Date() & "#") AS ServeByDate,
CInt(Forms!ScanEntry!PassnMaxUnExc) AS Expr1
FROM Pass
GROUP BY Pass.SID, DLookUp("[ServeByDate]","Dates - DT","[DTDate] = #" & Date
() & "#"), CInt(Forms!ScanEntry!PassnMaxUnExc);

 

[ncdavis25 via AccessMonster.com]

Thanks John I found it.
I'm having some troubles with a tardy database I'm creating, I created a
detention append query where I want to add all of the student tardies to
determine if they should receive a detention or not but it is not working.
when I run it, it request for me to enter a parameter value for Forms!
ScanEntry!PassSID, Forms!ScanEntry!PassDateTime, Forms!ScanEntry!
PassnMaxUnExc and then it says invalid use of Null. Where am I going wrong?

INSERT INTO DT ( SID, DTDateTime, ServeByDate, TotalLateCount )
SELECT DISTINCT Pass.SID, Max(Forms!ScanEntry!PassDateTime) AS Expr2, DLookUp
("[ServeByDate]","Dates - DT","[DTDate] = #" & Date() & "#") AS ServeByDate,
CInt(Forms!ScanEntry!PassnMaxUnExc) AS Expr1
FROM Pass
GROUP BY Pass.SID, DLookUp("[ServeByDate]","Dates - DT","[DTDate] = #" & Date
() & "#"), CInt(Forms!ScanEntry!PassnMaxUnExc);

 

[ncdavis25 via AccessMonster.com]

One more thing, I'm trying to simplify this as much as possible, but I think
I'm over doing it!

 

[John Spencer]

If you are referencing controls on a form, the form has to be open. Is
the form open?

Next on a form control value MAX (if it works) will return whatever the
current active record on the form has for a value. It won't find any
Max value.

Next the DLookup function probably need the table name surrounded with
brackets.

You dont need DISTINCT since you are using GROUP BY.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thanks John I found it.
I'm having some troubles with a tardy database I'm creating, I created a
detention append query where I want to add all of the student tardies to
determine if they should receive a detention or not but it is not working.
when I run it, it request for me to enter a parameter value for Forms!
ScanEntry!PassSID, Forms!ScanEntry!PassDateTime, Forms!ScanEntry!
PassnMaxUnExc and then it says invalid use of Null. Where am I going wrong?

INSERT INTO DT ( SID, DTDateTime, ServeByDate, TotalLateCount )
SELECT DISTINCT Pass.SID, Max(Forms!ScanEntry!PassDateTime) AS Expr2, DLookUp
("[ServeByDate]","Dates - DT","[DTDate] = #" & Date() & "#") AS ServeByDate,
CInt(Forms!ScanEntry!PassnMaxUnExc) AS Expr1
FROM Pass
GROUP BY Pass.SID, DLookUp("[ServeByDate]","Dates - DT","[DTDate] = #" & Date
() & "#"), CInt(Forms!ScanEntry!PassnMaxUnExc);

Click New Query and select Find Duplicates Query Wizard in the dialog.

If you are using Access 2007, I have no idea if it works the same.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[ncdavis25 via AccessMonster.com]

Hi John,

the form is open and I ran the query, but I still get the Invalid use of Null
message. I also surrounded the table name [Dates - DT], the field names are
ServeByDate and DTDate. I also took DISTINCT out.

If you are referencing controls on a form, the form has to be open. Is
the form open?

Next on a form control value MAX (if it works) will return whatever the
current active record on the form has for a value. It won't find any
Max value.

Next the DLookup function probably need the table name surrounded with
brackets.

You dont need DISTINCT since you are using GROUP BY.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thanks John I found it.
I'm having some troubles with a tardy database I'm creating, I created a

[quoted text clipped - 27 lines]