Changing direct labor rate without affecting past reports

  • Thread starter Thread starter corollamn32
  • Start date Start date
C

corollamn32

I have a question about how I can change a direct labor rate from one rate to
a lower rate without affecting past jobs and reports and jobs in process at
the current rate. I still want to be able to use the access system I have and
what would be the quickest and easiest way to fix this. Would a formula or
field need added.
 
On Sat, 7 Mar 2009 12:53:00 -0800, corollamn32

The classic version of this issue is where orders have been taken and
now we want to change the price of some products without affecting the
OrderTotal for existing orders.
The solution is to copy the price in the OrderDetail record. And
that's exactly what the Northwind sample database does.

I'm sure you can apply this to your situation.

-Tom.
Microsoft Access MVP
 
That depends on whether your data structure supports job-specific labor
rates. If you post your data structure, someone might suggest alternatives.
 
So how can this be done. Does a new field or formula need added. I want the
new jobs to use the new rate and by changing this rate for new jobs I don't
want to affect old jobs or or reports or jobs in process that current are
using the old rate. How can this be done specifically. Specifically what
needs added or changed to the program to make this work.
 
I can't post my data structure unfortunately, this is work and I have it at
work. If my data structure supports this then how would it be fixed and if it
doesn't how would it be fixed.
 
That's actually the nub of the question--we can't SEE your data structure,
so we can't know if it will or won't support this requirement.


What you can do, htough, is create a list of the fields in this table, sort
of like this, and post it:

tbLaborRates
============
LaborRateID
LaborRate
LaborRateEfffectiveDate


and so on.

From there, it would be possible to give you some help.
 
Is that all I need to do to make the change and not affect past reports and
not affect the rest of the access system from working the same way it has
work before.
 
Steve said:
Yes! Also be sure in your forms and reports where you display direct labor
rate that you diplay DirectLaborRate. I provide fee-based help for Access.
Excel and Word applications. If you need help making the changes, I could
make the changes for you for a modest fee. If you want my help, contact
me.

Steve
Click to expand...

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them.

He is a total loser because even though he has been told thousands oftimes
over the years, he still can not fathom the fact these newsgroups are not
for soliciting work. He has proven many times in the Access, Word and Excel
newsgroups that he does not know what he is talking about and he is the only
one who considers him to be an expert.

He has claimed to have help thousands, but it appears that none of those he
has helped is flooding him with work. He still finds a needs to beg for
work.

So stevie, have your rant and go away, again. Hopefully permenantly.

John... Visio MVP
 
Can anyone help me with this question and give me some kind of answer to this
so I know how to fix this. Thanks,
 
As originally stated, you need to provide a bit more detail about what your
current tables actually look like now.

This is probably going to involve some table modifications, but what those
modifcations will be can't really be addressed without a starting point from
which to address them: the current schema.

Anyone can make GUESSES about your current schema, but they will remain
guesses, and we won't be in a position to offer help, until you are able to
present the actual schema.
 
Unfortunately, this is one of the side effects of stevie trolling for work.
The original poster is left out to dry.

There are a couple of ways of handling this.
1) Add a field to the record to store the current rate.
2) Expand the rates table to include the date the rate became effective.
The first requires you to populate the previous entries with an appropriate
value and the second requires a bit of coding to make sure the correct rate
entry is selected based on the date of the transaction.

John... Visio MVP
 
Thanks, John

John... Visio MVP said:
Unfortunately, this is one of the side effects of stevie trolling for work.
The original poster is left out to dry.

There are a couple of ways of handling this.
1) Add a field to the record to store the current rate.
2) Expand the rates table to include the date the rate became effective.
The first requires you to populate the previous entries with an appropriate
value and the second requires a bit of coding to make sure the correct rate
entry is selected based on the date of the transaction.

John... Visio MVP
Click to expand...
 
#1 - There was no copyright and it is a very generic idea, so plagarism does
not apply. You trying to sell a CD of code segments gleaned from the
newsgroups with no credit to the original poster IS plagarism.
#2 - Only complex for you

