Hours for month as value in query

[AccessKay]

Hi Everyone,

I don’t know if this is a simple or complex request but it’s definitely
complex for me.
I want to have a field in my query that does the following:

If Month has 5 Fridays, Then value=200
If Month has 4 Fridays, Then value =160

I would assume that I’d need a parameter to ask for the month when I open
the query. I’m going to use this field in a calculation with another field.

Can anyone help me with this or point me in the right direction or am I
assuming too much here as far as the capabilities of Access?

Any suggestions will be greatly appreciated.

Kay

 

[vanderghast]

DateDiff( "ww", DateSerial( year, month, 0), DateSerial(year, month+1, 0),
FirstDayOfWeek := vbFriday)


At least, tested for July 2010, that gives:

? DateDiff("ww", DateSerial(2010, 7, 0), DateSerial(2010, 8, 0),
FirstDayOfWeek := vbFriday)
5


while for June 2010:

? DateDiff("ww", DateSerial(2010, 6, 0), DateSerial(2010, 7, 0),
FirstDayOfWeek := vbFriday)
4



and also, for October 2010:

? DateDiff("ww", DateSerial(2010, 10, 0), DateSerial(2010, 11, 0),
FirstDayOfWeek:= vbFriday)
5



If used in a query, replace vbFriday by its value, 6.



Vanderghast, Access MVP

 

[vanderghast]

Well, that was returning the number of Fridays, multiply by 40 to get what
you expected.

Vanderghast, Access MVP

 

[AccessKay]

Thanks but I’m not sure that I understand. So will I manually need to enter
the year, month and month+1 each month when I run this query? Also, I don’t
understand your last statement about replacing vbFriday by it’s value 6.
Here is the SQL to my query. I entered the function you gave me (used July).
I want a column to divide TPossHrs by Billable hours. And then is there a
way where I don’t have to change it each month?

SELECT tblKay.Empl, Sum(Abs([Type]="Billable")*[THours]) AS Billable,
Sum(Abs([Type]="Indirect")*[THours]) AS Indirect, Sum(tblKay.THours) AS THrs,
(DateDiff("ww",DateSerial(2010,7,0),DateSerial(2010,8,0)))*40 AS TPossHrs
FROM tblKay
GROUP BY tblKay.Empl;

Thanks,
Kay

 

[vanderghast]

If you don't have a field supplying a full date, yes, indeed, you will need
to supply that information, since it cannot get it from somewhere else... If
there is a field with a full date, you can then use Year(fullDateField) ,
Month(fullDateField) and Month(fullDateField) + 1 :


SELECT tblKay.Empl, Sum(Abs([Type]="Billable")*[THours]) AS Billable,
Sum(Abs([Type]="Indirect")*[THours]) AS Indirect, Sum(tblKay.THours) AS
THrs,
(DateDiff("ww",
DateSerial(YEAR(fullDate),MONTH(fullDate),0),
DateSerial(YEAR(fullDate),MONTH(fullDate) + 1 ,0),
6))*40 AS TPossHrs
FROM tblKay
GROUP BY tblKay.Empl;

I also included the required 6 (vbFriday). As is it, if there is not field
with the name fullDate, you should be prompted for it, as it becomes a
parameter. Note that in fact, if there is a field called fullDate, you
technically need:


SELECT tblKay.Empl, Sum(Abs([Type]="Billable")*[THours]) AS Billable,
Sum(Abs([Type]="Indirect")*[THours]) AS Indirect, Sum(tblKay.THours) AS
THrs,
(DateDiff("ww",
DateSerial(YEAR(fullDate),MONTH(fullDate),0),
DateSerial(YEAR(fullDate),MONTH(fullDate) + 1 ,0),
6))*40 AS TPossHrs
FROM tblKay
GROUP BY tblKay.Empl, YEAR(fullDate), MONTH(fullDate)


to get the sums: by employee, by year, by month

Vanderghast, Access MVP

Thanks but I’m not sure that I understand. So will I manually need to
enter
the year, month and month+1 each month when I run this query? Also, I don’t
understand your last statement about replacing vbFriday by it’s value 6.
Here is the SQL to my query. I entered the function you gave me (used
July).
I want a column to divide TPossHrs by Billable hours. And then is there a
way where I don’t have to change it each month?

SELECT tblKay.Empl, Sum(Abs([Type]="Billable")*[THours]) AS Billable,
Sum(Abs([Type]="Indirect")*[THours]) AS Indirect, Sum(tblKay.THours) AS
THrs,
(DateDiff("ww",DateSerial(2010,7,0),DateSerial(2010,8,0)))*40 AS TPossHrs
FROM tblKay
GROUP BY tblKay.Empl;

Thanks,
Kay

Well, that was returning the number of Fridays, multiply by 40 to get
what
you expected.

