Calculating certain Days of the week

K

KRB

I have a query that I need to find out how many absences occured on Monday's
and Fridays. I have a JobStart date field and Jobenddate field. These fields
determine the date range that the employee was absent. I have this formula
below to get the number of days absent between the JobStart Date and
JobEnddate:
DateDiff("d",[JobStartDate],[JobEnddate])+1
So Now I have my Total number of days that the employee was absent, but now
I need to know what day of the week that those absences occured. I do not
understand on how to use VB or the modules. It would be great if you could
help me do this as a query! If I you need more detail, I will be glad to
provide.

Thanks,
 
M

MGFoster

KRB said:
I have a query that I need to find out how many absences occured on Monday's
and Fridays. I have a JobStart date field and Jobenddate field. These fields
determine the date range that the employee was absent. I have this formula
below to get the number of days absent between the JobStart Date and
JobEnddate:
DateDiff("d",[JobStartDate],[JobEnddate])+1
So Now I have my Total number of days that the employee was absent, but now
I need to know what day of the week that those absences occured. I do not
understand on how to use VB or the modules. It would be great if you could
help me do this as a query! If I you need more detail, I will be glad to
provide.

Thanks,
Click to expand...

Weekday(some_date) -> the week day number (1=Sunday, 7=Saturday)
 
K

KRB

Ok, that works good for if I want just one day, but how would I use this to
determine each day with in a date range. For example:
JobStartDate=9/15/05 and JobendDate=9/20/05 the total number of days is 5.
I want it to display each day between that date range for example: Monday,
Tues, Wed etc....

MGFoster said:
KRB said:
I have a query that I need to find out how many absences occured on Monday's
and Fridays. I have a JobStart date field and Jobenddate field. These fields
determine the date range that the employee was absent. I have this formula
below to get the number of days absent between the JobStart Date and
JobEnddate:
DateDiff("d",[JobStartDate],[JobEnddate])+1
So Now I have my Total number of days that the employee was absent, but now
I need to know what day of the week that those absences occured. I do not
understand on how to use VB or the modules. It would be great if you could
help me do this as a query! If I you need more detail, I will be glad to
provide.

Thanks,
Click to expand...

Weekday(some_date) -> the week day number (1=Sunday, 7=Saturday)
Click to expand...
 
K

KARL DEWEY

This should do it for you.

Table named [KRB Labor] and field [People] for employee.

SELECT [KRB Labor].People, Count(Format([JobStartDate]+[CountNUM],"w")) AS
[Total Mon & Fri]
FROM [KRB Labor], CountNumber
WHERE ((([JobStartDate]+[CountNUM])<=IIf([JobEnddate] Is
Null,Date(),[JobEnddate])) AND ((Format([JobStartDate]+[CountNUM],"w"))=2 Or
(Format([JobStartDate]+[CountNUM],"w"))=6))
GROUP BY [KRB Labor].People
ORDER BY [KRB Labor].People;
 
K

KARL DEWEY

I forgot to add that I also used a table named CountNumber with a field
[CountNUM] containing numbers zero through your maximum date spread.

It also fills in if the employee has not returned yet.

KARL DEWEY said:
This should do it for you.

Table named [KRB Labor] and field [People] for employee.

SELECT [KRB Labor].People, Count(Format([JobStartDate]+[CountNUM],"w")) AS
[Total Mon & Fri]
FROM [KRB Labor], CountNumber
WHERE ((([JobStartDate]+[CountNUM])<=IIf([JobEnddate] Is
Null,Date(),[JobEnddate])) AND ((Format([JobStartDate]+[CountNUM],"w"))=2 Or
(Format([JobStartDate]+[CountNUM],"w"))=6))
GROUP BY [KRB Labor].People
ORDER BY [KRB Labor].People;


KRB said:
I have a query that I need to find out how many absences occured on Monday's
and Fridays. I have a JobStart date field and Jobenddate field. These fields
determine the date range that the employee was absent. I have this formula
below to get the number of days absent between the JobStart Date and
JobEnddate:
DateDiff("d",[JobStartDate],[JobEnddate])+1
So Now I have my Total number of days that the employee was absent, but now
I need to know what day of the week that those absences occured. I do not
understand on how to use VB or the modules. It would be great if you could
help me do this as a query! If I you need more detail, I will be glad to
provide.

Thanks,
Click to expand...
Click to expand...
 
K

KRB

