Counting of Dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Please help...

I have a table of data holding Engineers ID's and Visit Dates of when they
attended work jobs. I am wanting to Count the Visit Dates but where he does
multiple visits in 1 day I only want to count it once.

How can I do this in a query?

Thanks,
Jez
 
One way will be by creating two queries
1. GroupBy query to group the ID and the Date

Select Id , DateField
From TableName
Group By Id , DateField

2. Another group By query with a count, that based on the above query

Select Id , Count([DateField]) As CountOfDate
From QueryName
Group By Id
 
Hi, Thanks for getting back to me.

This still doesnt work.

What I am trying to do is..
If I attend a 3 jobs today and 2 Jobs tomorrow, I want to count the number
of days i have worked. so in this case it would be 2 days worked but 5 jobs
completed.

how can i workout how many days worked?

Thanks,
Jez



Ofer Cohen said:
One way will be by creating two queries
1. GroupBy query to group the ID and the Date

Select Id , DateField
From TableName
Group By Id , DateField

2. Another group By query with a count, that based on the above query

Select Id , Count([DateField]) As CountOfDate
From QueryName
Group By Id


--
Good Luck
BS"D


Jez said:
Hi,

Please help...

I have a table of data holding Engineers ID's and Visit Dates of when they
attended work jobs. I am wanting to Count the Visit Dates but where he does
multiple visits in 1 day I only want to count it once.

How can I do this in a query?

Thanks,
Jez
Click to expand...
Click to expand...
 
If you used the two queries as I suggested then can you post the SQL to both
of them?

But before you do that, if the date field contain the time, then you need to
remove the time from the date field in the first query

Select Id , DateValue(DateField) As NewFieldName
From TableName
Group By Id , DateValue(DateField)

And then run the second query
--
Good Luck
BS"D


Jez said:
Hi, Thanks for getting back to me.

This still doesnt work.

What I am trying to do is..
If I attend a 3 jobs today and 2 Jobs tomorrow, I want to count the number
of days i have worked. so in this case it would be 2 days worked but 5 jobs
completed.

how can i workout how many days worked?

Thanks,
Jez



Ofer Cohen said:
One way will be by creating two queries
1. GroupBy query to group the ID and the Date

Select Id , DateField
From TableName
Group By Id , DateField

2. Another group By query with a count, that based on the above query

Select Id , Count([DateField]) As CountOfDate
From QueryName
Group By Id


--
Good Luck
BS"D


Jez said:
Hi,

Please help...

I have a table of data holding Engineers ID's and Visit Dates of when they
attended work jobs. I am wanting to Count the Visit Dates but where he does
multiple visits in 1 day I only want to count it once.

How can I do this in a query?

Thanks,
Jez
Click to expand...
Click to expand...
Click to expand...
 
Thanks, that now works as I understand it better.

Its still not what I am looking for though...

My table shows

EngineerID VisitDate
123 10-Jul-06
123 10-Jul-06
123 09Jul-06
123 09Jul-06
123 08Jul-06
1234 10-Jul-06

I want my query to do as follows..

EngineerID DaysWorked
123 3
1234 1

Thanks,
Jez

Ofer Cohen said:
If you used the two queries as I suggested then can you post the SQL to both
of them?

But before you do that, if the date field contain the time, then you need to
remove the time from the date field in the first query

Select Id , DateValue(DateField) As NewFieldName
From TableName
Group By Id , DateValue(DateField)

And then run the second query
--
Good Luck
BS"D


Jez said:
Hi, Thanks for getting back to me.

This still doesnt work.

What I am trying to do is..
If I attend a 3 jobs today and 2 Jobs tomorrow, I want to count the number
of days i have worked. so in this case it would be 2 days worked but 5 jobs
completed.

how can i workout how many days worked?

Thanks,
Jez



Ofer Cohen said:
One way will be by creating two queries
1. GroupBy query to group the ID and the Date

Select Id , DateField
From TableName
Group By Id , DateField

2. Another group By query with a count, that based on the above query

Select Id , Count([DateField]) As CountOfDate
From QueryName
Group By Id


--
Good Luck
BS"D


:

Hi,

Please help...

I have a table of data holding Engineers ID's and Visit Dates of when they
attended work jobs. I am wanting to Count the Visit Dates but where he does
multiple visits in 1 day I only want to count it once.

How can I do this in a query?

Thanks,
Jez
Click to expand...
Click to expand...
Click to expand...
 
First Query, save it as "DateGroup"

SELECT VisitDate, EngineerID
FROM TableName
GROUP BY VisitDate, EngineerID

Second Query

SELECT EngineerID, Count(EngineerID) AS DaysWorked
FROM DateGroup
GROUP BY EngineerID

--
Good Luck
BS"D


Jez said:
Thanks, that now works as I understand it better.

Its still not what I am looking for though...

My table shows

