Query expression help

C

Chad

Hello, I want to run a query that would give me a total for the current
quarter for the year instead of puting in a date range. I have a formula
[expression] in my query that I want this for. Please help!

Here is my expression:
Delay Percent: Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT Reason 1]-[DT
MAINTENANCE]-[DT Reason 2]))
 
C

Chad

I figured out how to get what dates were in what quarter by my quarter
expression. I want an Daily percent for an employee in the current quarter.
Hope this helps...

SELECT tblMain.[EMPLOYEE NAME], Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT
Reason 1]-[DT MAINTENANCE]-[DT Reason 2])) AS [Delay Percent]
FROM tblEmployees INNER JOIN tblMain ON tblEmployees.EmployeeName =
tblMain.[EMPLOYEE NAME]
GROUP BY tblMain.ID, tblMain.[DAYS DATE], tblMain.[EMPLOYEE NAME],
DatePart("q",[DAYS DATE]), tblEmployees.Status
HAVING (((tblEmployees.Status)=False));
 
J

John Spencer

Does this mean you have solved your problem?

First Day of Current Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1,1)

Last Day of Current Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4,0)

First Day of Prior Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1-3,1)

Last Day of Prior Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4-1,0)

If you want data for just the current quarter of the current year then you
might use the following SQL

SELECT tblMain.[EMPLOYEE NAME], Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT
Reason 1]-[DT MAINTENANCE]-[DT Reason 2])) AS [Delay Percent]
FROM tblEmployees INNER JOIN tblMain ON tblEmployees.EmployeeName =
tblMain.[EMPLOYEE NAME]
WHERE tblMain.[DAYS DATE] Between
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1,1)
and DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4,0)
AND tblEmployees.Status=False
GROUP BY tblMain.ID, tblMain.[DAYS DATE]
, tblMain.[EMPLOYEE NAME]
, DatePart("q",[DAYS DATE]), tblEmployees.Status



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Chad said:
I figured out how to get what dates were in what quarter by my quarter
expression. I want an Daily percent for an employee in the current
quarter.
Hope this helps...

SELECT tblMain.[EMPLOYEE NAME], Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT
Reason 1]-[DT MAINTENANCE]-[DT Reason 2])) AS [Delay Percent]
FROM tblEmployees INNER JOIN tblMain ON tblEmployees.EmployeeName =
tblMain.[EMPLOYEE NAME]
GROUP BY tblMain.ID, tblMain.[DAYS DATE], tblMain.[EMPLOYEE NAME],
DatePart("q",[DAYS DATE]), tblEmployees.Status
HAVING (((tblEmployees.Status)=False));

--
Newbies need extra loven.........


Chad said:
Hello, I want to run a query that would give me a total for the current
quarter for the year instead of puting in a date range. I have a formula
[expression] in my query that I want this for. Please help!

Here is my expression:
Delay Percent: Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT Reason 1]-[DT
MAINTENANCE]-[DT Reason 2]))
Click to expand...
Click to expand...
 
C

Chad

John, What I wanted to do was find the total percent for an employee for the
quarter using this formula to find the percent but I cant figure out how to
just get it to show for the current quarter. Like a quarterly total but just
for the current qurent quarter. Here is what im using to find the employees
percent: Thanks!

Delay Percent: Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT Reason 1]-[DT
MAINTENANCE]-[DT Reason 2]))



--
Newbies need extra loven.........


John Spencer said:
Does this mean you have solved your problem?

First Day of Current Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1,1)

Last Day of Current Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4,0)

First Day of Prior Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1-3,1)

Last Day of Prior Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4-1,0)

If you want data for just the current quarter of the current year then you
might use the following SQL

SELECT tblMain.[EMPLOYEE NAME], Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT
Reason 1]-[DT MAINTENANCE]-[DT Reason 2])) AS [Delay Percent]
FROM tblEmployees INNER JOIN tblMain ON tblEmployees.EmployeeName =
tblMain.[EMPLOYEE NAME]
WHERE tblMain.[DAYS DATE] Between
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1,1)
and DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4,0)
AND tblEmployees.Status=False
GROUP BY tblMain.ID, tblMain.[DAYS DATE]
, tblMain.[EMPLOYEE NAME]
, DatePart("q",[DAYS DATE]), tblEmployees.Status



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Chad said:
I figured out how to get what dates were in what quarter by my quarter
expression. I want an Daily percent for an employee in the current
quarter.
Hope this helps...

