Full Version: Sorry if this is too newbie a question but i could use some help
UtterAccess Forums > Microsoft® Access > Access Forms
an63lu5
hi
work in a casino, and want to track my pay. i have 4 pay rates, which are based on penalties.
I have set up a table that will hold the below fields.
date, total hours worked, normal time, time 1/2, time 3/4, double time 3/4.
- the above are the fields i imput data into for my hours worked. because i can posibly work friday and go into saturday, the rates are different.
in the same table i have also setup other fields.
normal pay amount, time1/2 amount, time 3/4 amount, double time 3/4 amount, total pay.
I have then set up a second table with values for each of the above pay rates, they are static numbers and don't change.
ok, if i haven't confused you here is what i want to do. I want a function value or command of some sort that when i place a number in the first set of fields, reflecting the time i worked for that day, i would like it to look up the value that it reflects, multiply it by the number of hours placed in that field, and then display the total pay amount in the second set of fields.
ie: if i work 8 hours normal time, i put 8 in the 'normal time' field. Then the 'normal time amount' field will look up the 'normal time' static number in the 'pay rates' table, and multiply it by 8 and display it as the amount of '17.989' and then display the amount in the field.
finally i would like the 'total pay amount' field to display the 'sum' of all the amounts that are shown.
i hope that i have made sense, and if i am going around it the hard way let me know. i have also attached a copy of a pic of hte form, which will also help to explain if i didn't make it clear.
thanks for your help in advance
bret
ScottGem
Frankly this would be better off done in Excel. Your table structure is not normailized properly for Access. For Access you would want a single record for each time type.
ace
>normal pay amount, time1/2 amount, time 3/4 amount, double time 3/4 amount, total pay

Explain these fields.

Does
Time 1/2 Amount = Normal pay * 1.50
Time 3/4 Amount = Normal pay * 1.75
Double Time 3/4 amount = Normal pay * 2.75

If so all you need to store is Normal Pay Amount because the others can be calculated.
Same goes for Total Pay and Total Hours

So you need two tables

PayRate (NormalPay)
DaysWorked (DateWorked, NormalHrs, Time1/2Hrs, Time3/4Hrs, DoubleTime3/4Hrs)

I've attached an example .mdb file.

Keep in mind that the example is suitable for a single user personal use database.
It's not something you want to take to your boss at the Casino and use as a real
application.


