have normalization questions and need help
have normalization questions and need help
(OP)
I inherited a small access system that created a duplicate table for each operator. I want to normalize the tables but am not sure of the best way to do this. I read the posts on normalization but am still unsure how far to normalize.
Here's my scenario.
operators are required to perform data entry every day and if they do not enter the data on any given day, they have to go to the supervisor to have that info entered along with a reason why it was entered by super.
Currently, each person has multiple data entry forms to fill out which causes duplicate logged in rows.
for example.
login date operator Sheets MetersScrapped FH Repaired
1/10/13 wdorey 3
1/10/13 wdorey 5
1/10/13 wdorey 2
shouldn't this be
1/10/13 wdorey 3 5 2
Below are the tabs that they will use in the new system with the field names associated.
Data Entry Tab: Login Date, Data Entry, sheets
Scrapped Meters: Login Date, Bronze/Plastic, MtrSize, MtrScrap
Returned Meters: Login Date, Returned Meters, #Meters Returned, Meter Size, Manufacturer
MeterTestingResults: Login Date, Manufacturer, Meters Tested, Meter Test REason, Bronze/Plastic, #meters Passed
FH Meters Repr'd: login Date, meter size, repairs, FHMtrRepairedReason, #FHRepaired
Repair Test Bench: login date, equip repair, machine#, pm, repairs, notes
Meters Processed: login date, Meter Size, Manufacturer, Warranty, Mtrs Processed
Meters In Stock: login Date, Meters in Stock, Manufacturer, #Instock, Warranty
MeterCratesCut: login date, mtrcratescut, crate#
Lrg Meters Issued: login date, issued meters, metersize, manufacturer, mtrsissued
Yes and Nos: login date, other duties assigned, #trucks inspected, in training, warehouse, outof office
where
1.manufacturer, meter size, Data Entry, FH MtrRepaired, repairs (used on multiple screen), equipRepair, out of office are all dropdown value lists
2. Warranty, pm are yes/no
3. bronze/plastic is either bronze or plastic
I created
tblEmployee
empid
emplastname
empfirstname
qUESTSIONS.
1. For the drop downs, since the values most likely will not change is a value list still the way to go
2. do I create one table that has all of the values for the tabs OR a table for each tab?
3. On the login date, should I tie that to employee or the work table(s). Customer wants to prevent any backdata entry for the operators (if they are late they have to go to super who will do the data entry and mark reason why late)
4. If the operators are late, would you put that info in a separate table?
5. Since I have to add in a logout date, should there be an automatic login/logout per tab?
6. Right now each tab is bound to a field, should it be unbound?
While the super doesn't say they have to enter all the data at the same time (for question 5) all tabs must be filled out daily.
Any ideas/suggestions would be great.
Thanks
lhuffst
Here's my scenario.
operators are required to perform data entry every day and if they do not enter the data on any given day, they have to go to the supervisor to have that info entered along with a reason why it was entered by super.
Currently, each person has multiple data entry forms to fill out which causes duplicate logged in rows.
for example.
login date operator Sheets MetersScrapped FH Repaired
1/10/13 wdorey 3
1/10/13 wdorey 5
1/10/13 wdorey 2
shouldn't this be
1/10/13 wdorey 3 5 2
Below are the tabs that they will use in the new system with the field names associated.
Data Entry Tab: Login Date, Data Entry, sheets
Scrapped Meters: Login Date, Bronze/Plastic, MtrSize, MtrScrap
Returned Meters: Login Date, Returned Meters, #Meters Returned, Meter Size, Manufacturer
MeterTestingResults: Login Date, Manufacturer, Meters Tested, Meter Test REason, Bronze/Plastic, #meters Passed
FH Meters Repr'd: login Date, meter size, repairs, FHMtrRepairedReason, #FHRepaired
Repair Test Bench: login date, equip repair, machine#, pm, repairs, notes
Meters Processed: login date, Meter Size, Manufacturer, Warranty, Mtrs Processed
Meters In Stock: login Date, Meters in Stock, Manufacturer, #Instock, Warranty
MeterCratesCut: login date, mtrcratescut, crate#
Lrg Meters Issued: login date, issued meters, metersize, manufacturer, mtrsissued
Yes and Nos: login date, other duties assigned, #trucks inspected, in training, warehouse, outof office
where
1.manufacturer, meter size, Data Entry, FH MtrRepaired, repairs (used on multiple screen), equipRepair, out of office are all dropdown value lists
2. Warranty, pm are yes/no
3. bronze/plastic is either bronze or plastic
I created
tblEmployee
empid
emplastname
empfirstname
qUESTSIONS.
1. For the drop downs, since the values most likely will not change is a value list still the way to go
2. do I create one table that has all of the values for the tabs OR a table for each tab?
3. On the login date, should I tie that to employee or the work table(s). Customer wants to prevent any backdata entry for the operators (if they are late they have to go to super who will do the data entry and mark reason why late)
4. If the operators are late, would you put that info in a separate table?
5. Since I have to add in a logout date, should there be an automatic login/logout per tab?
6. Right now each tab is bound to a field, should it be unbound?
While the super doesn't say they have to enter all the data at the same time (for question 5) all tabs must be filled out daily.
Any ideas/suggestions would be great.
Thanks
lhuffst
RE: have normalization questions and need help
Duane
Hook'D on Access
MS Access MVP
RE: have normalization questions and need help
CODE -->
if so it should look be a single record
CODE
For part two the tabs are meaningless. The forms in a database are just windows into data coming from one or many tables. How a form is organized does not dictate how a table should be designed. It is the other way around. It would be more helpful if you showed the table structure.
FAQ700-6905: How to document Tables/Relationships for Tek-Tips can do the documentation for you
Or at least write it like
CODE
Whatever you do, do not use look up lists at the table level. The best is always to have a table with the values, and then use combo boxes at the form level.
The tabs do not dictate your table structure, it is the organization of data to fit your buisness model. The answer is probably neither.
Not sure what that means.
No you would tag it within the table in a field
If there is no back dating, the the log in can be time stamped based on physically accessing your form
I believe that each control on the form is bound to a field in a table, is what you meant to say. There is positively absolutely no reason to go unbound, especially if you have to ask any of these questions. Only persons who should build unbound forms are advanced Access developers, and even those only in rare cases. The amount of additional work needed to go unbound, is only worth the effort in few cases. 99% of the time I see someone design an unbound Access form, I can get the same results far easier with a fraction of the code.
RE: have normalization questions and need help
Duane
Hook'D on Access
MS Access MVP
RE: have normalization questions and need help
CODE
CODE
CODE
Based on your comments and what I've read, I plan on creating tables for the drop downs and believe I need at least 2 tables.
1 table for items that will be entered only one time (daily) and another for the items that could have multiple items (daily). i.e. if the operator fixes 12 meters but they are of different sizes, then I need to have multiple entries.
I hope this is more readable. The code shows multiple tables for the data entry portion but in the end, I will be using tblShopOperations and tblusers plus any new table that is created. Thanks for the help. Thanks for the procedures, they were a big help.
RE: have normalization questions and need help
I would use something like:
Just because Access lets you use them, it does not mean you should. Just my opinion.
Have fun.
---- Andy
A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.
RE: have normalization questions and need help
tblOperations ======================== oprOprID autonumber primary key oprTitle values such as "meters tested", "meters returned", "meters processed",... oprStatus numeric field with 0 for inactive oprDescr Description/comment field tblEmployees ======================== empEmpID primary key that never ever changes empFirstName First Name empLastName Last Name tblEmpOperDetail (junction table of each operation performed by each employee) ======================= eodEODID autonumber primary key eodEmpID link to tblEmployees.empEmpID eodOprID link to tblOperations.oprOprID eodValue probably a count or measure of some sort eodComments Comments eodDateTime date and time the operation is performed by the operator eodCreated Date record is created
This might be an over simplification of your requirements but should provide some direction on how to move data out of your table and column names and put them into data.
Duane
Hook'D on Access
MS Access MVP
RE: have normalization questions and need help
RE: have normalization questions and need help
http://r937.com/relational.html
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: have normalization questions and need help
tblEmployees ======================== empEmpID primary key that never ever changes empFirstName First Name empLastName Last Name empFullName First and Last name
oror
Stop. You ar doing it wrong.
Have fun.
---- Andy
A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.