SELECT tblMain.[EMPLOYEE NAME], Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT
Reason 1]-[DT MAINTENANCE]-[DT Reason 2])) AS [Delay Percent]
FROM tblEmployees INNER JOIN tblMain ON tblEmployees.EmployeeName =
tblMain.[EMPLOYEE NAME]
GROUP BY tblMain.ID, tblMain.[DAYS DATE], tblMain.[EMPLOYEE NAME],
DatePart("q",[DAYS DATE]), tblEmployees.Status
HAVING (((tblEmployees.Status)=False));

--
Newbies need extra loven.........


Chad said:
Hello, I want to run a query that would give me a total for the current
quarter for the year instead of puting in a date range. I have a formula
[expression] in my query that I want this for. Please help!

Here is my expression:
Delay Percent: Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT Reason 1]-[DT
MAINTENANCE]-[DT Reason 2]))
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

DID YOU TRY the query I posted. It should calculate the sum for the current
quarter only?

If you tried the query and it failed to give you the desired results, can
you tell us what the problem with the results are?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Chad said:
John, What I wanted to do was find the total percent for an employee for
the
quarter using this formula to find the percent but I cant figure out how
to
just get it to show for the current quarter. Like a quarterly total but
just
for the current qurent quarter. Here is what im using to find the
employees
percent: Thanks!

Delay Percent: Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT Reason 1]-[DT
MAINTENANCE]-[DT Reason 2]))



--
Newbies need extra loven.........


John Spencer said:
Does this mean you have solved your problem?

First Day of Current Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1,1)

Last Day of Current Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4,0)

First Day of Prior Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1-3,1)

Last Day of Prior Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4-1,0)

If you want data for just the current quarter of the current year then
you
might use the following SQL

SELECT tblMain.[EMPLOYEE NAME], Sum([DT REGULAR])/(Sum([EMPLOYEE
TIME]-[DT
Reason 1]-[DT MAINTENANCE]-[DT Reason 2])) AS [Delay Percent]
FROM tblEmployees INNER JOIN tblMain ON tblEmployees.EmployeeName =
tblMain.[EMPLOYEE NAME]
WHERE tblMain.[DAYS DATE] Between
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1,1)
and DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4,0)
AND tblEmployees.Status=False
GROUP BY tblMain.ID, tblMain.[DAYS DATE]
, tblMain.[EMPLOYEE NAME]
, DatePart("q",[DAYS DATE]), tblEmployees.Status



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Chad said:
I figured out how to get what dates were in what quarter by my quarter
expression. I want an Daily percent for an employee in the current
quarter.
Hope this helps...

SELECT tblMain.[EMPLOYEE NAME], Sum([DT REGULAR])/(Sum([EMPLOYEE
TIME]-[DT
Reason 1]-[DT MAINTENANCE]-[DT Reason 2])) AS [Delay Percent]
FROM tblEmployees INNER JOIN tblMain ON tblEmployees.EmployeeName =
tblMain.[EMPLOYEE NAME]
GROUP BY tblMain.ID, tblMain.[DAYS DATE], tblMain.[EMPLOYEE NAME],
DatePart("q",[DAYS DATE]), tblEmployees.Status
HAVING (((tblEmployees.Status)=False));

--
Newbies need extra loven.........


:

Hello, I want to run a query that would give me a total for the
current
quarter for the year instead of puting in a date range. I have a
formula
[expression] in my query that I want this for. Please help!

Here is my expression:
Delay Percent: Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT Reason
1]-[DT
MAINTENANCE]-[DT Reason 2]))
Click to expand...
Click to expand...
Click to expand...
 
C

Chad

John, I got the query to work but it kept saying Im missing a macro when I
tried to go back to design view. anyway I copyed the formula and implimented
it into my query and it works (BUT) instead of giving a percent for an
individual employee it gives every percent for every day in the current
quarter for that one employee. How do I get it to give a total percent for
that employee insted of one for every day in a quarter? Here is the SQL:
Thanks!