EngineerID VisitDate
123 10-Jul-06
123 10-Jul-06
123 09Jul-06
123 09Jul-06
123 08Jul-06
1234 10-Jul-06

I want my query to do as follows..

EngineerID DaysWorked
123 3
1234 1

Thanks,
Jez

Ofer Cohen said:
If you used the two queries as I suggested then can you post the SQL to both
of them?

But before you do that, if the date field contain the time, then you need to
remove the time from the date field in the first query

Select Id , DateValue(DateField) As NewFieldName
From TableName
Group By Id , DateValue(DateField)

And then run the second query
--
Good Luck
BS"D


Jez said:
Hi, Thanks for getting back to me.

This still doesnt work.

What I am trying to do is..
If I attend a 3 jobs today and 2 Jobs tomorrow, I want to count the number
of days i have worked. so in this case it would be 2 days worked but 5 jobs
completed.

how can i workout how many days worked?

Thanks,
Jez



:

One way will be by creating two queries
1. GroupBy query to group the ID and the Date

Select Id , DateField
From TableName
Group By Id , DateField

2. Another group By query with a count, that based on the above query

Select Id , Count([DateField]) As CountOfDate
From QueryName
Group By Id


--
Good Luck
BS"D


:

Hi,

Please help...

I have a table of data holding Engineers ID's and Visit Dates of when they
attended work jobs. I am wanting to Count the Visit Dates but where he does
multiple visits in 1 day I only want to count it once.

How can I do this in a query?

Thanks,
Jez
Click to expand...
Click to expand...
Click to expand...
 
Thanks for getting back to me...

but it still doesnt answer my question. The query just adds up all the
EngineerID.

I am trying to workout how many days worked and not jobs done.

EngineerID VisitDate
123 10-Jul-06
123 10-Jul-06
123 09Jul-06
123 09Jul-06
123 08Jul-06
1234 10-Jul-06

I want my query to do as follows..

EngineerID DaysWorked
123 3 as he worked 8,9,10 July but actually did 5 Jobs
1234 1

so when my query runs I want the answer to show me 3 in this case as that
how many days worked.

Thanks,
Jez



Ofer Cohen said:
First Query, save it as "DateGroup"

SELECT VisitDate, EngineerID
FROM TableName
GROUP BY VisitDate, EngineerID

Second Query

SELECT EngineerID, Count(EngineerID) AS DaysWorked
FROM DateGroup
GROUP BY EngineerID

--
Good Luck
BS"D


Jez said:
Thanks, that now works as I understand it better.

Its still not what I am looking for though...

My table shows

EngineerID VisitDate
123 10-Jul-06
123 10-Jul-06
123 09Jul-06
123 09Jul-06
123 08Jul-06
1234 10-Jul-06

I want my query to do as follows..

EngineerID DaysWorked
123 3
1234 1

Thanks,
Jez

Ofer Cohen said:
If you used the two queries as I suggested then can you post the SQL to both
of them?

But before you do that, if the date field contain the time, then you need to
remove the time from the date field in the first query

Select Id , DateValue(DateField) As NewFieldName
From TableName
Group By Id , DateValue(DateField)

And then run the second query
--
Good Luck
BS"D


:

Hi, Thanks for getting back to me.

This still doesnt work.

What I am trying to do is..
If I attend a 3 jobs today and 2 Jobs tomorrow, I want to count the number
of days i have worked. so in this case it would be 2 days worked but 5 jobs
completed.

how can i workout how many days worked?

Thanks,
Jez



:

One way will be by creating two queries
1. GroupBy query to group the ID and the Date

Select Id , DateField
From TableName
Group By Id , DateField

2. Another group By query with a count, that based on the above query

Select Id , Count([DateField]) As CountOfDate
From QueryName
Group By Id


--
Good Luck
BS"D


:

Hi,

Please help...

I have a table of data holding Engineers ID's and Visit Dates of when they
attended work jobs. I am wanting to Count the Visit Dates but where he does
multiple visits in 1 day I only want to count it once.

How can I do this in a query?

Thanks,
Jez
Click to expand...
Click to expand...
Click to expand...
 
Copy and paste the SQL I provided you with, change the name of the fields and
the table if needed, it worked for me.
Unless as I said in the second post, if the date field inlude also the time
then you need to use the DateValue

--
Good Luck
BS"D


Jez said:
Thanks for getting back to me...

but it still doesnt answer my question. The query just adds up all the
EngineerID.

I am trying to workout how many days worked and not jobs done.

EngineerID VisitDate
123 10-Jul-06
123 10-Jul-06
123 09Jul-06
123 09Jul-06
123 08Jul-06
1234 10-Jul-06

I want my query to do as follows..

EngineerID DaysWorked
123 3 as he worked 8,9,10 July but actually did 5 Jobs
1234 1

so when my query runs I want the answer to show me 3 in this case as that
how many days worked.

Thanks,
Jez



Ofer Cohen said:
First Query, save it as "DateGroup"

