3 year cycle in query (1 Viewer)

CJ_London

Super Moderator
Staff member
Local time
Today, 16:50
Joined
Feb 19, 2013
Messages
16,553
@namliam - why to you reckon that?
 

namliam

The Mailman - AWF VIP
Local time
Today, 17:50
Joined
Aug 11, 2003
Messages
11,696
because inline sql is executed for EVERY line that comes thru the sql, not just one time like it would if you stick the select into the from where it belongs.

On 10.000 records running the inline select 10.000 times instead of 1 time is an obvious drawback
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:50
Joined
Feb 19, 2013
Messages
16,553
I beg to differ - the subquery is filtered so only the relevant records are selected.

I'm about to go on holiday so don't have time to debate:) but happy to continue when I get back
 

Chintsapete

Registered User.
Local time
Today, 18:50
Joined
Jun 15, 2012
Messages
137
Hi CJ

I think this is going to work, I have to fiddle a bit with it to get it onto my payslips etc, but this is great. I do appreciate your time. Thanks a lot for that one it got me on the right track to finish. Just need a break now my brain is cooked. Shot
Pete
 

Chintsapete

Registered User.
Local time
Today, 18:50
Joined
Jun 15, 2012
Messages
137
Thanks a lot Namliam. I do appreciate your time, you and CJ got me on the right track with that one. A bit of a fiddle and I should get there. I'll post the code which worked in a bit, just need a break now, my brain is fried. Thanks and stay well.
Pete
 

namliam

The Mailman - AWF VIP
Local time
Today, 17:50
Joined
Aug 11, 2003
Messages
11,696
I beg to differ - the subquery is filtered so only the relevant records are selected.
Sub query is something different from an inline select

Sub selects I dont have much issues with in a some cases even un-avoidable....
Select...
From table
join (select ... from ...) as DummyName on ...

or
Select
From table
where keyvalue in (select keyvalue from...)
and some other samples, these only execute one time and are fine to use. Can be slightly better or slightly worse in performance but on a global plain wont make much of an impact.

Select (select from x)
From sometable
Here the select from x gets executed for every line the SQL returns, this can have major impact on performance
Offcourse if you have 1000 records in your tables, no one is going to notice.
Once you get to 1.000.000 you start having queries that run hours instead of minutes. ok may be over stating that a little, however a user waiting 5 minutes instead of 5 seconds can make a huge impact on the user's experience of your DB/report/product
 

Chintsapete

Registered User.
Local time
Today, 18:50
Joined
Jun 15, 2012
Messages
137
Ok guys, here's the problem. JC and Namliam your formulas essentially work both of them, but they're not accurate enough. I run them any which way I could think of (and my friend) and it seems to come down to that Access doesn't calculate the dates accurate enough. As soon the cycle# formula result is above .75 access seems to round up to the next full year and therefore start the next cycle up to 3 months early. Therefore the employees falling into that time bracket won't show up in the query result.
I'm completely at my wits end and might have to revert back to my old way and live with it.
Thanks for all your help (it extended my sleepless nights for a fair few days :).
Pete
 

namliam

The Mailman - AWF VIP
Local time
Today, 17:50
Joined
Aug 11, 2003
Messages
11,696
Not accurate enough? How do you mean?
Can you post some sample data that isnt working for you, using the INT function access WILL cut of any decimals as a result anything between 0.00001 and 0.999999 will be zero and add 1 to that to give period 1.

Should be relatively easy for you to fake up some data into a small database, I am very confident I can make it work if you can provide the "breaking" information.... or explain to you what is going wrong with the query vs your expectations.
 

Chintsapete

Registered User.
Local time
Today, 18:50
Joined
Jun 15, 2012
Messages
137
You beauty, Int was the missing link I forgot to put in with all the variations I tried. It works:D, wow thank you.