SELECT Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT Reason 1]-[DT
MAINTENANCE]-[DT Reason 2])) AS [Delay Percent], tblMain.[EMPLOYEE NAME]
FROM tblEmployees INNER JOIN tblMain ON tblEmployees.EmployeeName =
tblMain.[EMPLOYEE NAME]
WHERE (((tblMain.[DAYS DATE]) Between
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1,1) And
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4,0)))
GROUP BY tblMain.[EMPLOYEE NAME], tblEmployees.Status, tblMain.ID
HAVING (((tblMain.[EMPLOYEE NAME])=[Forms]![frmSidebar].[txtEmployeeName])
AND ((tblEmployees.Status)=False));

--
Newbies need extra loven.........


John Spencer said:
DID YOU TRY the query I posted. It should calculate the sum for the current
quarter only?

If you tried the query and it failed to give you the desired results, can
you tell us what the problem with the results are?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Chad said:
John, What I wanted to do was find the total percent for an employee for
the
quarter using this formula to find the percent but I cant figure out how
to
just get it to show for the current quarter. Like a quarterly total but
just
for the current qurent quarter. Here is what im using to find the
employees
percent: Thanks!

Delay Percent: Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT Reason 1]-[DT
MAINTENANCE]-[DT Reason 2]))



--
Newbies need extra loven.........


John Spencer said:
Does this mean you have solved your problem?

First Day of Current Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1,1)

Last Day of Current Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4,0)

First Day of Prior Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1-3,1)

Last Day of Prior Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4-1,0)

If you want data for just the current quarter of the current year then
you
might use the following SQL

SELECT tblMain.[EMPLOYEE NAME], Sum([DT REGULAR])/(Sum([EMPLOYEE
TIME]-[DT
Reason 1]-[DT MAINTENANCE]-[DT Reason 2])) AS [Delay Percent]
FROM tblEmployees INNER JOIN tblMain ON tblEmployees.EmployeeName =
tblMain.[EMPLOYEE NAME]
WHERE tblMain.[DAYS DATE] Between
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1,1)
and DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4,0)
AND tblEmployees.Status=False
GROUP BY tblMain.ID, tblMain.[DAYS DATE]
, tblMain.[EMPLOYEE NAME]
, DatePart("q",[DAYS DATE]), tblEmployees.Status



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I figured out how to get what dates were in what quarter by my quarter
expression. I want an Daily percent for an employee in the current
quarter.
Hope this helps...

SELECT tblMain.[EMPLOYEE NAME], Sum([DT REGULAR])/(Sum([EMPLOYEE
TIME]-[DT
Reason 1]-[DT MAINTENANCE]-[DT Reason 2])) AS [Delay Percent]
FROM tblEmployees INNER JOIN tblMain ON tblEmployees.EmployeeName =
tblMain.[EMPLOYEE NAME]
GROUP BY tblMain.ID, tblMain.[DAYS DATE], tblMain.[EMPLOYEE NAME],
DatePart("q",[DAYS DATE]), tblEmployees.Status
HAVING (((tblEmployees.Status)=False));

--
Newbies need extra loven.........


:

Hello, I want to run a query that would give me a total for the
current
quarter for the year instead of puting in a date range. I have a
formula
[expression] in my query that I want this for. Please help!

Here is my expression:
Delay Percent: Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT Reason
1]-[DT
MAINTENANCE]-[DT Reason 2]))
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

You probably need to remove tblMain.ID from the Group By clause. Even if it
is not showing it is going to be used to define the group and it wouldn't
hurt to remove Status from the group by either and to roll all your criteria
into the where clause. If you do all that the query will end up looking
like

SELECT Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT Reason 1]-[DT
MAINTENANCE]-[DT Reason 2])) AS [Delay Percent]
, tblMain.[EMPLOYEE NAME]
FROM tblEmployees INNER JOIN tblMain
ON tblEmployees.EmployeeName = tblMain.[EMPLOYEE NAME]
WHERE tblMain.[DAYS DATE] Between
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1,1) And
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4,0)
AND tblEmployees.Status = False
AND TblMain.[Employee Name]=[Forms]![frmSidebar]![txtEmployeeName]
GROUP BY tblMain.[EMPLOYEE NAME]


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Chad said:
John, I got the query to work but it kept saying Im missing a macro when I
tried to go back to design view. anyway I copyed the formula and
implimented
it into my query and it works (BUT) instead of giving a percent for an
individual employee it gives every percent for every day in the current
quarter for that one employee. How do I get it to give a total percent for
that employee insted of one for every day in a quarter? Here is the SQL:
Thanks!

