• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

adding criteria to an Access SQL

I have an employee work hours database that I use for payroll input.  I want to create a query that will choose which employee is eligible for overtime pay based on the criteria that they must have worked 15 of the previous 30 days prior to the holiday.  I have created a query that selects the employees who work on that day which selects everyone, but when i try to include further criteria I run into trouble.  I have include both the original which selects everyone and the new one which does not work.  Any help would be appreciated

SELECT paydata.empname, paydata.empnumber, paydata.hours AS SumofHours
FROM paydata
WHERE paydata.workdate = [statday]
ORDER BY paydata.empname;
SELECT paydata.empname, paydata.empnumber, paydata.hours AS SumofHours
FROM paydata
WHERE paydata.workdate = [statday] AND WHERE sum paydata.workdate BETWEEN (statday and statday - 30) >14
ORDER BY paydata.empname;

Open in new window

0
Gerw
Asked:
Gerw
  • 4
  • 3
2 Solutions
 
8080_DiverCommented:
For one thing, instead of SUM, you need to COUNT the workdate.  However, a bigger problem is that you are trying to do things in the WHERE clause that you need to do in the SELECT clause s well as your trying to provide 2 WHERE clasues for one SELECt statement. ;-).

See if this works for you:
SELECT Y.empname
      ,Y.empnumber
      ,Y.hours AS SumofHours
      ,Z.DaysWorked
FROM
(
SELECT paydata.empname
      ,paydata.empnumber
      ,paydata.hours AS SumofHours
FROM paydata
WHERE paydata.workdate = [statday]
) Y
INNER JOIN
(
SELECT  paydata.empnumber
       ,COUNT(paydata.empnumber) DaysWorked
FROM   paydata
WHERE  paydata.workdate BETWEEN(statday AND statday - 30)
GROUP BY paydata.empnumber
HAVING COUNT(paydata.empnumber) > 14
) Z
ON Y.empnumber = Z.empnumber

Open in new window



0
 
GerwAuthor Commented:
I am unfamiliar with the structure of the first SELECT statement.  Are you assigning the empName value to Y and empNumber value to Y etc?  

When I try to run the query as put together above, I get the error "Could not find the object 'COUNT' (paydata.empnumber) DaysWorked.  Check spelling etc.
0
 
GerwAuthor Commented:
I was able to correct the COUNT object error by adding the "as" before DaysWorked and another error by adding brackets around statday and statday-30, but now I am getting a 'too complex to be evaluated" error.  As the query runs it first asks for the stat holiday input, then asks for a value for Y.  This does not seem correct.  Here is the code with the changes I made to eliminate the object error and the BETWEEN - AND error.



COUNT(paydata.empnumber) as DaysWorked
FROM   paydata
WHERE  paydata.workdate BETWEEN(statday) AND (statday - 30)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
8080_DiverCommented:
Okay, I confess, I wrote SQL Server SQl.  

The Y and Z are aliases for the table names (which saves a heck of a lot of typing).  In Access, you may have to set the COUNT line to something like DaysWorked: COUNT(paydata.empnumber)

Also, I inadvertantly left in your erroneous BETWEEN constraint.  That line should read:
WHERE  paydata.workdate BETWEEN statday AND (statday - 30)

Open in new window


In fact, it sounds like it would probably be easier for you to create a separate query (let's call it qry_Days_Worked_In_Last_30) That consists of the following:
SELECT  paydata.empnumber
       ,DaysWorked: COUNT(paydata.empnumber) 
FROM   paydata
WHERE  paydata.workdate BETWEEN statday AND (statday - 30)
GROUP BY paydata.empnumber

Open in new window


Then add the >14 in the Constraint line below the DaysWorked column.

Once you have done that, start building a new query based upon the following:
SELECT paydata.empname
      ,paydata.empnumber
      ,paydata.hours AS SumofHours
FROM paydata
WHERE paydata.workdate = [statday]

Open in new window


Thenswitching to the Design view in the Query Builder, add the first query (qry_Days_Worked_In_Last_30 to the selection of "tables" and link that to the paydata table via the empnumber columns.  That should accomplish what I was trying to do in the SQL I gave you.
0
 
8080_DiverCommented:
Re: Response #35702128

You may need to add an AS before the Y and Z:
) AS Y

Open in new window


) AS Z

Open in new window

0
 
GerwAuthor Commented:
I will work on this solution.  I may have to delay for a few days as the business demands will take up most of my time over the next 4 or 5 days.  Thanks
0
 
GerwAuthor Commented:
This solution was helpful but difficult for me to follow
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now