Some basic help please (1 Viewer)

[okanagan]

I'm posting my first question, having read the newbie guides. I have little knowledge but much desire. I would appreciate any guidance.

I am developing an application to track labour hours and costs by project. I've attached a prototype database with 3 tables:
Labour - contains employee, project, hours, pay period end date, calculated cost.
Rates - contains classification, hourly rate
Staff - contains employee, classification

For relationships I've linked:
Employee in Labour to employee in Staff.
Classification in Staff to classification in Rates.
That seems to work for producing the results I want from simple queries.

Employee classification changes over time. I plan to use a form so users can update.
Hourly rate by classification also changes. I'll use a form for this as well.

I envision users adding hours for their employees on a form that captures employee, pay period, project, hours. I'd like to calculate the cost of those hours by referencing the employee's classification plus the hourly rate for that classification. The calculated cost would then be stored in the labour table and retain the calculated value in spite of future employee classification and rate changes.

I've tried various combinations of queries and forms (not included in my sample upload) but I've run into #Name errors when I put the calculated field on the form and the form will not allow the adding of new records, even with the DataEntry property set to yes.

Am I on the right track at all?

Thanks,
Chris
Using Access 2016

 

[CJ_London]

the rule is one form, one table - your form has 3 tables. You need to use subforms

It's not totally clear to me what you are trying to do or what classifications are, but you should be linking to ID's (the primary key) not the name in your relationships

I suspect you need the following tables

staff (PK, name)
classifications (PK, name)
projects (PK, name)
rates (pk, classificationFK, rate, datefrom)
staffclassifications (PK, staffFK, classificationFK, datefrom)
ProjectLabour (PK, ProjectFK, StaffFK,Date, Hours)

FK means Family or Foreign Key ans is the link back to the PK of the relevant table

From the above for hours entry you would have a form based on ProjectLabour table. Select the projectFK, the staffFK, enter the date and the number of hours.

A report can then look up the staff member rate via the staff classifications taking account what the date is

 

[okanagan]

Thanks CJ_London. Your reply suggests an approach I had not thought of. Classification is an attribute of each employee and changes over time. Each classification has a particular hourly rate, which also changes over time. I had been trying to calculate the costs (rate*hours) when the hours worked were recorded and freezing the result so future reporting could use the actual costs based on the rate at the time, rather than the rates in force when the report is run. But with your suggested datefrom fields, which I assume are to track when classifications or rates change, I won't calculate the labour costs until they are needed for a report. The date of the ProjectLabour record will be used to derive the appropriate rate.

I'm excited to persue this! Thanks again,
Chris
PS I also appreciate the suggested structure, I think it's just what I needed.

 

[The_Doc_Man]

Sounds like you are still putting things together. That is a good thing because you have time to do a little study before casting everything in concrete.

You claim "little knowledge" and we ALL went through that stage. May I suggest that right now, you should read about normalization so that you will see some ideas on how to build tables that relate to other tables. And you would also see why you DON'T put certain things in the same tables with other things.

If you decide you want to do this, you can search THIS forum using the search feature in the top bar to the right of where you login name shows up.

If you decide to search the general internet, search for "Database Normalization" because there are other kinds of normalization. When you do that search, look at the domain of the results and start off with .EDU sites. I'm not saying the .COM sites are necessarily going to be wrong but they might more aggressively try to sell you something.

 

[Pat Hartman]

I fixed your database, however, you probably want to change how the hours entry is done to minimize data entry but first things first. But first, you need to really get a grip on the changes I made since they are substantial.

1. I fixed the tables so that they are properly defined and related as they need to be. Once I added the correct foreign keys, I deleted the text fields that were there before.
2. I changed the names of your primary keys. It makes no sense and causes a great deal of confusion to have all your IDs named "ID". For those of you old enough to remember: "Hi, I'm Darrel, this is my brother Darrel, and this is my other brother Darrel"
3. I changed the joins in your form's query because I changed the way the relationships work. Relationships are always table field (which iscalled the Foreign key) to PK of the "parent" table.
4. I added a combo to select the employee. Technically, the form adds/updates records in the labor table. The joins are used to pull in data from other tables for reference. There is no problem with using a multi-table query as your recordSource but you do need to be careful to actually only update a single table. For that reason, I locked and removed the tab stop from the reference fields and the calculated field. You don't want to accidentally change the class or rate on an employee record as you are entering hours.
5. I fixed the calculation so that cost will be saved correctly. Saving this calculated field is not a violation of normal forms although it would probably have been better to save the rate and always calculate the cost but saving the calculated cost is OK. Keep in mind that in the real world, I would do this differently and I would store the rate rather than the calculated value.

