Re: Query expression help

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" wrote:

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" <Chad@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2A91FCFC-54EA-460F-BAC9-39A5345A2D96@xxxxxxxxxxxxxxxx
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" wrote:

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" <Chad@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F7B6FC69-1DE7-4A0C-BD0C-BADBCF653ADC@xxxxxxxxxxxxxxxx
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" wrote:

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" <Chad@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:04B0E18D-D3C9-4E2C-8716-4E3554422BFE@xxxxxxxxxxxxxxxx
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" wrote:

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]))

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









.

Relevant Pages

  • Re: Corstabquery or normal query
    ... This is my SQL for Query: ... "John Spencer" wrote: ... 2007 Deacon Bob 1 ...
    (microsoft.public.access.reports)
  • Re: Using If expressions with an Or expression, and a Count questi
    ... "Amin" wrote: ... you guys (and a cool SQL website) so I am now coding exclusively in SQL. ... "John Spencer" wrote: ... IT is the ENTIRE query. ...
    (microsoft.public.access.queries)
  • Re: Trim Data
    ... The query does run and returns all other fields correctly except this one ... "John Spencer" wrote: ... please post the SQL of the query. ... "KARL DEWEY" wrote: ...
    (microsoft.public.access.queries)
  • Re: Query for Left(String,5) - Access 2007
    ... instead of the linked excel file only because I can't think of anything ... "John Spencer" wrote: ... the query is going to try to link every ... The Query design view actually creates SQL. ...
    (microsoft.public.access.queries)
  • Re: Link Tables
    ... "John Spencer MVP" wrote: ... As soon as I remove this last table link and the table the query ... You don't need to make multiple tables, just add the Employee ...
    (microsoft.public.access.queries)