Vanderghast, Access MVP

 

[AccessKay]

I’m loving this but it’s killing me at the same time…I got it all in my query
successfully and understood better about what I was doing…very happy about
this. Thank you for the additional details. But…it’s no longer grouping by
Empl. I just want one line for each employee that will have the total
billable hours, total possible hours…etc. and calculate the Billable Pct
correctly.

And…I don’t want to use THours anymore. I want this to be TPossHrs. I
tried to change it but it’s asking me to enter a parameter value.

Here is my new code with the changes you gave me:

SELECT tblKay.Empl, Sum(Abs([Type]="Billable")*[THours]) AS Billable,
Sum(Abs([Type]="Indirect")*[THours]) AS Indirect, Sum(tblKay.THours) AS THrs,
(DateDiff("ww",DateSerial(Year(fullDate),Month(fullDate),0),DateSerial(Year(fullDate),Month(fullDate)+1,0),6))*40
AS TPossHrs, Month([fullDate]) AS D_Month, tblKay.fullDate,
[Billable]/[TPossHrs] AS BillablePct

FROM tblKay

GROUP BY tblKay.Empl, Month([fullDate]), tblKay.fullDate, Year(fullDate)

HAVING (((Year([fullDate]))=[Enter Year]) AND ((Month([fullDate]))=[Enter
Month]));

I thought maybe I could take off the Month and Year by Group By but it
didn’t like it. And then I tried to change [THours] to [TPossHrs] and that’s
when I got the prompt for a parameter value.

Thanks so much already…I’ll be so thankful if I can finish this.

Kay

If you don't have a field supplying a full date, yes, indeed, you will need
to supply that information, since it cannot get it from somewhere else... If
there is a field with a full date, you can then use Year(fullDateField) ,
Month(fullDateField) and Month(fullDateField) + 1 :


SELECT tblKay.Empl, Sum(Abs([Type]="Billable")*[THours]) AS Billable,
Sum(Abs([Type]="Indirect")*[THours]) AS Indirect, Sum(tblKay.THours) AS
THrs,
(DateDiff("ww",
DateSerial(YEAR(fullDate),MONTH(fullDate),0),
DateSerial(YEAR(fullDate),MONTH(fullDate) + 1 ,0),
6))*40 AS TPossHrs
FROM tblKay
GROUP BY tblKay.Empl;

I also included the required 6 (vbFriday). As is it, if there is not field
with the name fullDate, you should be prompted for it, as it becomes a
parameter. Note that in fact, if there is a field called fullDate, you
technically need:


SELECT tblKay.Empl, Sum(Abs([Type]="Billable")*[THours]) AS Billable,
Sum(Abs([Type]="Indirect")*[THours]) AS Indirect, Sum(tblKay.THours) AS
THrs,
(DateDiff("ww",
DateSerial(YEAR(fullDate),MONTH(fullDate),0),
DateSerial(YEAR(fullDate),MONTH(fullDate) + 1 ,0),
6))*40 AS TPossHrs
FROM tblKay
GROUP BY tblKay.Empl, YEAR(fullDate), MONTH(fullDate)


to get the sums: by employee, by year, by month

Vanderghast, Access MVP

Thanks but I’m not sure that I understand. So will I manually need to
enter
the year, month and month+1 each month when I run this query? Also, I don’t
understand your last statement about replacing vbFriday by it’s value 6.
Here is the SQL to my query. I entered the function you gave me (used
July).
I want a column to divide TPossHrs by Billable hours. And then is there a
way where I don’t have to change it each month?

SELECT tblKay.Empl, Sum(Abs([Type]="Billable")*[THours]) AS Billable,
Sum(Abs([Type]="Indirect")*[THours]) AS Indirect, Sum(tblKay.THours) AS
THrs,
(DateDiff("ww",DateSerial(2010,7,0),DateSerial(2010,8,0)))*40 AS TPossHrs
FROM tblKay
GROUP BY tblKay.Empl;

Thanks,
Kay

Well, that was returning the number of Fridays, multiply by 40 to get
what
you expected.

Vanderghast, Access MVP

 

[vanderghast]

You should remove the fullDate field itself from the SELECT and the GROUP BY
clauses, but keep Month(fullDate) and Year(fullDate).
Bring your actual HAVING clause in the WHERE clause


SELECT tblKay.Empl,

Sum(Abs([Type]="Billable")*[THours]) AS Billable,

Sum(Abs([Type]="Indirect")*[THours]) AS Indirect,

Sum(tblKay.THours) AS THrs,

(DateDiff("ww",DateSerial(Year(fullDate),Month(fullDate),0),DateSerial(Year(fullDate),Month(fullDate)+1,0),6))*40
AS TPossHrs, Month([fullDate]) AS D_Month,