You might also do some reading on normalization. That will help you to understand how tables are related.

 

[okanagan]

This is being a great help. I have now read up a bit about normalization.

Unsurprisingly, it seems there are many different ways to achieve the same result. CJ_London suggests 6 tables while Pat Hartman kindly fixed my database while staying with just 3 tables. I'm not sure which way to go but I get the message that the more time I spend improving the structure, the easier the later development will be.

Thanks Pat for showing me how to make a form calculate something before updating the database, by using Visual Basic code embedded into the form properties. I wonder, is the use of code like this common in Access applications?

I'll spend some more time to study everyone's input and to experiment. I now think I won't save the costs with the hours. Calculating the costs only when needed for reporting seems like the better way to go.

Thanks tons. I'm likely to have more questions but I feel now that I can make some progress, where I was just spinning my wheels before.

 

[Pat Hartman]

Keep in mind that your schema does not track rates over time. Therefore, you either need to change the rate table so that it has start and end dates OR you have to store the rate at the time the hours are recorded. It is better to store the rate rather than the result of hours * rate but you need to do one of the two things. Having used both methods, I find it easier to store the rate rather than keep rate history. If you have a requirement to keep rate history, then that should influence your decision.

I agree with Doc that your schema is probably too simplistic but you have to start someplace. The important thing is to think through what you need out of the application thoroughly because that will dictate what you have to collect in your input forms. As Doc says, Access isn't going to give you something you didn't give it first.

 

[Cronk]

Re not tracking rates over time, historical reporting will be inaccurate if the rates have changed in the report period.

 

[okanagan]

I posted a simplified version of the database to ask a basic question. I'll need to flesh it out quite a bit but I'm still wandering in the wilderness regarding the options for design. Some questions for the patient:

1. Is it better to use a data field for the primary key, provided there are no duplicates in the field, rather than use the autonumber ID that Access adds for you if you don't identify a primary key?
2. CJ_London's response, if I understand it correctly, suggests adding several tables with just single fields, besides the primary key, which I assume is an autonumber. Contrast that with Pat Hartman's solution which just stuck with the original 3 tables. Which approach make more sense for me? Is it personal preference?
3. I don't think Cronk's comment applies because the suggested plan is to store the rate in effect at the time the hours are entered, so changes in rate won't affect historical reporting, unless I'm missing something?

However, I'm now leaning towards not storing rate nor hours * rate. Instead I'll maintain start and end dates in the rate table and use them to derive the applicable rate when calculating cost of the hours in a query. The bonus of this approach is that I can get historical costs for a project or calculate costs for the same project at today's rates. Does this seem reasonable?

 

[Isaac]

I'll throw in my opinion on one point or so. For more study, simply google "surrogate vs. natural keys". Personally, I'd suggest googling a wider population than just AWF, because by including larger RDBMS you get a lot of viewpoints and input that is valuable and more than just the Access community--but still useful points that will relate to doing things in Access.

Is it better to use a data field for the primary key, provided there are no duplicates in the field, rather than use the autonumber ID that Access adds for you if you don't identify a primary key?

No. Use a key with no business meaning whatsoever. Access's AutoNumber is perfect. Doing anything but this just drags you down a hundred rabbit holes. No matter how much you think a value will be unique and never be edited, the reality is that in some % of those times, you really don't know what the future holds or aren't foreseeing all the possibilities. Imagine somebody in HR designing a table for employees. He feels positive that he will never enter a record with the same employee ID twice. Then someone leaves and comes back later. Someone from another system or higher up says to use the same employee ID #, but change other attributes. Maybe this creates a problem. Probably it would have been best to allow for this previously unforeseen possibility. Oops, should have used a surrogate PK. Often times what drives the "I think this will always be unique" is a perspective limited by the inability to see a bigger picture and upstream or downstream systems. No matter how unlikely you think it is, why take the chance at all...ever? Imagine the stress you'll feel when someday the unique paradigm is blown up by new information, and suddenly a fundamental re-design of large proportions is your weekend rush job. With the ability to create your design in such a way that relationships are enforced through KEY values, and uniqueness is enforced through indexes (and/or other methods), there is no such thing as a "need" for a natural key, so why create a risk where none needs to exist.

