By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,934 Members | 1,527 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,934 IT Pros & Developers. It's quick & easy.

Help with and Regular vs Overtime Query

P: n/a
I am having trouble bellow wit this query. I have the total regular hours and the overtime. The problem is that if it is 8 hours and under, I need it to show as regular hours. Any thoughts?

SELECT [EPT Regular Time Total Daily Query].[Employee Name], [EPT Regular Time Total Daily Query].Date, [EPT Regular Time Total Daily Query].[Sum Of Total Regular Time], IIf([Sum Of Total Regular Time]<=8,[TotalHours],8) AS [Regular Hours], Sum(([Sum Of Total Regular Time]-[Regular Hours])) AS Ot
FROM [EPT Regular Time Total Daily Query]
GROUP BY [EPT Regular Time Total Daily Query].[Employee Name], [EPT Regular Time Total Daily Query].Date, [EPT Regular Time Total Daily Query].[Sum Of Total Regular Time], IIf([Sum Of Total Regular Time]<=8,[TotalHours],8);

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Why not break it into two columns. Calculation in one, text in another.
You can always combine the two values at some later point. (On a form,
or another query).

HTH.

Dave

Nov 13 '05 #2

P: n/a
Still kind of new to this whole access deal. Can you give me some assitance with the code.

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #3

P: n/a
Jamie,
Calculate the total hours first. Then use IIf() to calculate regular,
overtime and double time amounts. It's a lot easier if you know to total
hours first. Also, the group by is probably unnecessary.

"Jamie Pittman via AccessMonster.com" <fo***@AccessMonster.com> wrote in
message news:41******************************@AccessMonste r.com...
I am having trouble bellow wit this query. I have the total regular hours
and the overtime. The problem is that if it is 8 hours and under, I need
it to show as regular hours. Any thoughts?

SELECT [EPT Regular Time Total Daily Query].[Employee Name], [EPT Regular
Time Total Daily Query].Date, [EPT Regular Time Total Daily Query].[Sum Of
Total Regular Time], IIf([Sum Of Total Regular Time]<=8,[TotalHours],8) AS
[Regular Hours], Sum(([Sum Of Total Regular Time]-[Regular Hours])) AS Ot
FROM [EPT Regular Time Total Daily Query]
GROUP BY [EPT Regular Time Total Daily Query].[Employee Name], [EPT
Regular Time Total Daily Query].Date, [EPT Regular Time Total Daily
Query].[Sum Of Total Regular Time], IIf([Sum Of Total Regular
Time]<=8,[TotalHours],8);

--
Message posted via http://www.accessmonster.com

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.