Calculate Total Cost For Multiple Types With Effective Date

[edsuk]

Hi

I have been asked to create a facility for Accounts to allocate a number of
costs against an employee, these multiple costs then need to be
converted into a total cost by effective date,
an example below may explain more clearly:


EmpID Type EffDate HourlyCost
1 Lab 01/01/06 10.00
1 Veh 01/01/06 00.50
1 Lab 01/01/07 12.50
2 Lab 01/01/06 13.00
2 Veh 06/06/06 01.00
2 Veh 09/09/06 01.50
2 Equip 05/05/06 02.00
2 Lab 09/09/06 14.50

I need to convert the above into:

Emp EffDate TotalHourlyCost
1 01/01/06 10.50
1 01/01/07 13.00
2 01/01/06 13.00
2 05/05/06 15.00
2 06/06/06 16.00
2 09/09/06 18.00

No tables have been created yet but as they want to be able to add any cost 'type' I can't see any benefit in having separate
tables for each cost type, so all I can think of at the moment is to enter the data into one table then
derive the total cost to insert into another table for the cost queries to run against.

i.e. any time a change is made delete the data in the 2nd table and append the new data

If anyone could give me a start on this it would be very much appreciated.

Thanks

 

[pbail1]

Hi edsuk

Can you explain how you get the figures in the second table as I cannot see how some of the figures match the base data above - I can be thick sometimes so bear with me!

If it is purely employee X did two tasks on Day Y and total was the cost for task 1 + the cost for task 2 then you can just do sum of the hourly cost and group the data by employee ID and the date. Is that what you are trying to do?

Example SQL:

SELECT test.employeeID, test.Date, Sum(test.cost) AS SumOfcost
FROM test
GROUP BY test.employeeID, test.Date;

 

[Golom]

I can't seem to derive the numbers in your result table from those in the source table. For example, For EMP 2, you have
[tt]
EmpID Type EffDate HourlyCost RunningSum
2 Lab 01/01/06 13.00 13.00
2 Equip 05/05/06 02.00 15.00
2 Veh 06/06/06 01.00 16.00
2 Veh 09/09/06 01.50
2 Lab 09/09/06 14.50 32.00
[/tt]
ordered by Effdate, and the result is
[tt]
Emp EffDate TotalHourlyCost
2 01/01/06 13.00
2 05/05/06 15.00
2 06/06/06 16.00
2 09/09/06 18.00
[/tt]
We agree for the first 3 values but how did you get $18.00 on 09/09/06? ... and what happened to the $14.50 for "Lab" on 09/09/06?

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[edsuk]

Sorry I didn't explain it fully did I !

These are cost factors not actuals so basically Labor is the Labor Rate at the relevant date, so for emp 2 on 09/09/06 his Labor Rate went from 13.00 to 14.50 (an increase of 1.50) plus the Veh changed from 1.00 to 1.50 (an increase of 0.50) so the effective costs at 09/09/06 are:

Lab 14.50 (increased - the old rate of 13.00 is effective prior 9/9/06)
Veh 1.50 (increased - 1.00 prior to 9/9/06)
Equip 2.00 (no change)

Total 18.00


so for a job performed on 9/8/06 the cost is 16.00 ph
a job peformed on 9/9/06 has increased to 18.00 ph

Basically I need to obtain a value for each of the different types that may be entered against an employee - I need to get the values into the format of the 2nd table so that I can cost the jobs effectively at relevant date.

To be honest it's driving me nuts! But because I cannot tie them down to just giving me a total rate update when it occurs I have been left to come up with a solution, I've had this for weeks and still have't found a logical approach, hence my calls for help

Mark

 

[edsuk]

I have been thinking about this since the my last post, and maybe below is a much better explanation.

Example:

Emp 1

EffDate Lab Veh Equip Total
01/01/06 10.00 00.50 00.00 10.50
01/01/07 12.50 00.50 00.00 13.00

Emp 2

EffDate Lab Veh Equip Total
01/01/06 13.00 00.00 00.00 13.00
05/05/06 13.00 00.00 02.00 15.00
06/06/06 13.00 01.00 02.00 16.00
09/09/06 14.50 01.50 02.00 18.00

The above would be easy to develop, however what I am really looking for is the added ability for an extra 'Type' to be added without the requirement for the user to ask for us to add one (this could oocur n times).

e.g.

Accounts want to add a New Type called 'Insurance':

Emp 1

EffDate Lab Veh Equip Ins Total
01/01/06 10.00 00.50 00.00 00.00 10.50
01/01/07 12.50 00.50 00.00 00.00 13.00
01/03/07 12.50 00.50 00.00 03.00 16.00

Emp 2

EffDate Lab Veh Equip Ins Total
01/01/06 13.00 00.00 00.00 00.00 13.00
05/05/06 13.00 00.00 02.00 00.00 15.00
06/06/06 13.00 01.00 02.00 00.00 16.00
09/09/06 14.50 01.50 02.00 00.00 18.00
01/03/07 14.50 01.50 02.00 03.00 21.00


If anyone can think of an approach as to how to do this it would be really appreciated, if not, I may have to go with Option B and wait for the requests for new 'Types' to come flooding in. !