Edited by: ace on Sun Jan 22 13:07:07 EST 2006.
NoahP
That's not normalized either. The fields for each time of hours are 'repeating groups'. In Access you should also use the autonumber PK. You'd need something like:
blTimeTypes
TimeTypeID Autonumber PK
TimeType Text
TimeMult Numeric
records would be like:
1...Normal...1
2...Overtime...1.5
3...Time 3/4...1.75
etc.
tblPayRates
PayRateID Autonumber PK
EffectiveDate Date/Time (Short Date)
PayRate Currency
tblHoursWorked
HrsWrkdID Autonumber PK
DateWrkd Date/Time (Short Date)
TimeTypeID FK to tblTimeType
HrsWrkd Number
Noah
an63lu5
my base rate is 17.42 an hour, this is paid between monday and friday.
on saturday my rate is time 1/2 on my base rate less loading.
o: my actual base rate is 13.9363 and hour + 25% loading (monday to friday) which works out to be 17.42/h
on saturdays it is time 1/2 (on base rate) which is time 1/2 of 13.9363 = 20.90 and so on for the other 2 rates.
I see what yo have done in your database, but it is based on 1 pay rate, and that is what i type in. I thought it would be easier if i set up the table so it holds the 4 rates, and then when i type hours worked in that specific field, ie: normal time, double time 1/2 etc, it would just look up that rate, multiply it by the hours worked and display it in the text box next to it.
i have attached a copy of my database so far, i have got hte expression up now the same as yours was, but i am getting a (#name?) error in the text box, so i feel i have missed something.
thanks heaps
bret
ace
>>DaysWorked (DateWorked, NormalHrs, Time1/2Hrs, Time3/4Hrs, DoubleTime3/4Hrs)
>The fields for each time of hours are 'repeating groups'.
The op said that he wanted to keep track of the hours he worked.
A single user with data about a single user. We aren't talking
about an enterprise level application here.
I say the fields for each type of hour are not repeating groups.
They are different pieces of information about the hours worked on a single
day. NormalHrs is not the same thing as Time1/2Hrs. You can easily write
a single non-Union query to get the total pay for a single or multiple days
using that structure. You can easily write a single non-Union query to
total any combination of type of hour.
> That's not normalized either.
I say that row is normalized. Every piece of data in the row is completely
dependent on the DateWorked, which is the only candidate for a primary
key in that row. Beyond that the table meets the business rules as stated
by the op.
>In Access you should also use the autonumber PK.
The only reason to use an autonumber primary key would be if the table
in question only had candidate key(s) made up of more than one field. Even
then it is a convenience, not manditory. It's not at all necessay if the table
is not part of a relationship with another table. Which it isn't.
NoahP
Please note, this isn't a personal attack. It hasn't been and won't be, as far as I'm concerned. It's about giving the best advice possible, based on 'best practices' for relational database design.
It doesn't matter if it's 'enterprise level' or not, not normalized is not normalized. If it doesn't need to be normalized then the setup would best be as Scott suggested, in an Excel spreadsheet. If it's in a database application, then it needs to be normalized to at least 3NF (3rd Normal Form).
How are they not repeating groups? They are all hours. They are not the same TYPE of hours, but they are all hours and should be in their own table. What if another type of hours is introduced? Do you add yet another repeating field and change every query/form/report to meet the new need, or add a record to a types table and continue on as usual?
The record is not normalized. We'll have to agree to disagree. The hour types are repeating groups. Again, we'll have to agree to disagree.
You'll fiind that 99.999% of the professionals here at UA will always use autonumber PK's in Access. You have your opinion on when it should be used, and I, and plenty of others differ. In your setup it's not part of a relationship, but, it should be.
I've attached a normalized structure that has a simple form/subform arrangement so that entries can be made for each day, or edited for each day. There are already entries for 1/22/06.
Noah
ace
>Please note, this isn't a personal attack. It hasn't been and won't be,
>as far as I'm concerned. It's about giving the best advice possible,
> based on 'best practices' for relational database design.
I'm new here, but I am not new to discussion groups. I understand the
difference between a personal attack and a difference of opinion.
Odon't respond to personal attacks.
I encourage different opinions.
Do you want to go on with this or are you certain that me being wrong
is written in stone? Your call.
ace
> my base rate is 17.42 an hour, this is paid between monday and friday.
>on saturday my rate is time 1/2 on my base rate less loading.
I have modified your db and attached it to this message.
I encourage you to take a look at NoahP's attachment as well.
THis is a standard example of a 1 to many relation and how the
gui for such an arrangment is handled.
DougY
PMFJI,
Is Noah stated, this is about advise for best practice in relational database design.
The use level of a database application should not impact the normalization of said database. It should never be the reason to "abnormalized" a database. Because we promote best practice here in UA, we will provide the OP with an advice that will support such practice. While there may be reasons for a developer to choose to slightly stray from the standard normalization rules, it should be done knowingly and while realizing the impact of such action, and not dismissed lightly.
The structure you proposed is not normalized properly. Each type of hours worked should be an independent records...(think about it this way, not every workday will have all hours worked type in it...) Noah's sample supports what the OP needs while maintaining normalization. Since it is a personal tracking system, as Scott proposed, an Excel spreadsheet may be the right answer for it (I will champion Access, but is it not the only data management tool available).
HAs to the primary key... compound PK aside (it is a different topic of discussion), it is an ancient battle ground between natural PK and surrogate PK... most developers that I know use an autonumber as a PK... although there may be another indexed unique field in the table for the users to treat as a PK... here is a post from the tutorial forum that visit the topic.
EDIT (saw your post as I was finishing mine): Nothing is written in stone... no one knows everything and we are all here to learn as much as we help others to learn.
NoahP
I think the best bet is agreeing to disagree on the candidate/surrogate keys stuff. I'm firm there that autonumber is the way to go with Access. I do use indexes, including compound indexes to insure uniqueness, but I always use autonumber PK's in my tables. I've just had too much success with zero problems doing so. Also, too often it seems, candidate keys would turn out to be text and no way I'm going to have a text PK. Way too slow.
I'm stubborn in my beliefs, but, willing to admit I'm wrong if shown I am. I make mistakes all the time, as do we all. frown.gif
Noah
ace
> here is a post from the tutorial forum that visit the topic.
I've read that .doc file before.
I think any treatise on the proper selection of a primary key that has
no mention of the requirements for a unique row and how to enforce that
is about 80% incomplete and 100% mis-leading.
an63lu5
Hi Noah
Thank you for your database. It doesn't quite do what i was trying to achieve.
I do have the database working now, thanks to Ace. I am not sure of the difference between normalized and not normalized fields etc, as I have not studied access indepth.
Sorry if i started a heated topic. As for your database Noah, it is almost what i want to do, but not quite. The database that Ace fixed is what i am looking for. On a given day my shift might run into 2 days, ie saturday night to sunday morning. I am recording my hours in relevance to the shift, so if i do 4 hours sat, and 4 hours sunday, i will have 4 hours in the time 1/2 field, and 4 hours in the time 3/4 field, therefor giving me 8 hours, and the total money earned in the total field.
thanks heaps for everyone who tried to help.
kindest regards
bret
NoahP
The one I did will do that too. You only have different records for each type of hours. One would be for 4 hours of the Time 1/2 type and the other would be for 4 hours of the Time 3/4 type.
You definitely shouldn't be storing these calculated totals. What if you change an hours amount? Can you guarantee the total will be updated correctly? Calculating totals like this as needed is the best practice to follow.
Noah
R. Hicks
You will find you are in the minority here at UA ...
Some people use natural keys in their table just to find later there could be a problem in the choice ...
You will never encounter a problem when using an Autonumber as the Primary key ...
I am a firm believer in that a Primary Key value is for Access exclusive use and should have absolutely no meaning to you or your users ...

RDH
ace
>You definitely shouldn't be storing these calculated totals. What if you change
> an hours amount? Can you guarantee the total will be updated correctly?
> Calculating totals like this as needed is the best practice to follow.
nless he has changed what I sent him the calculated values are shown
in the controls on the form only. There are no fields to store them in.
DougY
The tutorial is solid. Pages 3, 4 and 5 discuss the uniqueness per row requirement...
Is to enforcing... an autonumber is "self enforced" by the database... how much simpler and more efficient can you get?
You have the right to use whatever PK you want in your databases. You also have the right to recommend using natural keys, but that will earn you the right to hear/read opposing opinions.
HAs an additional note, I would suggest that before you share your opinion on a well written tutorial, you should read it and not just glance through it (assuming you actually opened the document).
NoahP
Wasn't sure if he was or not, just wanted to be sure that he knew the risks if it were the case.
oah
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.