Sample scenario I actually saw happen with a colleague when working for a gov't taxing agency:

1. John is told to design tables to import tax returns on a daily basis as they come down from an upstream corporate system.
2. John considers using SSN as a natural key, but thinks about it a while and realizes he'll need SSN + TaxYear.
3. John has some import problems. Upon troubleshooting, he realizes he forgot about amended returns...Oh yes, of course! I'll change the natural key to SSN + TaxYear + Form number [1040, 1040x, etc]. John spends the weekend working overtime to correct everything - which involves a lot of remediation involving removing and replacing indexes, and makes several data mistakes in the process.
4. A few weeks later, John has more import problems. John realizes that occasionally a taxpayer actually files more than one amended return! He wouldn't have 'guessed' this, but another weekend of emergency work fixes the problem. The natural key is now SSN + TaxYear + Formnumber + DateReceived. At this point John is questioning everything, but the solution still isn't apparent to him.
5. Later on, more import problems. What could it possibly be this time?? He finds out that in certain "exception" circumstances, the same tax return will be processed twice - once by the system, and again manually to correct certain flagged errors. In these unique circumstances, the upstream application from him will actually send him the exact same return, twice, on the same date. This was something he theoretically could have foreseen with enough investigation up-front, but his time in requirements gathering had limits, and unfortunately he missed this. Turns out there is a unique attribute "Corrected", which takes a 1 or a 0.
6. John considers once again re-designing his natural key to include the "Corrected" column, but soon realizes that the safest course of action is to create a surrogate key with no embedded meaning. He spends two long weekends redesigning indexes and keys, updating records on primary and foreign key relationships, creating the necessary constraints and removing others and makes a number of mistakes in the process. In the future, after conferring with colleagues to explain all the negative impacts from these incidents, he decides to use surrogate keys from now on and not tempt fate.

...and I haven't even mentioned how long it took John to find out about the process of assigning non-SSN taxpayers TID's, which started out as a dummy number (inside this particular processing scenario), BEFORE getting a unique one.

Your business users don't have to know anything about these surrogate PK and FK keys which are being used behind the scenes to do most of the joins. Further, joins on a datatype used by AutoNumber are the most performant kind, while most creatively derived natural keys simply won't be able to use a numeric datatype.

Some common "but then I can't" 's:

• Yes, you can still give business users something they like to call a "key" if you want, made up with some embedded business meaning. It will be additional to your actual, database-level, PK. Just don't leave the meeting guaranteeing them on your part that it will "always be unique", unless you actually HAVE created a constraint that proves this, whether on the table side or through input programming. Never, ever, agree with business users who swear that such-and-such creative combination of things will always be unique on the sole basis of data components alone. I say this in the context of, for the purpose of creating a key.
• You can still enforce uniqueness by way of indexes on column(s)

However, I'm now leaning towards not storing rate nor hours * rate. Instead I'll maintain start and end dates in the rate table and use them to derive the applicable rate when calculating cost of the hours in a query

That seems like a good idea. I've seen a lot of this kind of thing done in corporate warehouses when it comes to healthcare eligibility segments. It will cost just a bit more effort on the querying side, but probably worth it to avoid storing unnecessary and potentially change-able data as if it were static.

 

[sxschech]

Regarding post #5

"Hi, I'm Darrel, this is my brother Darrel, and this is my other brother Darrel"

It is actually Larry.

"Hi, I'm Larry, this is my brother Darrel, and this is my other brother Darrel"

 

[The_Doc_Man]

The answer to question #1 of your post #9 is open to debate, but generally the question is decided by whether the data field key is STABLE. That is, how often is it ever edited? We call the "data field" key a "natural" key and the autonumber key a "synthetic" key. You can look up "synthetic" key or "natural" key in this forum and get a huge debate.

