@gkick,
i have not downloaded the above attachment.
this response is based around the attachment you uploaded in a previous obviously related topic:
viewtopic.php?f=61&t=101391
i did not reply to that thread because it was marked [Solved] before i got to download your database, however i did produce a solution of sorts & that solution forms the basis of the attachment below which uses embedded hsqldb 1.8.0.10.
just want to say that what you were aiming for there was achievable but not realistic & i settled for a more practical outcome.
it's often difficult to configure a users db in order to achieve a result & very often quicker & easier to build a db from scratch.
i placed most of the fields from your table "tblschedule" into a new table "tPayRateLog".
i do not use the table "tblschedule".
i only access the table "tblPax" to extract the field "signin".
i do use the table "tbllog" which contains the sign in/out data. i do not use & cannot see the purpose of the field "tbllog"."ldate" which contains errors.
"tPayRateLog"."OTRate" (overtime rate) is a decimal value so overtime pay = "tPayRateLog"."StdRate" * "tPayRateLog"."OTRate". this is in preference to the literal values which you used.
what we have:
four list box queries.
a query called 'qFilteredRemunerationWithPayRate'. the calculations within this query are based on your original code.
a view created from this query is the data source for the report.
the logged hours (difference between signin & signout) are rounded down to the nearest fifteen minutes for purpose of clarity.
two forms:
one to select values & open the report (uses macros). this form is related to your previous topic as mentioned above.
the other 'fPayRate' addresses this topic & relates to pay rates.
the top pair of forms are used to create/update pay rates.
the bottom pair are used to assign/update an employees pay rate.
overall i think that it's fairly easy to use & importantly preserves history.
this is not a fully functioning db, the intention here is to demonstrate a method which hopefully may help in securing a solution.
there are many issues which need to be addressed e.g. the actual start/finish times & that pay rates may vary by day or hour.
this is a 'Base' forum & 'Base' does not play well with some current back end database functions (arrays for example) & is therefore restrictive.
i tested by using the query 'qFilteredRemunerationWithPayRate':
1) open query for edit.
2) copy the query & close the window.
3) paste into a query design window.
4) delete everything above the first 'from' clause.
5) insert 'select *' as the first row.
6) delete the second row from bottom (group by clause).
7) bottom row should = 'order by p."signin", t."signin"'.
8) activate the 'SQL' icon. save as 'Query1'.
now we have a query which shows each & every row.
remember that the data is filtered according to the values selected in the form 'fOpen Report' so open the form, select a signin (try Mark who had a change of rate), close the form.
execute the query & we can see the signin, logged hrs, pay rate etc.
i went further & registered the db then imported 'Query1' into 'Calc' & used the 'SUM' function on various groups of cells.
EXAMPLES OF CHANGE IN PAY RATE:
using the form 'fPayRate'.
Mark is on pay rate B. all employee on pay rate B will have received a salary increase of 5% on 1 Jan 2020.
hit pay rate B & employee Mark & note that the first row values are applied between StartDate & EndDate while the current pay rate (second row) is applied from StartDate & EndDate is null.
Max was on pay rate A until 31 Dec 2019 when he was switched to pay rate E. hit pay rate A, E & employee Max to see how it should be done.
Edit: 29 Mar 2020. found error in code used "PayRateLogID" when i should have used "PayRateID"
replaced guilty attachment with ammended version. |
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10