ACCESS FEED: Re: [MS_AccessPros] Project and tasks cost question

Selasa, 18 Juni 2013

Re: [MS_AccessPros] Project and tasks cost question

An image of what the database looks like is in the photos section of the group. It is an old image but it has not changed too much since then.

Jim Wagner
________________________________

________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, June 17, 2013 11:59 PM
Subject: RE: [MS_AccessPros] Project and tasks cost question

Jim-

You need day-by-day detail to be able to calculate correctly. If all you have is project start / end and total hours, it's impossible to calculate the correct rate. What does your table look like that has hours in it?

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of lancucki
Sent: Monday, June 17, 2013 11:19 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Project and tasks cost question

In that case, you would break the results into a series of intervals based on the effective dates and use the rates for each interval. A bit more work, but you still use the original table. This is the basis for handling historical data.

John…

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Jim Wagner
Sent: Monday, June 17, 2013 3:53 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Project and tasks cost question

John,

What happens if the project or task spans over the dates? will it calculate both pay rates?

Jim Wagner
________________________________

________________________________
From: Jim Wagner < <mailto:luvmymelody%40yahoo.com> luvmymelody@yahoo.com>
To: " <mailto:MS_Access_Professionals%40yahoogroups.com> MS_Access_Professionals@yahoogroups.com" < <mailto:MS_Access_Professionals%40yahoogroups.com> MS_Access_Professionals@yahoogroups.com>
Sent: Monday, June 17, 2013 8:04 AM
Subject: Re: [MS_AccessPros] Project and tasks cost question

John,

State University with the state broke, No I did not expect a raise.

I will try the suggestion and let you know

Jim Wagner
________________________________

________________________________
From: John Viescas < <mailto:JohnV%40msn.com> JohnV@msn.com>
To: <mailto:MS_Access_Professionals%40yahoogroups.com> MS_Access_Professionals@yahoogroups.com
Sent: Monday, June 17, 2013 7:57 AM
Subject: RE: [MS_AccessPros] Project and tasks cost question

Jim-

Congrats! What, you never expected to get a raise???

John's suggestion is good. With that, write a little public function like
this:

Option Compare Database
Option Explicit

Dim intRateCount As Integer
Dim curRates() As Currency
Dim datEffDates() As Date

Public Function GetRate(datTransDate As Date) As Currency
Dim db As DAO.Database, rst As DAO.Recordset, intI As Integer
' If rates table not loaded,
If intRateCount = 0 Then
' Point to this database
Set db = CurrentDb
' Open the rates table
Set rst = db.OpenRecordset("SELECT * FROM tblRates " & _
"Order By EffDate Desc;")
' Loop to load all the rates in descending date order
Do Until rst.EOF
' Add 1 to the number of rates
intRateCount = intRateCount + 1
' Expand the two arrays
ReDim Preserve curRates(intRateCount)
ReDim Preserve datEffDates(intRateCount)
' Load the data
curRates(intRateCount) = rst!Rate
datEffDates(intRateCount) = rst!EffDate
' Get the next record and loop
rst.MoveNext
Loop
' Close out the recordset
rst.Close
Set rst = Nothing
Set db = Nothing
End If
' Now find the rate
For intI = 1 To intRateCount
If datTransDate >= datEffDates(intI) Then
' Found it!
GetRate = curRates(intI)
Exit For
End If
Next intI
' Done
End Function

tblRates needs a Rate and an EffDate column. First record will have a date
like 1/1/1900 and your current rate. Second record will have the date your
raise is effective and the new rate. Future raises just need another row in
the table!

Everywhere in your app where you have hard coded the rate, substitute a call
to the GetRate function. Note that the function loads the rate table the
first time it is called, then uses the loaded array to quickly return the
rate thereafter.

Have fun...

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
<http://www.viescas.com/> http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: <mailto:MS_Access_Professionals%40yahoogroups.com> MS_Access_Professionals@yahoogroups.com
[mailto: <mailto:MS_Access_Professionals%40yahoogroups.com> MS_Access_Professionals@yahoogroups.com] On Behalf Of lancucki
Sent: Monday, June 17, 2013 4:29 PM
To: <mailto:MS_Access_Professionals%40yahoogroups.com> MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Project and tasks cost question

Create a table for the rates that include an effective date. Anything before
the effective date will use the base rate, anything after with use the rate
based on the effective date. So if the rates change, you can add a new
record with the new effective date and rate.

John. Visio MVP

From: <mailto:MS_Access_Professionals%40yahoogroups.com> MS_Access_Professionals@yahoogroups.com
[mailto: <mailto:MS_Access_Professionals%40yahoogroups.com> MS_Access_Professionals@yahoogroups.com] On Behalf Of luvmymelody
Sent: Monday, June 17, 2013 10:00 AM
To: <mailto:MS_Access_Professionals%40yahoogroups.com> MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Project and tasks cost question

Hello all,

Well it finally happened, I will be getting a raise after 6 years next
month. The problem is that my tasks and project database has been using a
calculation on the forms and reports for calculating the costs for the
project time. Though I want to change the cost on the projects, I only have
the calculations in expressions on the forms and reports not in a table. I
can change the expression to the new rate but that will change the costs for
all previous work to the new rate. This will not be accurate because all
previous work will have been done at the lower rate.

How can I rework the database to keep the lower rate for work already
completed and still working on and any new projects and tasks use the new
rate?

Thank You

Jim Wagner

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]