Enter Parameter Value

[kwooten]

I added the wrong DB then posted incorrectly.. database is below

[kwooten]

I am doing a calculation for FTE and am getting a "Enter Parameter Value" error "SumofHOURS".. If I delete my calculation then I wont get the error.. Here is SQL statement.

SELECT HUMANIC_CPO_EMP_ALL.DIV_1_DESC, HUMANIC_CPO_EMP_ALL.DIV_2_DESC, Sum(HUMANIC_CPO_PAYROLL_MOTHER_2011.HOURS) AS SumOfHOURS, Sum(HUMANIC_CPO_PAYROLL_MOTHER_2011.EARNINGS) AS SumOfEARNINGS, ([SumOfHOURS]/(14*86.67)) AS FTE
FROM HUMANIC_CPO_PAYROLL_MOTHER_2011 LEFT JOIN HUMANIC_CPO_EMP_ALL ON HUMANIC_CPO_PAYROLL_MOTHER_2011.SOCSEC = HUMANIC_CPO_EMP_ALL.SSN
GROUP BY HUMANIC_CPO_EMP_ALL.DIV_1_DESC, HUMANIC_CPO_EMP_ALL.DIV_2_DESC, HUMANIC_CPO_PAYROLL_MOTHER_2011.PAY_TYPE, HUMANIC_CPO_EMP_ALL.FLSA, ([SumOfHOURS]/(14*86.67))
HAVING (((HUMANIC_CPO_EMP_ALL.DIV_1_DESC) Is Not Null) AND ((HUMANIC_CPO_PAYROLL_MOTHER_2011.PAY_TYPE) In ('OVERTIME')))
ORDER BY HUMANIC_CPO_EMP_ALL.DIV_1_DESC, HUMANIC_CPO_EMP_ALL.DIV_2_DESC;

[boblarson]

You can't use a field's alias like that in another calculation in the same query. You have two choices. Either to recreate the entire calculation for that field which you are using it right now or save this query without that second calculation and then use that query in a new one where you can then refer to that alias.

[kwooten]

oh ok. I can do that..

Also, I am getting duplicated "div2" titles when I should only have 1 title with totals.. In these duplicates I may have one with 100 hours and the other with 30 hours...

[boblarson]

Duplicates would be caused by more than one record for the given grouping.

[kwooten]

Then how do I get it to sum so that i only have one record? The divs are named the same.

[boblarson]

Looks like PayType is the possible culprit. Changing that from Group By to WHERE instead (in design view) might be the solution.

[boblarson]

Oh, and I would open the database but I don't have 2007 with me at the moment so I can't try it out. I'm only at 2003 currently at work. They used to have a test machine with 2007 on it I could use but they had to take that machine for a new employee sometime back.

[kwooten]

i saved the file as a 2003 version... i think.

I have pay_type as in ('overtime').. I didnt think that would cause multiple values?

[kwooten]

Looks like PayType is the possible culprit. Changing that from Group By to WHERE instead (in design view) might be the solution.

This didnt work..

Also I am trying to create a report with all this data.. Can I use more than 1 query to create a report?

[boblarson]

You can use more than one query if you

1. Pull them all together using a Union Query (not likely a good option)
2. Use subreports if different fields and different aggregations are required.
3. Use multiple queries linked in one other query which, if linked properly can help with the display of things.
4. Reports can set groupings and aggregations so perhaps just getting a query to get the data and then you can group and aggregate in the report is the way to go.

[kwooten]

ok, i gotta figure out how to get these divs to stop duping too and I cant figure out why they are doing it.

regarding reporting...

I have this "OT" query pulling data on the SUMofHOURS and SUMofEARNINGS. Then I have a "OT2" query that includes the FTE formula, then another query with total regular earnings with a formula to calculate overtime % of regular earnings (OT earnings/total regular earnings). The constant in all 3 of these is the SSN and all the data is coming from the same 2 tables.

[rpeare]

look at your FLSA column, you have it as GROUP BY in your query but are not showing the result on your query. It has no criteria in it either. If you delete that column it should sum correctly

[kwooten]

Thanks rpeare... that was it!