Need to change numbers without updating underlying table

[streatorunlimited]

Hi.

I work for a small, non-profit that gave me the task of making a database to track some of our consumers' hourly and piece-rate wages. Because the resulting information must interface with our accounting software every 2 weeks to pull a .txt file input, I had to design everything with that result in mind (of which now works).

If need be I can post a copy of my db, but I would have to remove a significant amount of client data (HIPPA), so I'll try to explain as best I can for now:

I have a table, "tblActivity" which records the bulk of their activity (ID, Date, Hours, Supervisor, Pieces, etc,.)

I based a query off of this table that calculates total hourly[Hours]*[Payrate table], piece-rate total [pieces]*[piece rate] and then adds the two together for total wages.

I built a form off of the query, "Consumer Activity" for data entry that automatically pulls up the hourly and piece rates when you select job type, so that the intended entry person wouldn't have to look off of the massive payrate chart.

All has been working fine given my humble knowledge of Access except for that I did not provision for one important detail, commensurate wages. You see, sometimes an hourly wage is the same job as is pulled up in my pay rates table, but is a different wage. I would like to be able to plug in the commensurate wage (dynamic number) without having it update the rate table.

I'm not hopeful that my design isn't horrible flawed and I'll have to practically start over, but it's worth a shot to ask.

Edit- I edited out the personal info and am posting my DB. Please go easy on me as this is about my second every database the first of which was far less complex.

 

[missinglinq]

I did not provision for one important detail, commensurate wages. You see, sometimes an hourly wage is the same job as is pulled up in my pay rates table, but is a different wage. I would like to be able to plug in the commensurate wage (dynamic number) without having it update the rate table.

Sorry, but I've read this post five times and to be honest, I can't make heads or tails out it! You really need to do a little more explaining as to exactly what commensurate wage means and how you hope to "plug it in." When posting questions, you need to keep in mind that the chances of anyone on the forum knowing the ins and outs of your particular business/industry and its business requirement are slim, and so we have to depend on you to help us out.

Welcome to Access World Forums!

Linq

 

[streatorunlimited]

My apologies for being so confusing; I'm a bit burned out on all of this on a Friday.

In my industry, commensurate wage is a percentage of a normal hourly wage that is calculated based upon all kinds of factors that are updated ever 6mo and are different for every consumer. In the end, it means that some consumers get a percentage of a normal hourly wage.

For example, if a consumer can get $6/hr normally and their commensurate wage is 50%, then they get $3/hr. Sadly, the numbers are never this clean and are usually some odd percent, hence the reason I want to plug in the commensurate rate on the fly in place of the table-based hourly rate (if possible).

Edit- I'd also like to add that our data-entry person has no issue with manually plugging in the commensurate rate; it's that she doesn't want to also have to key the normal hourly rate as well. That's why I have it look up from the table.

Thank you for getting back to me.

 

[missinglinq]

No problem! It easy to forget that everyone doesn't know what you know and take for granted!This makes much more sense! Having a challenged grandchild, I'm guessing that this percentage involves some subjective evaluation and is not something Access is going to be able to calculate for you. The simplest way to do this would be to have a field in your table to hold this percentage in after the evaluation has been made.

Then, somewhere, probably in your query, you're going to need a calculated field, something like

AdjustedPay: [Hours]*([Wages]*[CommensurateWagePerCent])

and use the calculated field, AdjustedPay, in your form.

If, on the other hand, you want the data input person to enter this percentage, you could do the calculation on the form. The simple formula would be similar to the calculated field in the query, using it in the AfterUpdate event of the CommensurateWagePerCent textbox. :

Me.AdjustedPay: Me.Hours*(Me.Wages*Me.CommensurateWagePerCent)

I said the simple formula because if you do it this way, at the form level in code, you're also going to have to check to make sure that none of the involved controls (Wages, Hours) are null before running the calculation, because

Anything * Null = Null.

You're also going to have to put similar code in the AfterUpdate events of Wages and Hours, checking the other two controls for Null. That's because you don't know but what the data input person won't enter Hours, Wages, CommensurateWagePerCent, realize they made a mistake, then go back and changes either Hours or Wages. Without code in these places also, the AdjustedPay wouldn't be re-calculated.

As you can see, the easy way to do this is to have the formula in the query. Access then takes care of not running the calculation until all components are in place.

 

[streatorunlimited]

I'm guessing that this percentage involves some subjective evaluation and is not something Access is going to be able to calculate for you.

Precisely. The consumer's wage is determined by taking a percentage of a "prevailing wage," a number determined by the average hourly rate based upon job type/locale.

It's all pretty confusing to me how they arrive at the percentage, but luckily for me I don't have to calculate it . I just need to look it up and plug it in.

I'll try that. Thank you

Hey. Thank you for the extra effort in answering my question. It has helped me get my head around this.

 

[missinglinq]

These types of subjective judgments not only look confusing to a lay person, if you will, they often are confusing to professionals in the field as well! I once developed a weighted questionnaire to be used by new therapists in evaluating how frequently home oxygen patients needed to be visited. Old hands took about 30 seconds to make this judgment call, but newbies spent hours agonizing and frequently made the wrong call. I was amazed when the form took me two days to develop and ended up with 43 questions.

Glad to help you out. Post back if you have further questions.

Linq