I am all in favor of natural keys as long as they are indeed unique and immutable. Like say you have a company-issued employee number (true number, not number-letter-number) for someone. There is no reason to create a synthetic key if your employee number will fit in a LONG integer. Inventory SKU-type numbers also can be used in place of "rolling your own" numbers - if they fit. If the key you want to use is a field in your table but is actually synthetic from some other system, that also works. I.e. if you used a synthetic key in the payroll system and that BECAME the employee's company ID, there is no reason to not use the number in a secondary but separate database that sometimes ties into the first system.

However, where you have doubt over permanence or uniqueness, or if the natural key is longer than would fit in a LONG integer, a synthetic key might be better. The length factor is of course related to the idea that an index (that holds the values of your various keys) does best when the keys are short. Which is why synthetic keys work so well when compared to long text words. Shorter keys fit better in the indexes than do the longer text values because you can put more short keys in a buffer at one time. And Access works on buffered disk blocks.

Your question #2 regarding "which approach is better" for those "translation" tables is actually related to #1. When you can use a natural value, it might not be wrong to do so. But the question is, how often does that value get used around the system? Take the simple case for the Staff table CJ suggested, with a PK and the name of the person. Most names are longer than four letters so the odds are quite great that you will need 20 to 30 characters for names (and maybe more if they came from India or Malaysia). So... how many times will you need to store each person's name? If you have only one staff table, store the literal name. If that person's name appears in several places around the DB, then there will be many cases where you can use 4 bytes (1 LONG) in each of those other places. Only you can decide how many times that name will be used.

Your question #3 (store the rate and hours applicable at the time) is perfectly valid. Your counter-idea of storing the rates and dates is a perfectly valid. You have to look at what it costs you and what you gain from it. I can't tell you because you need to look at how often you will need to compute that rate.

I will only add that it is extremely rare for a problem is unique and it is equally rare for the solution to be unique. You often have choices that are dictated by the details of your situation. The best we can hope to do is warn you of pitfalls along the way.

 

[CJ_London]

It depends on business need but keeping a rate history gives you more control - it also means you can project into the future when a rate is going to change, so quotes etc can take this into account a price change in the future. Same applies to people who have given notice, on LTS, etc

I would also store the calculated value (or to be more precise it's components) for data which effectively becomes a legal document - basically once created it cannot be changed. For example an invoice once issued cannot be modified. If a correction is required it is done by issuing a credit note or another invoice.

 

[Pat Hartman]

Re not tracking rates over time, historical reporting will be inaccurate if the rates have changed in the report period.

Not true if you examine the table where the rates are used. As I said - if you have a need to keep a history of rates, you should use that method.

The bonus of this approach is that I can get historical costs for a project or calculate costs for the same project at today's rates. Does this seem reasonable?

I believe i said it was. Just make sure you understand how you will join to the rate history table. I find that keeping a start AND and end date makes for the simplest solution. The default end date should be some fixed date such as 12/31/2100. Otherwise, if you keep just an end date or start date, your query will need a max() function and that will make the query not updateable and so not suitable to use as the RecordSource for your edit form. By keeping both a start and and end date, your new record logic becomes more complex because you have to close out the previous date range by replacing the far future date with the actual end date and then add the new record. Plus you have to make sure that there is no overlap in the ranges as well as no gaps. So the new rate must start on the end date of the most recent rate + 1 day.

Is it better to use a data field for the primary key, provided there are no duplicates in the field, rather than use the autonumber ID that Access adds for you if you don't identify a primary key?

I would recommend going with an autonumber as the PK. If you already have or want to have a more user friendly ID, then I would add that as a separate field along with a unique index to prevent duplicates. The autonumber is used for all internal work including relationships. The user-friendly key is used for searching only. The reason for keeping the two separate is that over time, you may decide you need to change the rules for constructing the user-friendly key and so the field size may change. Not using that field to enforce relationships means that you won't have to change any table but the one holding the user-friendly key.

"Hi, I'm Larry, this is my brother Darrel, and this is my other brother Darrel"

Someone with a better memory Thanks