SELECT Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT Reason 1]-[DT
MAINTENANCE]-[DT Reason 2])) AS [Delay Percent], tblMain.[EMPLOYEE NAME]
FROM tblEmployees INNER JOIN tblMain ON tblEmployees.EmployeeName =
tblMain.[EMPLOYEE NAME]
WHERE (((tblMain.[DAYS DATE]) Between
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1,1) And
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4,0)))
GROUP BY tblMain.[EMPLOYEE NAME], tblEmployees.Status, tblMain.ID
HAVING (((tblMain.[EMPLOYEE NAME])=[Forms]![frmSidebar].[txtEmployeeName])
AND ((tblEmployees.Status)=False));

--
Newbies need extra loven.........


John Spencer said:
DID YOU TRY the query I posted. It should calculate the sum for the
current
quarter only?

If you tried the query and it failed to give you the desired results, can
you tell us what the problem with the results are?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Chad said:
John, What I wanted to do was find the total percent for an employee
for
the
quarter using this formula to find the percent but I cant figure out
how
to
just get it to show for the current quarter. Like a quarterly total but
just
for the current qurent quarter. Here is what im using to find the
employees
percent: Thanks!

Delay Percent: Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT Reason 1]-[DT
MAINTENANCE]-[DT Reason 2]))



--
Newbies need extra loven.........


:

Does this mean you have solved your problem?

First Day of Current Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1,1)

Last Day of Current Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4,0)

First Day of Prior Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1-3,1)

Last Day of Prior Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4-1,0)

If you want data for just the current quarter of the current year then
you
might use the following SQL

SELECT tblMain.[EMPLOYEE NAME], Sum([DT REGULAR])/(Sum([EMPLOYEE
TIME]-[DT
Reason 1]-[DT MAINTENANCE]-[DT Reason 2])) AS [Delay Percent]
FROM tblEmployees INNER JOIN tblMain ON tblEmployees.EmployeeName =
tblMain.[EMPLOYEE NAME]
WHERE tblMain.[DAYS DATE] Between
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1,1)
and DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4,0)
AND tblEmployees.Status=False
GROUP BY tblMain.ID, tblMain.[DAYS DATE]
, tblMain.[EMPLOYEE NAME]
, DatePart("q",[DAYS DATE]), tblEmployees.Status



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I figured out how to get what dates were in what quarter by my
quarter
expression. I want an Daily percent for an employee in the current
quarter.
Hope this helps...

SELECT tblMain.[EMPLOYEE NAME], Sum([DT REGULAR])/(Sum([EMPLOYEE
TIME]-[DT
Reason 1]-[DT MAINTENANCE]-[DT Reason 2])) AS [Delay Percent]
FROM tblEmployees INNER JOIN tblMain ON tblEmployees.EmployeeName =
tblMain.[EMPLOYEE NAME]
GROUP BY tblMain.ID, tblMain.[DAYS DATE], tblMain.[EMPLOYEE NAME],
DatePart("q",[DAYS DATE]), tblEmployees.Status
HAVING (((tblEmployees.Status)=False));

--
Newbies need extra loven.........


:

Hello, I want to run a query that would give me a total for the
current
quarter for the year instead of puting in a date range. I have a
formula
[expression] in my query that I want this for. Please help!

Here is my expression:
Delay Percent: Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT Reason
1]-[DT
MAINTENANCE]-[DT Reason 2]))
Click to expand...
Click to expand...
Click to expand...
 
C

Chad

Yes your SQL wors great! 2 questions though. 1) Why is it when I enter the
SQL into a query and try to look at it in design view it gives me an error
(cant run macro or callback function 'fDesign'. 2) Is all you did is take out
the ID field? Thanks!
--
Newbies need extra loven.........