I put in the code below which works for someone else.
To summarize the whole setup: I got 2 tables 1= "EmployeeDetails" containing data like Emp#, Name, date of engagement etc 2= Salaries YTD with all the history like days worked, days leave, days sick etc in it. The sick leave cycle has to roll over every 3 years. I ended up doing 3 queries, because the in line strings became a bit much to get right.
Query 1 "SickLeavePrep1":
Code:
SELECT EmployeesDetailQ.[Emp#], EmployeesDetailQ.NickName, Sum([Salaries YTD].DaysSick) AS SumOfDaysSick, EmployeesDetailQ.YearsEmploy, (DateDiff("m",[DateofEngagment],Date())) AS MonthsEmpl, (Int(DateDiff('m',[dateofengagment],Date())\36)) AS [Cycle#], EmployeesDetailQ.DateOfEngagment, DateAdd("m",(Int(DateDiff("m",[DateofEngagment],Date())/36)*36),[DateOfEngagment]) AS SCStart, [Salaries YTD].Date
FROM EmployeesDetailQ INNER JOIN [Salaries YTD] ON EmployeesDetailQ.[Emp#] = [Salaries YTD].[Emp#]
GROUP BY EmployeesDetailQ.[Emp#], EmployeesDetailQ.NickName, EmployeesDetailQ.YearsEmploy, (DateDiff("m",[DateofEngagment],Date())), (Int(DateDiff('m',[dateofengagment],Date())\36)), EmployeesDetailQ.DateOfEngagment, DateAdd("m",(Int(DateDiff("m",[DateofEngagment],Date())/36)*36),[DateOfEngagment]), [Salaries YTD].Date, EmployeesDetailQ.LeftCompany
HAVING (((EmployeesDetailQ.LeftCompany)=No));

Query 2 "SickleavePrep2": (Mainly for the end date)
Code:
SELECT SickLeavePrep1.[Emp#], SickLeavePrep1.NickName, SickLeavePrep1.SumOfDaysSick, SickLeavePrep1.YearsEmploy, SickLeavePrep1.MonthsEmpl, SickLeavePrep1.[Cycle#], SickLeavePrep1.DateOfEngagment, SickLeavePrep1.SCStart, DateAdd("m",36,[ScStart])-1 AS ScEnd, SickLeavePrep1.Date
FROM SickLeavePrep1;

Query 3 "SickLeave":
Code:
SELECT SickLeavePrep2.[Emp#], SickLeavePrep2.NickName, SickLeavePrep2.DateOfEngagment, SickLeavePrep2.[Cycle#], SickLeavePrep2.SCStart, SickLeavePrep2.SCEnd, 30-Nz((Sum([SumOfDaysSick]))) AS SickdaysLeft, (Sum([SumOfDaysSick])) AS SickDaysUsed
FROM SickLeavePrep2
WHERE (((SickLeavePrep2.Date) Between [ScStart] And [ScEnd]))
GROUP BY SickLeavePrep2.[Emp#], SickLeavePrep2.NickName, SickLeavePrep2.DateOfEngagment, SickLeavePrep2.[Cycle#], SickLeavePrep2.SCStart, SickLeavePrep2.SCEnd;

One could probably cut it down to 2 queries but as I said the strings become very difficult so this made my life easier and it works.

Super many thanks to Namliam and CJ to get me there, wouldn't have been able to else.
 

namliam

The Mailman - AWF VIP
Local time
Today, 17:50
Joined
Aug 11, 2003
Messages
11,696
If you are going to "cut it up" I would have put both SCStart and SCEnd into the second query basing the on the Cycle#....

However, I am glad you got it to work afterall :), however you will with this contruct end up with -10 "SickdaysLeft" which to me is odd.
SickdaysLeft can (to me) never be below 0... then once it reaches 0 stay 0 and another column "SickdaysTooMany" (or something) starts counting to say 10 in this example. Is only a simple IIF which I did earlier I think... however if you are happy we are happy :)
 

Chintsapete

Registered User.
Local time
Today, 18:50
Joined
Jun 15, 2012
Messages
137
You can't imagine how happy I am right now :D, I had to many sleepless nights over it. I didn't consider the -10 scenario but on the other hand I don't mind too much either. If our dogs bite the security guy and we have to pay him the extra days than it shows in the same column and for the rest I make a report or warning on the payroll if they used them up, I haven't decided yet which way. I first have recover from this success :D.
Thanks again for your help, amazing.
 

namliam

The Mailman - AWF VIP
Local time
Today, 17:50
Joined
Aug 11, 2003
Messages
11,696
Enjoy your success while it lasts, the next problem is just around the corner....

*ow wait*
 

Users who are viewing this thread

Top Bottom