[Billable]/[TPossHrs] AS BillablePct

FROM tblKay

WHERE (((Year([fullDate]))=[Enter Year])
AND ((Month([fullDate]))=[Enter Month]))

GROUP BY tblKay.Empl, Month([fullDate]), Year(fullDate)



Vanderghast, Access MVP

I’m loving this but it’s killing me at the same time…I got it all in my
query
successfully and understood better about what I was doing…very happy about
this. Thank you for the additional details. But…it’s no longer grouping
by
Empl. I just want one line for each employee that will have the total
billable hours, total possible hours…etc. and calculate the Billable Pct
correctly.

And…I don’t want to use THours anymore. I want this to be TPossHrs. I
tried to change it but it’s asking me to enter a parameter value.

Here is my new code with the changes you gave me:

SELECT tblKay.Empl, Sum(Abs([Type]="Billable")*[THours]) AS Billable,
Sum(Abs([Type]="Indirect")*[THours]) AS Indirect, Sum(tblKay.THours) AS
THrs,
(DateDiff("ww",DateSerial(Year(fullDate),Month(fullDate),0),DateSerial(Year(fullDate),Month(fullDate)+1,0),6))*40
AS TPossHrs, Month([fullDate]) AS D_Month, tblKay.fullDate,
[Billable]/[TPossHrs] AS BillablePct

FROM tblKay

GROUP BY tblKay.Empl, Month([fullDate]), tblKay.fullDate, Year(fullDate)

HAVING (((Year([fullDate]))=[Enter Year]) AND ((Month([fullDate]))=[Enter
Month]));

I thought maybe I could take off the Month and Year by Group By but it
didn’t like it. And then I tried to change [THours] to [TPossHrs] and
that’s
when I got the prompt for a parameter value.

Thanks so much already…I’ll be so thankful if I can finish this.

Kay

If you don't have a field supplying a full date, yes, indeed, you will
need
to supply that information, since it cannot get it from somewhere else...
If
there is a field with a full date, you can then use Year(fullDateField)
,
Month(fullDateField) and Month(fullDateField) + 1 :


SELECT tblKay.Empl, Sum(Abs([Type]="Billable")*[THours]) AS Billable,
Sum(Abs([Type]="Indirect")*[THours]) AS Indirect, Sum(tblKay.THours) AS
THrs,
(DateDiff("ww",
DateSerial(YEAR(fullDate),MONTH(fullDate),0),
DateSerial(YEAR(fullDate),MONTH(fullDate) + 1 ,0),
6))*40 AS TPossHrs
FROM tblKay
GROUP BY tblKay.Empl;

I also included the required 6 (vbFriday). As is it, if there is not
field
with the name fullDate, you should be prompted for it, as it becomes a
parameter. Note that in fact, if there is a field called fullDate, you
technically need:


SELECT tblKay.Empl, Sum(Abs([Type]="Billable")*[THours]) AS Billable,
Sum(Abs([Type]="Indirect")*[THours]) AS Indirect, Sum(tblKay.THours) AS
THrs,
(DateDiff("ww",
DateSerial(YEAR(fullDate),MONTH(fullDate),0),
DateSerial(YEAR(fullDate),MONTH(fullDate) + 1 ,0),
6))*40 AS TPossHrs
FROM tblKay
GROUP BY tblKay.Empl, YEAR(fullDate), MONTH(fullDate)


to get the sums: by employee, by year, by month

Vanderghast, Access MVP

Thanks but I’m not sure that I understand. So will I manually need to
enter
the year, month and month+1 each month when I run this query? Also, I
don’t
understand your last statement about replacing vbFriday by it’s value
6.
Here is the SQL to my query. I entered the function you gave me (used
July).
I want a column to divide TPossHrs by Billable hours. And then is
there a
way where I don’t have to change it each month?

SELECT tblKay.Empl, Sum(Abs([Type]="Billable")*[THours]) AS Billable,
Sum(Abs([Type]="Indirect")*[THours]) AS Indirect, Sum(tblKay.THours) AS
THrs,
(DateDiff("ww",DateSerial(2010,7,0),DateSerial(2010,8,0)))*40 AS
TPossHrs
FROM tblKay
GROUP BY tblKay.Empl;

Thanks,
Kay



:

Well, that was returning the number of Fridays, multiply by 40 to get
what
you expected.

Vanderghast, Access MVP

 

[AccessKay]

Thank you so much! I could have never thought of this myself but hope to
learn from this.

Kay

You should remove the fullDate field itself from the SELECT and the GROUP BY
clauses, but keep Month(fullDate) and Year(fullDate).
Bring your actual HAVING clause in the WHERE clause


SELECT tblKay.Empl,