John Spencer said:
You probably need to remove tblMain.ID from the Group By clause. Even if it
is not showing it is going to be used to define the group and it wouldn't
hurt to remove Status from the group by either and to roll all your criteria
into the where clause. If you do all that the query will end up looking
like

SELECT Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT Reason 1]-[DT
MAINTENANCE]-[DT Reason 2])) AS [Delay Percent]
, tblMain.[EMPLOYEE NAME]
FROM tblEmployees INNER JOIN tblMain
ON tblEmployees.EmployeeName = tblMain.[EMPLOYEE NAME]
WHERE tblMain.[DAYS DATE] Between
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1,1) And
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4,0)
AND tblEmployees.Status = False
AND TblMain.[Employee Name]=[Forms]![frmSidebar]![txtEmployeeName]
GROUP BY tblMain.[EMPLOYEE NAME]


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Chad said:
John, I got the query to work but it kept saying Im missing a macro when I
tried to go back to design view. anyway I copyed the formula and
implimented
it into my query and it works (BUT) instead of giving a percent for an
individual employee it gives every percent for every day in the current
quarter for that one employee. How do I get it to give a total percent for
that employee insted of one for every day in a quarter? Here is the SQL:
Thanks!

SELECT Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT Reason 1]-[DT
MAINTENANCE]-[DT Reason 2])) AS [Delay Percent], tblMain.[EMPLOYEE NAME]
FROM tblEmployees INNER JOIN tblMain ON tblEmployees.EmployeeName =
tblMain.[EMPLOYEE NAME]
WHERE (((tblMain.[DAYS DATE]) Between
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1,1) And
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4,0)))
GROUP BY tblMain.[EMPLOYEE NAME], tblEmployees.Status, tblMain.ID
HAVING (((tblMain.[EMPLOYEE NAME])=[Forms]![frmSidebar].[txtEmployeeName])
AND ((tblEmployees.Status)=False));

--
Newbies need extra loven.........


John Spencer said:
DID YOU TRY the query I posted. It should calculate the sum for the
current
quarter only?

If you tried the query and it failed to give you the desired results, can
you tell us what the problem with the results are?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John, What I wanted to do was find the total percent for an employee
for
the
quarter using this formula to find the percent but I cant figure out
how
to
just get it to show for the current quarter. Like a quarterly total but
just
for the current qurent quarter. Here is what im using to find the
employees
percent: Thanks!

Delay Percent: Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT Reason 1]-[DT
MAINTENANCE]-[DT Reason 2]))



--
Newbies need extra loven.........


:

Does this mean you have solved your problem?

First Day of Current Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1,1)

Last Day of Current Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4,0)

First Day of Prior Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1-3,1)

Last Day of Prior Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4-1,0)

If you want data for just the current quarter of the current year then
you
might use the following SQL

SELECT tblMain.[EMPLOYEE NAME], Sum([DT REGULAR])/(Sum([EMPLOYEE
TIME]-[DT
Reason 1]-[DT MAINTENANCE]-[DT Reason 2])) AS [Delay Percent]
FROM tblEmployees INNER JOIN tblMain ON tblEmployees.EmployeeName =
tblMain.[EMPLOYEE NAME]
WHERE tblMain.[DAYS DATE] Between
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1,1)
and DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4,0)
AND tblEmployees.Status=False
GROUP BY tblMain.ID, tblMain.[DAYS DATE]
, tblMain.[EMPLOYEE NAME]
, DatePart("q",[DAYS DATE]), tblEmployees.Status



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I figured out how to get what dates were in what quarter by my
quarter
expression. I want an Daily percent for an employee in the current
quarter.
Hope this helps...

SELECT tblMain.[EMPLOYEE NAME], Sum([DT REGULAR])/(Sum([EMPLOYEE
TIME]-[DT
Reason 1]-[DT MAINTENANCE]-[DT Reason 2])) AS [Delay Percent]
FROM tblEmployees INNER JOIN tblMain ON tblEmployees.EmployeeName =
tblMain.[EMPLOYEE NAME]
GROUP BY tblMain.ID, tblMain.[DAYS DATE], tblMain.[EMPLOYEE NAME],
DatePart("q",[DAYS DATE]), tblEmployees.Status
HAVING (((tblEmployees.Status)=False));

