Counting of Dates

[Jez]

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

 

[Ofer Cohen]

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

 

[Jez]

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

 

[Ofer Cohen]

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

 

[Jez]

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

 

[Ofer Cohen]

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

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

 

[Jez]

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

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

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

 

[Ofer Cohen]

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

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

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

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