Now grow up and go away.

John... Visio MVP
 
Adding a field to store a value that must be retained is standard. I have
suggested it before, as have many, many others. By your definition, you
have plagarized us.
 
John... Visio MVP said:
#1 - There was no copyright and it is a very generic idea, so plagarism
does not apply. You trying to sell a CD of code segments gleaned from
the newsgroups with no credit to the original poster IS plagarism.
Click to expand...

Good one. BruceM's response was also good. I'm just envious that he
markets the code he got from me much better than I do :-).
#2 - Only complex for you
Click to expand...

Not necessarily. The kinds of problems where variable rates that depend
on dates are used can get very complex very quickly, especially the
temporal SQL. If the rate table is simply used to get a value to be
used historically, then, yes it's simple. Otherwise, be careful.

In:

http://groups.google.com/group/microsoft.public.access/msg/11b91e542fbda26f

I said:

"Under current conditions, without those advanced SQL capabilites
present in Access, the SQL required to maintain a separate temporal
table suggested by Jamie can get way out of hand quickly with only a few
slight changes to the requirements. Only a few Access programmers can
keep up with the complexity of the queries shown in Snodgrass' book
until Access gains newer SQL capabilities. In the meantime, we have to
get by in a way that can't quickly exceed our SQL capacities. Handling
temporal data properly is much tougher than it looks. My advice for
anyone venturing down the temporal table path is to warn their employers
that small design changes can potentially create a huge amount of
effort. I think it's great that Jamie is helping prepare Access
programmers for a more standardized future, but I do not believe that
Access provides us with a realistic means to achieve that vision yet."

James A. Fortune
(e-mail address removed)
 
James A. Fortune said:
Not necessarily. The kinds of problems where variable rates that depend
on dates are used can get very complex very quickly, especially the
temporal SQL. If the rate table is simply used to get a value to be used
historically, then, yes it's simple. Otherwise, be careful.

James A. Fortune
(e-mail address removed)
Click to expand...


I have done it many times in various environments that go back to mainframe
DBs that predate SQL. It is a relatively simple concept, but as you with any
database concept, you do have to be careful and have things well documented
and understood by other developers you work with. In another thread, Ken
Snell gave a good example of how it could be done in Access.

Remember, I was talking about stevie, who has a copy/paste mentality and
usually does not grasp the concepts.

John... Visio MVP
 
John... Visio MVP said:
I have done it many times in various environments that go back to
mainframe DBs that predate SQL. It is a relatively simple concept, but
as you with any database concept, you do have to be careful and have
things well documented and understood by other developers you work with.
In another thread, Ken Snell gave a good example of how it could be done
in Access.
Click to expand...

What you're thinking of is not nearly as complicated as what I'm talking
about. I rarely get accused of calling something simple difficult. By
complex, I mean complex, even to Ken Snell or to you or to me. By very
quickly, I mean very quickly.
Remember, I was talking about stevie, who has a copy/paste mentality and
usually does not grasp the concepts.
Click to expand...

I think he accidentally got it more right than he intended, unless he
read my post from the past and thought, "Maybe if it's considered
difficult I won't get flamed." Anyway, the warning wasn't for "Steve."
I don't mind if he gets swamped because he underestimated the
complexity of the task. I understand that people need to be told that
"Steve" is not smart enough to do the job based on his posts :-), but
lurking near this simple problem are complications you really don't want
to get involved with. Think in terms of half or full pages of SQL per
query once things get going. Who wants to maintain that? Then it
starts getting complex. To give you an idea, here's a link to the book
on temporal data using SQL that Jamie Collins cited:

http://www.cs.arizona.edu/people/rts/tdbbook.pdf

Just about the time I read the book I was given some temporal data to
implement. After seeing how quickly the queries became complex, and
having knowledge of where things were headed, I steered the process back
toward something simpler as soon and as tactfully as possible. Again,
if you see a similar situation developing, be careful. The warning is real.

James A. Fortune
(e-mail address removed)
 
Back
Top