--
Newbies need extra loven.........


:

Hello, I want to run a query that would give me a total for the
current
quarter for the year instead of puting in a date range. I have a
formula
[expression] in my query that I want this for. Please help!

Here is my expression:
Delay Percent: Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT Reason
1]-[DT
MAINTENANCE]-[DT Reason 2]))
Click to expand...
Click to expand...
Click to expand...
 
C

Chad

John, I was looking at the query and I forgot, I need the ID to be in the
query because im using it for a subform and I have childs that use the ID to
link them together. Is this possible to use the ID filed and have it show 1
result? Thanks!
 
J

John Spencer

1) I have no idea why the query would cause the message about fDesign to
appear. Nothing in the query should do this.

2) If you have the ID field in there then the query is going to group on the
Id field. And I made a guess that this meant you were going to get multiple
rows based on the inclusion of the ID field, so I proposed removing it. The
other changes I made were to increase the efficiency of the query by moving
the criteria from the HAVING clause to the WHERE clause. You should use the
HAVING clause to apply criteria to columns that you have aggregated (sum,
max, min, etc) and the WHERE clause to columns that you are grouping by.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Chad said:
Yes your SQL wors great! 2 questions though. 1) Why is it when I enter the
SQL into a query and try to look at it in design view it gives me an error
(cant run macro or callback function 'fDesign'. 2) Is all you did is take
out
the ID field? Thanks!
--
Newbies need extra loven.........


John Spencer said:
You probably need to remove tblMain.ID from the Group By clause. Even if
it
is not showing it is going to be used to define the group and it wouldn't
hurt to remove Status from the group by either and to roll all your
criteria
into the where clause. If you do all that the query will end up looking
like

SELECT Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT Reason 1]-[DT
MAINTENANCE]-[DT Reason 2])) AS [Delay Percent]
, tblMain.[EMPLOYEE NAME]
FROM tblEmployees INNER JOIN tblMain
ON tblEmployees.EmployeeName = tblMain.[EMPLOYEE NAME]
WHERE tblMain.[DAYS DATE] Between
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1,1) And
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4,0)
AND tblEmployees.Status = False
AND TblMain.[Employee Name]=[Forms]![frmSidebar]![txtEmployeeName]
GROUP BY tblMain.[EMPLOYEE NAME]


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Chad said:
John, I got the query to work but it kept saying Im missing a macro
when I
tried to go back to design view. anyway I copyed the formula and
implimented
it into my query and it works (BUT) instead of giving a percent for an
individual employee it gives every percent for every day in the current
quarter for that one employee. How do I get it to give a total percent
for
that employee insted of one for every day in a quarter? Here is the
SQL:
Thanks!

SELECT Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT Reason 1]-[DT
MAINTENANCE]-[DT Reason 2])) AS [Delay Percent], tblMain.[EMPLOYEE
NAME]
FROM tblEmployees INNER JOIN tblMain ON tblEmployees.EmployeeName =
tblMain.[EMPLOYEE NAME]
WHERE (((tblMain.[DAYS DATE]) Between
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1,1) And
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4,0)))
GROUP BY tblMain.[EMPLOYEE NAME], tblEmployees.Status, tblMain.ID
HAVING (((tblMain.[EMPLOYEE
NAME])=[Forms]![frmSidebar].[txtEmployeeName])
AND ((tblEmployees.Status)=False));

--
Newbies need extra loven.........


:

DID YOU TRY the query I posted. It should calculate the sum for the
current
quarter only?

If you tried the query and it failed to give you the desired results,
can
you tell us what the problem with the results are?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John, What I wanted to do was find the total percent for an employee
for
the
quarter using this formula to find the percent but I cant figure out
how
to
just get it to show for the current quarter. Like a quarterly total
but
just
for the current qurent quarter. Here is what im using to find the
employees
percent: Thanks!

Delay Percent: Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT Reason
1]-[DT
MAINTENANCE]-[DT Reason 2]))



--
Newbies need extra loven.........


:

Does this mean you have solved your problem?