As mentioned this request has been around for a while and keeps getting put to the back of the pile!

Thanks to all

Mark

 

[edsuk]

Can anyone give me feedback on this intial approach:

Create a Table with the following fields
ID, EffDate, Total, Lab, Veh, Equip

Create function to derive Total by counting number of fields in the table then use a loop to sum the values from col(3) to col and populate 'Total' with the sum

Create function for user to add new column to table. Store new column name in another table that contain Descriptions for use throughout the application.

Create unbound form for adding data using combo box to display all valid 'Types' with limit to list set to 'Yes'.

I suppose the only other issue would be displaying the data as the no. of columns grow. Generally I would have created a list box to display the records for the relevant employee. This may need a bit more thought.

Any views?

Mark

 

[edsuk]

Don't worry guys.

I found a solution, basically used nested loops.

Accounts can add as many 'Types' as they like and I can calculate the Total Cost for each effective date.

Private Sub CalcHourly()

On Error GoTo Cal_Err

Dim str1 As String
Dim str2 As String
Dim str3 As String
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim rst3 As Recordset
Dim rst4 As Recordset
Dim i As Single

str1 = "SELECT EffDate FROM T_EmpRates WHERE EmpID=" & Me.EmployeeID & " GROUP BY EffDate ORDER BY EffDate"
str2 = "SELECT Type FROM T_EmpRates WHERE EmpID=" & Me.EmployeeID & " GROUP BY Type ORDER BY Type"
str3 = "SELECT EffDate, Type, HourlyCost FROM T_EmpRates WHERE EmpID=" & Me.EmployeeID & " ORDER BY EffDate DESC , Type"

Set rst1 = CurrentDb().OpenRecordset(str1, dbOpenDynaset)
Set rst2 = CurrentDb().OpenRecordset(str2, dbOpenDynaset)
Set rst3 = CurrentDb().OpenRecordset(str3, dbOpenDynaset)
Set rst4 = CurrentDb().OpenRecordset("T_HourlyCost", dbOpenDynaset)

If rst1.RecordCount = 0 Then Exit Sub
If rst2.RecordCount = 0 Then Exit Sub
If rst3.RecordCount = 0 Then Exit Sub



rst1.MoveFirst
i = 0

Do
        rst2.MoveFirst
    Do
        rst3.MoveFirst
        rst3.FindFirst "[EffDate] <= #" & Format(rst1(0), "mm/dd/yyyy") & "# AND [Type]='" & rst2(0) & "'"
            If rst3.NoMatch = False Then
                i = i + Nz(rst3(2), 0)
            End If

        rst2.MoveNext
    Loop Until rst2.EOF

    rst4.MoveFirst
    
    rst4.FindFirst "[EmpID]=" & Me.EmployeeID & " AND [EffDate] = #" & Format(rst1(0), "mm/dd/yyyy") & "#"
    
    If rst4.NoMatch Then
        rst4.AddNew
        rst4(1) = Me.EmployeeID
        rst4(2) = Format(rst1(0), "dd/mm/yyyy")
        rst4(3) = i
        rst4.Update
    Else
        rst4.Edit
        rst4(3) = i
        rst4.Update
    End If
    
    i = 0
    rst1.MoveNext
Loop Until rst1.EOF

....

 

[BoxHead]

edsuk, I think you are making this more difficult than it has to be.

Create a Table with the following fields
ID, EffDate, Total, Lab, Veh, Equip

Take a look at

http://r937.com/relational.html

Normalizing your table to have fields for
EmpId, EmpName, EffDate, EffCharge, ChargeType
would allow you to get at and update the data with more flexibility and fewer 'work-arounds'.

You could use a Totals query grouping on EmpId, EmpName, MaxOfEffDate, EffCharge, ChargeType to return

[tt]
1 Alan Adams 1/1/2005 $12.50 Lab
1 Alan Adams 6/1/2005 $2.50 Veh
1 Alan Adams 3/1/2006 $1.50 Eqp
1 Alan Adams 3/1/2005 $3.50 Ins
2 Bill Burns 1/1/2006 $13.50 Lab
2 Bill Burns 1/1/2006 $2.50 Ins
3 Charlie Cox 12/1/2005 $12.50 Lab
[/tt]

Crosstab and Totals queries based on that first query should be able to get you all of the information you need. Adding new types would automatically add rows to Select queries and add columns to crosstabs.




When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[edsuk]

Boxhead

Agree that my initial approach made it difficult, and can confirm this was not the approach I eventually used.

The problem I had is that I have many complex queries (mostly union type) linked to a specific table and I was trying to avoid having to re-write all affected queries by this request. Having the totals using a group by query would have meant having to re-write all the queries that referenence the table as originally designed. This table contained EmpID, EffDate, Cost.

This is why I steered away from the relational model for this request. The code basicaly takes information entered by the user into a new table and calculates the total cost for each effective date by employee, it then either amends the data in my original table or adds a new record if the empId/effective date is not found.

This approach was fairly easy in the end.

I would like to thank you however for responding, cheers.

Mark