SELECT VisitDate, EngineerID
FROM TableName
GROUP BY VisitDate, EngineerID

Second Query

SELECT EngineerID, Count(EngineerID) AS DaysWorked
FROM DateGroup
GROUP BY EngineerID

--
Good Luck
BS"D


Jez said:
Thanks, that now works as I understand it better.

Its still not what I am looking for though...

My table shows

EngineerID VisitDate
123 10-Jul-06
123 10-Jul-06
123 09Jul-06
123 09Jul-06
123 08Jul-06
1234 10-Jul-06

I want my query to do as follows..

EngineerID DaysWorked
123 3
1234 1

Thanks,
Jez

:

If you used the two queries as I suggested then can you post the SQL to both
of them?

But before you do that, if the date field contain the time, then you need to
remove the time from the date field in the first query

Select Id , DateValue(DateField) As NewFieldName
From TableName
Group By Id , DateValue(DateField)

And then run the second query
--
Good Luck
BS"D


:

Hi, Thanks for getting back to me.

This still doesnt work.

What I am trying to do is..
If I attend a 3 jobs today and 2 Jobs tomorrow, I want to count the number
of days i have worked. so in this case it would be 2 days worked but 5 jobs
completed.

how can i workout how many days worked?

Thanks,
Jez



:

One way will be by creating two queries
1. GroupBy query to group the ID and the Date

Select Id , DateField
From TableName
Group By Id , DateField

2. Another group By query with a count, that based on the above query

Select Id , Count([DateField]) As CountOfDate
From QueryName
Group By Id


--
Good Luck
BS"D


:

Hi,

Please help...

I have a table of data holding Engineers ID's and Visit Dates of when they
attended work jobs. I am wanting to Count the Visit Dates but where he does
multiple visits in 1 day I only want to count it once.

How can I do this in a query?

Thanks,
Jez
Click to expand...
Click to expand...
Click to expand...
 
Ofer said:
Copy and paste the SQL I provided you with, change the name of the fields and
the table if needed, it worked for me.
Unless as I said in the second post, if the date field inlude also the time
then you need to use the DateValue
Click to expand...

Substituting the names/data (i.e. no time elements) the OP posted,
here's one query:

SELECT DT1.EngineerID, COUNT(*) AS DaysWorked,
SUM(DT1.JobsEachVisitDate) AS Jobs
FROM
(
SELECT W1.EngineerID, W1.VisitDate, COUNT(*) AS JobsEachVisitDate
FROM WorkJobs AS W1
GROUP BY W1.EngineerID, W1.VisitDate
) AS DT1
GROUP BY DT1.EngineerID;

And here's some VBA to recreate the data and demo the SQL code:

Sub workjobs()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection
' Note: this table is missing a key!
.Execute _
"CREATE TABLE WorkJobs (EngineerID INTEGER" & _
" NOT NULL, VisitDate DATETIME NOT NULL," & _
" CONSTRAINT VisitDate__no_time_part CHECK(" & _
" HOUR(VisitDate) = 0 AND MINUTE(VisitDate)" & _
" = 0 AND SECOND(VisitDate) = 0)); "
.Execute _
"INSERT INTO WorkJobs (EngineerID, VisitDate)" & _
" VALUES (123, #2006-07-10 00:00:00#);"
.Execute _
"INSERT INTO WorkJobs (EngineerID, VisitDate)" & _
" VALUES (123, #2006-07-10 00:00:00#);"
.Execute _
"INSERT INTO WorkJobs (EngineerID, VisitDate)" & _
" VALUES (123, #2006-07-09 00:00:00#);"
.Execute _
"INSERT INTO WorkJobs (EngineerID, VisitDate)" & _
" VALUES (123, #2006-07-09 00:00:00#);"
.Execute _
"INSERT INTO WorkJobs (EngineerID, VisitDate)" & _
" VALUES (123, #2006-07-08 00:00:00#);"
.Execute _
"INSERT INTO WorkJobs (EngineerID, VisitDate)" & _
" VALUES (1234, #2006-07-10 00:00:00#);"

Dim rs
Set rs = .Execute( _
"SELECT DT1.EngineerID, COUNT(*) AS DaysWorked," & _
" SUM(DT1.JobsEachVisitDate) AS Jobs FROM" & _
" (SELECT W1.EngineerID, W1.VisitDate, COUNT(*)" & _
" AS JobsEachVisitDate FROM WorkJobs AS W1" & _
" GROUP BY W1.EngineerID, W1.VisitDate) AS" & _
" DT1 GROUP BY DT1.EngineerID;")
Dim sCols As String
Dim f
For Each f In rs.Fields
sCols = sCols & f.Name & vbTab
Next
sCols = Left$(sCols, Len(sCols) - Len(vbTab))
MsgBox sCols & vbCr & rs.GetString(2, , vbTab & vbTab)
rs.Close

End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--
 
Back
Top