Sum(Abs([Type]="Billable")*[THours]) AS Billable,

Sum(Abs([Type]="Indirect")*[THours]) AS Indirect,

Sum(tblKay.THours) AS THrs,

(DateDiff("ww",DateSerial(Year(fullDate),Month(fullDate),0),DateSerial(Year(fullDate),Month(fullDate)+1,0),6))*40
AS TPossHrs, Month([fullDate]) AS D_Month,

[Billable]/[TPossHrs] AS BillablePct

FROM tblKay

WHERE (((Year([fullDate]))=[Enter Year])
AND ((Month([fullDate]))=[Enter Month]))

GROUP BY tblKay.Empl, Month([fullDate]), Year(fullDate)



Vanderghast, Access MVP

I’m loving this but it’s killing me at the same time…I got it all in my
query
successfully and understood better about what I was doing…very happy about
this. Thank you for the additional details. But…it’s no longer grouping
by
Empl. I just want one line for each employee that will have the total
billable hours, total possible hours…etc. and calculate the Billable Pct
correctly.

And…I don’t want to use THours anymore. I want this to be TPossHrs. I
tried to change it but it’s asking me to enter a parameter value.

Here is my new code with the changes you gave me:

SELECT tblKay.Empl, Sum(Abs([Type]="Billable")*[THours]) AS Billable,
Sum(Abs([Type]="Indirect")*[THours]) AS Indirect, Sum(tblKay.THours) AS
THrs,
(DateDiff("ww",DateSerial(Year(fullDate),Month(fullDate),0),DateSerial(Year(fullDate),Month(fullDate)+1,0),6))*40
AS TPossHrs, Month([fullDate]) AS D_Month, tblKay.fullDate,
[Billable]/[TPossHrs] AS BillablePct

FROM tblKay

GROUP BY tblKay.Empl, Month([fullDate]), tblKay.fullDate, Year(fullDate)

HAVING (((Year([fullDate]))=[Enter Year]) AND ((Month([fullDate]))=[Enter
Month]));

I thought maybe I could take off the Month and Year by Group By but it
didn’t like it. And then I tried to change [THours] to [TPossHrs] and
that’s
when I got the prompt for a parameter value.

Thanks so much already…I’ll be so thankful if I can finish this.

Kay

If you don't have a field supplying a full date, yes, indeed, you will
need
to supply that information, since it cannot get it from somewhere else...
If
there is a field with a full date, you can then use Year(fullDateField)
,
Month(fullDateField) and Month(fullDateField) + 1 :


SELECT tblKay.Empl, Sum(Abs([Type]="Billable")*[THours]) AS Billable,
Sum(Abs([Type]="Indirect")*[THours]) AS Indirect, Sum(tblKay.THours) AS
THrs,
(DateDiff("ww",
DateSerial(YEAR(fullDate),MONTH(fullDate),0),
DateSerial(YEAR(fullDate),MONTH(fullDate) + 1 ,0),
6))*40 AS TPossHrs
FROM tblKay
GROUP BY tblKay.Empl;

I also included the required 6 (vbFriday). As is it, if there is not
field
with the name fullDate, you should be prompted for it, as it becomes a
parameter. Note that in fact, if there is a field called fullDate, you
technically need:


SELECT tblKay.Empl, Sum(Abs([Type]="Billable")*[THours]) AS Billable,
Sum(Abs([Type]="Indirect")*[THours]) AS Indirect, Sum(tblKay.THours) AS
THrs,
(DateDiff("ww",
DateSerial(YEAR(fullDate),MONTH(fullDate),0),
DateSerial(YEAR(fullDate),MONTH(fullDate) + 1 ,0),
6))*40 AS TPossHrs
FROM tblKay
GROUP BY tblKay.Empl, YEAR(fullDate), MONTH(fullDate)


to get the sums: by employee, by year, by month

Vanderghast, Access MVP


Thanks but I’m not sure that I understand. So will I manually need to
enter
the year, month and month+1 each month when I run this query? Also, I
don’t
understand your last statement about replacing vbFriday by it’s value
6.
Here is the SQL to my query. I entered the function you gave me (used
July).
I want a column to divide TPossHrs by Billable hours. And then is
there a
way where I don’t have to change it each month?

SELECT tblKay.Empl, Sum(Abs([Type]="Billable")*[THours]) AS Billable,
Sum(Abs([Type]="Indirect")*[THours]) AS Indirect, Sum(tblKay.THours) AS
THrs,
(DateDiff("ww",DateSerial(2010,7,0),DateSerial(2010,8,0)))*40 AS
TPossHrs
FROM tblKay
GROUP BY tblKay.Empl;

Thanks,
Kay



:

Well, that was returning the number of Fridays, multiply by 40 to get
what
you expected.

Vanderghast, Access MVP