First Day of Current Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1,1)

Last Day of Current Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4,0)

First Day of Prior Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1-3,1)

Last Day of Prior Quarter
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4-1,0)

If you want data for just the current quarter of the current year
then
you
might use the following SQL

SELECT tblMain.[EMPLOYEE NAME], Sum([DT REGULAR])/(Sum([EMPLOYEE
TIME]-[DT
Reason 1]-[DT MAINTENANCE]-[DT Reason 2])) AS [Delay Percent]
FROM tblEmployees INNER JOIN tblMain ON tblEmployees.EmployeeName =
tblMain.[EMPLOYEE NAME]
WHERE tblMain.[DAYS DATE] Between
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1,1)
and DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4,0)
AND tblEmployees.Status=False
GROUP BY tblMain.ID, tblMain.[DAYS DATE]
, tblMain.[EMPLOYEE NAME]
, DatePart("q",[DAYS DATE]), tblEmployees.Status



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I figured out how to get what dates were in what quarter by my
quarter
expression. I want an Daily percent for an employee in the
current
quarter.
Hope this helps...

SELECT tblMain.[EMPLOYEE NAME], Sum([DT REGULAR])/(Sum([EMPLOYEE
TIME]-[DT
Reason 1]-[DT MAINTENANCE]-[DT Reason 2])) AS [Delay Percent]
FROM tblEmployees INNER JOIN tblMain ON tblEmployees.EmployeeName
=
tblMain.[EMPLOYEE NAME]
GROUP BY tblMain.ID, tblMain.[DAYS DATE], tblMain.[EMPLOYEE
NAME],
DatePart("q",[DAYS DATE]), tblEmployees.Status
HAVING (((tblEmployees.Status)=False));

--
Newbies need extra loven.........


:

Hello, I want to run a query that would give me a total for the
current
quarter for the year instead of puting in a date range. I have a
formula
[expression] in my query that I want this for. Please help!

Here is my expression:
Delay Percent: Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT Reason
1]-[DT
MAINTENANCE]-[DT Reason 2]))
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Perhaps, but now I am getting into guessing what the field Main.ID
represents - which I did when I proposed the change. In other words, I need
to know a lot more about your table structure, etc.

You can try adding the Main.Id back into the query and using an aggregate
function to force only one value

SELECT Max(Main.ID) as ID
, Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT Reason 1]-
[DT MAINTENANCE]-[DT Reason 2])) AS [Delay Percent]
, tblMain.[EMPLOYEE NAME]
FROM tblEmployees INNER JOIN tblMain
ON tblEmployees.EmployeeName = tblMain.[EMPLOYEE NAME]
WHERE tblMain.[DAYS DATE] Between
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1,1) And
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4,0)
AND tblEmployees.Status = False
AND TblMain.[Employee Name]=[Forms]![frmSidebar]![txtEmployeeName]
GROUP BY tblMain.[EMPLOYEE NAME]

OR try this

SELECT Main.ID
, Sum([DT REGULAR])/(Sum([EMPLOYEE TIME]-[DT Reason 1]-
[DT MAINTENANCE]-[DT Reason 2])) AS [Delay Percent]
, tblMain.[EMPLOYEE NAME]
FROM tblEmployees INNER JOIN tblMain
ON tblEmployees.EmployeeName = tblMain.[EMPLOYEE NAME]
WHERE tblMain.[DAYS DATE] Between
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1,1) And
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4,0)
AND tblEmployees.Status = False
AND TblMain.[Employee Name]=[Forms]![frmSidebar]![txtEmployeeName]
GROUP BY tblMain.[EMPLOYEE NAME], Main.ID

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
C

Chad

John, I tried both SQL's and both asked for the ID#. Its not a necessity that
I get it to work with the ID in my query I can just use the query in a report
instead of what I was trying to do. Im just glade you helped me with getting
the formula to find the quarter! Seems like evertime I have a question your
there to bail me out! Thanks for hellping all us newbies!!!!!
 

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

Query and text box help on formula 6
Query error help 6
Percent help 3
Update query help 3
Need help with Report formula 6
Formula in text box on RPT help... 9
Select Query 1
Select Record Query 1

Top