Ok, stupid question, but where would I insert this into my query. What I gave
you was only part of the query. I'm not good with programming at all. Here is
my SQL statment I currently have:

SELECT Site.Name, Person.Number, Position.Name, Job.JobStartDate,
Job.JobEndDate, Job.JobStartTime, Job.JobEndTime, Reason.Name,
PositionGroupMembr.GroupID, SiteGroupMember.GroupID, Reason.ID,
DateDiff("d",[JobStartDate],[JobEnddate])+1 AS Days,
DateDiff("h",[JobStarttime],[JobEndtime]) AS Hours, IIf([hours]>4,"1") AS
Whole, IIf([hours]<=4,".5") AS Half, IIf([hours]<=4,".5","1") AS Type,
IIf([reason.id]=17,17,1) AS Prof, Format([jobstartdate],"dddd") AS [Start
Day], Format([jobenddate],"dddd") AS [End Day], Weekday([jobstartdate]) AS
Expr3
FROM (((PositionGroupMembr INNER JOIN [Position] ON
PositionGroupMembr.PositionID = Position.ID) INNER JOIN (((((Person INNER
JOIN Employee ON Person.ID = Employee.ID) INNER JOIN Site ON Person.SiteID =
Site.ID) INNER JOIN SiteGroupMember ON Site.ID = SiteGroupMember.SiteID)
INNER JOIN Job ON Employee.ID = Job.EmployeeID) INNER JOIN JobStatus ON
Job.Status = JobStatus.ID) ON Position.ID = Employee.PrimaryPosition) INNER
JOIN Reason ON Job.ReasonID = Reason.ID) INNER JOIN (Query11 INNER JOIN
CalendarTrack ON Query11.Name = CalendarTrack.Name) ON Employee.CalendarTrack
= CalendarTrack.ID
GROUP BY Site.Name, Person.Number, Position.Name, Job.JobStartDate,
Job.JobEndDate, Job.JobStartTime, Job.JobEndTime, Reason.Name,
PositionGroupMembr.GroupID, SiteGroupMember.GroupID, Reason.ID,
DateDiff("h",[JobStarttime],[JobEndtime]), Query11.[Hours per Day],
Query11.[Half Day Hours]
HAVING (((Job.JobStartDate)>=#8/11/2005#) AND
((Job.JobEndDate)<=#5/24/2006#) AND ((PositionGroupMembr.GroupID)=576 Or
(PositionGroupMembr.GroupID)=578) AND ((SiteGroupMember.GroupID)=175 Or
(SiteGroupMember.GroupID)=176 Or (SiteGroupMember.GroupID)=177 Or
(SiteGroupMember.GroupID)=178));


KARL DEWEY said:
I forgot to add that I also used a table named CountNumber with a field
[CountNUM] containing numbers zero through your maximum date spread.

It also fills in if the employee has not returned yet.

KARL DEWEY said:
This should do it for you.

Table named [KRB Labor] and field [People] for employee.

SELECT [KRB Labor].People, Count(Format([JobStartDate]+[CountNUM],"w")) AS
[Total Mon & Fri]
FROM [KRB Labor], CountNumber
WHERE ((([JobStartDate]+[CountNUM])<=IIf([JobEnddate] Is
Null,Date(),[JobEnddate])) AND ((Format([JobStartDate]+[CountNUM],"w"))=2 Or
(Format([JobStartDate]+[CountNUM],"w"))=6))
GROUP BY [KRB Labor].People
ORDER BY [KRB Labor].People;


KRB said:
I have a query that I need to find out how many absences occured on Monday's
and Fridays. I have a JobStart date field and Jobenddate field. These fields
determine the date range that the employee was absent. I have this formula
below to get the number of days absent between the JobStart Date and
JobEnddate:
DateDiff("d",[JobStartDate],[JobEnddate])+1
So Now I have my Total number of days that the employee was absent, but now
I need to know what day of the week that those absences occured. I do not
understand on how to use VB or the modules. It would be great if you could
help me do this as a query! If I you need more detail, I will be glad to
provide.

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

I would suggest building a separate query for this and then opening your
orignal query in design view and doing a left join on Person.Number.

In looking at your query it seems that it might not work if someone never
was absent from the job.

KRB said:
Ok, stupid question, but where would I insert this into my query. What I gave
you was only part of the query. I'm not good with programming at all. Here is
my SQL statment I currently have:

SELECT Site.Name, Person.Number, Position.Name, Job.JobStartDate,
Job.JobEndDate, Job.JobStartTime, Job.JobEndTime, Reason.Name,
PositionGroupMembr.GroupID, SiteGroupMember.GroupID, Reason.ID,
DateDiff("d",[JobStartDate],[JobEnddate])+1 AS Days,
DateDiff("h",[JobStarttime],[JobEndtime]) AS Hours, IIf([hours]>4,"1") AS
Whole, IIf([hours]<=4,".5") AS Half, IIf([hours]<=4,".5","1") AS Type,
IIf([reason.id]=17,17,1) AS Prof, Format([jobstartdate],"dddd") AS [Start
Day], Format([jobenddate],"dddd") AS [End Day], Weekday([jobstartdate]) AS
Expr3
FROM (((PositionGroupMembr INNER JOIN [Position] ON
PositionGroupMembr.PositionID = Position.ID) INNER JOIN (((((Person INNER
JOIN Employee ON Person.ID = Employee.ID) INNER JOIN Site ON Person.SiteID =
Site.ID) INNER JOIN SiteGroupMember ON Site.ID = SiteGroupMember.SiteID)
INNER JOIN Job ON Employee.ID = Job.EmployeeID) INNER JOIN JobStatus ON
Job.Status = JobStatus.ID) ON Position.ID = Employee.PrimaryPosition) INNER
JOIN Reason ON Job.ReasonID = Reason.ID) INNER JOIN (Query11 INNER JOIN
CalendarTrack ON Query11.Name = CalendarTrack.Name) ON Employee.CalendarTrack
= CalendarTrack.ID
GROUP BY Site.Name, Person.Number, Position.Name, Job.JobStartDate,
Job.JobEndDate, Job.JobStartTime, Job.JobEndTime, Reason.Name,
PositionGroupMembr.GroupID, SiteGroupMember.GroupID, Reason.ID,
DateDiff("h",[JobStarttime],[JobEndtime]), Query11.[Hours per Day],
Query11.[Half Day Hours]
HAVING (((Job.JobStartDate)>=#8/11/2005#) AND
((Job.JobEndDate)<=#5/24/2006#) AND ((PositionGroupMembr.GroupID)=576 Or
(PositionGroupMembr.GroupID)=578) AND ((SiteGroupMember.GroupID)=175 Or
(SiteGroupMember.GroupID)=176 Or (SiteGroupMember.GroupID)=177 Or
(SiteGroupMember.GroupID)=178));


KARL DEWEY said:
I forgot to add that I also used a table named CountNumber with a field
[CountNUM] containing numbers zero through your maximum date spread.

It also fills in if the employee has not returned yet.

KARL DEWEY said:
This should do it for you.

Table named [KRB Labor] and field [People] for employee.

SELECT [KRB Labor].People, Count(Format([JobStartDate]+[CountNUM],"w")) AS
[Total Mon & Fri]
FROM [KRB Labor], CountNumber
WHERE ((([JobStartDate]+[CountNUM])<=IIf([JobEnddate] Is
Null,Date(),[JobEnddate])) AND ((Format([JobStartDate]+[CountNUM],"w"))=2 Or
(Format([JobStartDate]+[CountNUM],"w"))=6))
GROUP BY [KRB Labor].People
ORDER BY [KRB Labor].People;


:

I have a query that I need to find out how many absences occured on Monday's
and Fridays. I have a JobStart date field and Jobenddate field. These fields
determine the date range that the employee was absent. I have this formula
below to get the number of days absent between the JobStart Date and
JobEnddate:
DateDiff("d",[JobStartDate],[JobEnddate])+1
So Now I have my Total number of days that the employee was absent, but now
I need to know what day of the week that those absences occured. I do not
understand on how to use VB or the modules. It would be great if you could
help me do this as a query! If I you need more detail, I will be glad to
provide.

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

Just a little snippet that may help with the problem.
Create a Calculated field for each day of the week. The calculation will
look something like:
Sunday: IIf(Weekday([holdate])=1,IIf([hours]<=4,"Half","Whole") )
....
Saturday: IIf(Weekday([holdate])=7,IIf([hours]<=4,"Half","Whole"))

I noticed there are some calculations to determine if it was a half day, a
whole day, or whatever.

KARL DEWEY said:
I would suggest building a separate query for this and then opening your
orignal query in design view and doing a left join on Person.Number.

In looking at your query it seems that it might not work if someone never
was absent from the job.

KRB said:
Ok, stupid question, but where would I insert this into my query. What I gave
you was only part of the query. I'm not good with programming at all. Here is
my SQL statment I currently have:

SELECT Site.Name, Person.Number, Position.Name, Job.JobStartDate,
Job.JobEndDate, Job.JobStartTime, Job.JobEndTime, Reason.Name,
PositionGroupMembr.GroupID, SiteGroupMember.GroupID, Reason.ID,
DateDiff("d",[JobStartDate],[JobEnddate])+1 AS Days,
DateDiff("h",[JobStarttime],[JobEndtime]) AS Hours, IIf([hours]>4,"1") AS
Whole, IIf([hours]<=4,".5") AS Half, IIf([hours]<=4,".5","1") AS Type,
IIf([reason.id]=17,17,1) AS Prof, Format([jobstartdate],"dddd") AS [Start
Day], Format([jobenddate],"dddd") AS [End Day], Weekday([jobstartdate]) AS
Expr3
FROM (((PositionGroupMembr INNER JOIN [Position] ON
PositionGroupMembr.PositionID = Position.ID) INNER JOIN (((((Person INNER
JOIN Employee ON Person.ID = Employee.ID) INNER JOIN Site ON Person.SiteID =
Site.ID) INNER JOIN SiteGroupMember ON Site.ID = SiteGroupMember.SiteID)
INNER JOIN Job ON Employee.ID = Job.EmployeeID) INNER JOIN JobStatus ON
Job.Status = JobStatus.ID) ON Position.ID = Employee.PrimaryPosition) INNER
JOIN Reason ON Job.ReasonID = Reason.ID) INNER JOIN (Query11 INNER JOIN
CalendarTrack ON Query11.Name = CalendarTrack.Name) ON Employee.CalendarTrack
= CalendarTrack.ID
GROUP BY Site.Name, Person.Number, Position.Name, Job.JobStartDate,
Job.JobEndDate, Job.JobStartTime, Job.JobEndTime, Reason.Name,
PositionGroupMembr.GroupID, SiteGroupMember.GroupID, Reason.ID,
DateDiff("h",[JobStarttime],[JobEndtime]), Query11.[Hours per Day],
Query11.[Half Day Hours]
HAVING (((Job.JobStartDate)>=#8/11/2005#) AND
((Job.JobEndDate)<=#5/24/2006#) AND ((PositionGroupMembr.GroupID)=576 Or
(PositionGroupMembr.GroupID)=578) AND ((SiteGroupMember.GroupID)=175 Or
(SiteGroupMember.GroupID)=176 Or (SiteGroupMember.GroupID)=177 Or
(SiteGroupMember.GroupID)=178));


KARL DEWEY said:
I forgot to add that I also used a table named CountNumber with a field
[CountNUM] containing numbers zero through your maximum date spread.

It also fills in if the employee has not returned yet.

:

This should do it for you.

Table named [KRB Labor] and field [People] for employee.

SELECT [KRB Labor].People, Count(Format([JobStartDate]+[CountNUM],"w")) AS
[Total Mon & Fri]
FROM [KRB Labor], CountNumber
WHERE ((([JobStartDate]+[CountNUM])<=IIf([JobEnddate] Is
Null,Date(),[JobEnddate])) AND ((Format([JobStartDate]+[CountNUM],"w"))=2 Or
(Format([JobStartDate]+[CountNUM],"w"))=6))
GROUP BY [KRB Labor].People
ORDER BY [KRB Labor].People;


:

I have a query that I need to find out how many absences occured on Monday's
and Fridays. I have a JobStart date field and Jobenddate field. These fields
determine the date range that the employee was absent. I have this formula
below to get the number of days absent between the JobStart Date and
JobEnddate:
DateDiff("d",[JobStartDate],[JobEnddate])+1
So Now I have my Total number of days that the employee was absent, but now
I need to know what day of the week that those absences occured. I do not
understand on how to use VB or the modules. It would be great if you could
help me do this as a query! If I you need more detail, I will be glad to
provide.

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

Display weeks from a Milestone 0
Calculating Dates 2
Crosstab query to include fields with Null value 2
Min/Max Dates 1
Group by Weeks of the Month 10
Create a Control in a form that can store a cumulative count 2
Query Help 3
Show data source in Union Select All Queries 2

Top