INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

RE: have normalization questions and need help

Please use TGML to format your posts. It's the appropriate effort if you want to get some assistance. Text like "login date operator Sheets MetersScrapped FH Repaired" are far from readable. The Pre tag can help with lining up columns.

Duane
Hook'D on Access
MS Access MVP

RE: have normalization questions and need help

I assume you meant the records looked like this

CODE -->

login date operator Sheets MetersScrapped FH Repaired 
1/10/13     wdorey    3
1/10/13     wdorey             5
1/10/13     wdorey                          2 

if so it should look be a single record

CODE

1/10/13   wdorey       3           5           2 

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

tableName
  FieldName1 (primary key)
  FieldName2 (numeric, boolean, date, text,...)
  ... 


Quote:

1. For the drop downs, since the values most likely will not change is a value list still the way to go
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.

Quote:


2. do I create one table that has all of the values for the tabs OR a table for each tab?
The tabs do not dictate your table structure, it is the organization of data to fit your buisness model. The answer is probably neither.

Quote:

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)
Not sure what that means.

Quote:


4. If the operators are late, would you put that info in a separate table?
No you would tag it within the table in a field

Quote:


5. Since I have to add in a logout date, should there be an automatic login/logout per tab?
If there is no back dating, the the log in can be time stamped based on physically accessing your form


Quote:


6. Right now each tab is bound to a field, should it be unbound?

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

(OP)
Hi. Here is the output from the procedures.

CODE

Table output

tblUsers
   UserId   dbLong  PrimaryKey  Indexed
   WorkGroupID   dbLong  Indexed
   GranterID   dbLong  Indexed
   UserAbbrv   dbText
   UserName   dbText
   UserPwd   dbText
   FullName   dbText
   WSSCID   dbText  Indexed
   Company   dbText
   PhoneNumber   dbText
   UserType   dbText
   DateCreated   dbDate
   Email   dbText
   Title   dbText
   LastName   dbText
   FirstName   dbText

tblShopOperations
   Login Date   dbDate
   Employee Id   dbInteger  Indexed
   Employee Name   dbText
   Meter Test Reasons   dbText  Indexed
   Mtr Size   dbText
   Manufacturer   dbText
   Bronze/Plastic   dbText
   Returned Mtrs   dbText
   Truck Inspection   dbText
   FH Mtr Repaired   dbText
   Mtr Processing   dbText
   Mtr Scrap   dbText
   Mtr Crates Cut   dbText
   Mtrs in Stock   dbText
   Data Entry/CSIS   dbText
   Equipment Repair   dbText
   Test Machine   dbText
   Machine #   dbText
   PM   dbText
   Repairs   dbText
   Warehouse   dbText
   In Training   dbMemo
   Other Duties Assigned   dbMemo
   Out of Office   dbText
   Notes   dbText
   Mtrs Tested   dbLong  Indexed
   Mtrs Issued   dbLong  Indexed
   Mtrs Returned   dbLong  Indexed
   Mtrs Processed   dbLong  Indexed
   Mtrs Scrap   dbLong
   Mtr Crate Cuts   dbLong
   Sheets   dbLong
   In Stock   dbLong
   Monthly Totals   dbLong
   fldCreatedBy   dbText
   fldModifyDate   dbDate
   fldModifiedBy   dbText
   Issued Mtrs   dbText
   Warranty   dbText
   Crate #   dbLong
   # Mtrs Passed   dbLong
   % Passed   dbLong
   # FH Repaired   dbLong

WilliamDorey
   LoginDate   dbDate
   EmployeeId   dbInteger  Indexed
   EmployeeName   dbText
   MeterTestReasons   dbText  Indexed
   MtrSize   dbText
   Manufacturer   dbText
   Bronze/Plastic   dbText
   ReturnedMtrs   dbText
   TruckInspection   dbText
   FHMtrRepaired   dbText
   MtrProcessing   dbText
   MtrScrap   dbText
   MtrCratesCut   dbText
   MtrsinStock   dbText
   DataEntry/CSIS   dbText
   EquipmentRepair   dbText
   TestMachine   dbText
   Machine#   dbText
   PM   dbText
   Repairs   dbText
   Warehouse   dbText
   InTraining   dbMemo
   OtherDutiesAssigned   dbMemo
   OutofOffice   dbText
   Notes   dbText
   MtrsTested   dbLong  Indexed
   MtrsIssued   dbLong  Indexed
   MtrsReturned   dbLong  Indexed
   MtrsProcessed   dbLong  Indexed
   MtrsScrap   dbLong
   MtrCrateCuts   dbLong
   Sheets   dbLong
   InStock   dbLong
   MonthlyTotals   dbLong
   fldCreatedBy   dbText
   fldModifyDate   dbDate
   fldModifiedBy   dbText
   IssuedMtrs   dbText
   Warranty   dbText
   Crate#   dbLong
   #MtrsPassed   dbLong
   %Passed   dbLong
   #FHRepaired   dbLong 

CODE

Relations output
Name: MSysNavPaneGroupCategoriesMSysNavPaneGroups
  Table: MSysNavPaneGroupCategories
  Foreign Table: MSysNavPaneGroups
  PK: Id   FK:GroupCategoryID

Name: MSysNavPaneGroupsMSysNavPaneGroupToObjects
  Table: MSysNavPaneGroups
  Foreign Table: MSysNavPaneGroupToObjects
  PK: Id   FK:GroupID 

CODE

queries output
   DateFrom   dbBinary
   DateTo   dbBinary
SELECT tblShopOperations.LoginDate, tblShopOperations.EmployeeId, tblShopOperations.EmployeeName, tblShopOperations.MeterTestReasons, tblShopOperations.MtrSize, tblShopOperations.Manufacturer, tblShopOperations.[Bronze/Plastic], tblShopOperations.ReturnedMtrs, tblShopOperations.TruckInspection, tblShopOperations.FHMtrRepaired, tblShopOperations.MtrProcessing, tblShopOperations.MtrScrap, tblShopOperations.MtrCratesCut, tblShopOperations.MtrsinStock, tblShopOperations.[DataEntry/CSIS], tblShopOperations.EquipmentRepair, tblShopOperations.TestMachine, tblShopOperations.[Machine#], tblShopOperations.PM, tblShopOperations.Repairs, tblShopOperations.Warehouse, tblShopOperations.InTraining, tblShopOperations.OtherDutiesAssigned, tblShopOperations.OutofOffice, tblShopOperations.Notes, tblShopOperations.MtrsTested, tblShopOperations.MtrsIssued, tblShopOperations.MtrsReturned, tblShopOperations.MtrsProcessed, tblShopOperations.MtrsScrap, tblShopOperations.MtrCrateCuts, tblShopOperations.Sheets, tblShopOperations.InSto
ck, tblShopOperations.MonthlyTotals, tblShopOperations.fldCreatedBy, tblShopOperations.fldModifyDate, tblShopOperations.fldModifiedBy, tblShopOperations.IssuedMtrs, tblShopOperations.Warranty, tblShopOperations.[Crate#], tblShopOperations.[#MtrsPassed], tblShopOperations.[%Passed], tblShopOperations.[#FHRepaired], tu_users.LastName, tu_users.FirstName, [Forms]![frmMainTab]![frmReports].[Form]![txtFromDate] AS DateFrom, [Forms]![frmMainTab]![frmReports].[Form]![txttoDate] AS DateTo
FROM tblusers INNER JOIN tblShopOperations ON tblusers.UserAbbrv = tblShopOperations.EmployeeName
WHERE (((tblShopOperations.LoginDate) Between Forms!frmMainTab.form!frmReports!txtfromdate And Forms!frmMainTab.form!frmReports!txttodate) And ((tblShopOperations.EmployeeName)=Forms!frmMainTab.form!frmReports!txtcboselected));


qryMonthlyStats
   LoginDate   dbDate
   EmployeeId   dbInteger
   EmployeeName   dbText
   Sum Of Mtrs Tested   dbDouble
   Sum Of# Mtrs Passed   dbDouble
   Sum Of Mtrs Issued   dbDouble
   Sum Of Mtrs Returned   dbDouble
   Sum Of Mtrs Processed   dbDouble
   Sum Of Mtrs Scrap   dbDouble
   Sum Of Mtr Crate Cuts   dbDouble
   Sum Of Sheets   dbDouble
   Sum Of In Stock   dbDouble
   sum of%Passed   dbDouble
   LastName   dbText
   FirstName   dbText
SELECT DISTINCTROW tblShopOperations.LoginDate, tblShopOperations.EmployeeId, tblShopOperations.EmployeeName, Sum(tblShopOperations.MtrsTested) AS [Sum Of Mtrs Tested], Sum(tblShopOperations.[#MtrsPassed]) AS [Sum Of# Mtrs Passed], Sum(tblShopOperations.MtrsIssued) AS [Sum Of Mtrs Issued], Sum(tblShopOperations.MtrsReturned) AS [Sum Of Mtrs Returned], Sum(tblShopOperations.MtrsProcessed) AS [Sum Of Mtrs Processed], Sum(tblShopOperations.MtrsScrap) AS [Sum Of Mtrs Scrap], Sum(tblShopOperations.MtrCrateCuts) AS [Sum Of Mtr Crate Cuts], Sum(tblShopOperations.Sheets) AS [Sum Of Sheets], Sum(tblShopOperations.InStock) AS [Sum Of In Stock], Avg(tblShopOperations.[%Passed]) AS [sum of%Passed], tu_users.LastName, tu_users.FirstName
FROM tblusers INNER JOIN tblShopOperations ON tblusers.UserAbbrv = tblShopOperations.EmployeeName
GROUP BY tblShopOperations.LoginDate, tblShopOperations.EmployeeId, tblShopOperations.EmployeeName, tblusers.LastName, tblusers.FirstName
HAVING (((tblShopOperations.LoginDate) Between [Forms]![frmMainTab]![frmReports].[Form]![txtFromDate] And [Forms]![frmMainTab]![frmReports].[Form]![txttoDate]));


qryTotalsByDate
   LoginDate   dbDate
   Employee Name   dbBinary
   MtrsTested   dbLong
   MeterTestReasons   dbText
   FHMtrRepaired   dbText
   MtrSize   dbText
   Manufacturer   dbText
   Bronze/Plastic   dbText
   MtrsIssued   dbLong
   ReturnedMtrs   dbText
   MtrsReturned   dbLong
   MtrsProcessed   dbLong
   MtrScrap   dbText
   MtrCratesCut   dbText
   Sheets   dbLong
   TruckInspection   dbText
   DataEntry/CSIS   dbText
   EquipmentRepair   dbText
   TestMachine   dbText
   %Passed   dbLong
SELECT tblShopOperations.LoginDate, tblShopOperations.[Employee Name], tblShopOperations.MtrsTested, tblShopOperations.MeterTestReasons, tblShopOperations.FHMtrRepaired, tblShopOperations.MtrSize, tblShopOperations.Manufacturer, tblShopOperations.[Bronze/Plastic], tblShopOperations.MtrsIssued, tblShopOperations.ReturnedMtrs, tblShopOperations.MtrsReturned, tblShopOperations.MtrsProcessed, tblShopOperations.MtrScrap, tblShopOperations.MtrCratesCut, tblShopOperations.Sheets, tblShopOperations.TruckInspection, tblShopOperations.[DataEntry/CSIS], tblShopOperations.EquipmentRepair, tblShopOperations.TestMachine, tblShopOperations.[%Passed]
FROM [David Redzensky] INNER JOIN tblShopOperations ON [David Redzensky].[Employee Name] = tblShopOperations.EmployeeName
WHERE (((tblShopOperations.LoginDate) Between [type beginning date] And [type ending date]));


qrywdorey
   LoginDate   dbDate
   EmployeeName   dbText
   IssuedMtrs   dbText
   MtrSize   dbText
   Manufacturer   dbText
   MtrsIssued   dbLong
SELECT tblShopOperations.LoginDate, tblShopOperations.EmployeeName, tblShopOperations.IssuedMtrs, tblShopOperations.MtrSize, tblShopOperations.Manufacturer, tblShopOperations.MtrsIssued
FROM tblShopOperations
WHERE (((tblShopOperations.IssuedMtrs) Is Not Null)) OR (((tblShopOperations.MtrSize) Is Not Null));


Rhoshon Lockhart Query
   Login Date   dbDate
   Sum Of Mtrs Tested   dbDouble
   Sum Of # Mtrs Passed   dbDouble
   Sum Of % Passed   dbDouble
   Sum Of Mtrs Issued   dbDouble
   Sum Of Mtrs Returned   dbDouble
   Sum Of Mtrs Processed   dbDouble
   Sum Of Mtrs Scrap   dbDouble
   Sum Of Mtr Crate Cuts   dbDouble
   Sum Of Sheets   dbDouble
   Sum Of In Stock   dbDouble
SELECT DISTINCTROW Rhoshon.[Login Date], Sum(Rhoshon.[Mtrs Tested]) AS [Sum Of Mtrs Tested], Sum(Rhoshon.[# Mtrs Passed]) AS [Sum Of # Mtrs Passed], Sum(Rhoshon.[% Passed]) AS [Sum Of % Passed], Sum(Rhoshon.[Mtrs Issued]) AS [Sum Of Mtrs Issued], Sum(Rhoshon.[Mtrs Returned]) AS [Sum Of Mtrs Returned], Sum(Rhoshon.[Mtrs Processed]) AS [Sum Of Mtrs Processed], Sum(Rhoshon.[Mtrs Scrap]) AS [Sum Of Mtrs Scrap], Sum(Rhoshon.[Mtr Crate Cuts]) AS [Sum Of Mtr Crate Cuts], Sum(Rhoshon.Sheets) AS [Sum Of Sheets], Sum(Rhoshon.[In Stock]) AS [Sum Of In Stock]
FROM Rhoshon
GROUP BY Rhoshon.[Login Date], Format$([Rhoshon Lockhart].[Login Date],'Long Date')
HAVING (((Rhoshon.[Login Date]) Between [type from date] And [type to date]));


rLockhart totals by date
   Login Date   dbDate
   Mtrs Tested   dbLong
   Meter Test Reasons   dbText
   FH Mtr Repaired   dbText
   Mtr Size   dbText
   Manufacturer   dbText
   Bronze/Plastic   dbText
   Mtrs Issued   dbLong
   Returned Mtrs   dbText
   Mtrs Returned   dbLong
   Mtrs Processed   dbLong
   Mtrs Scrap   dbLong
   Mtr Crate Cuts   dbLong
   Sheets   dbLong
   Truck Inspection   dbText
   Data Entry/CSIS   dbText
   Equipment Repair   dbText
   Test Machine   dbBoolean
   % Passed   dbInteger
SELECT [Tammy Smith].[Login Date], [Tammy Smith].[Mtrs Tested], [Tammy Smith].[Meter Test Reasons], [Tammy Smith].[FH Mtr Repaired], [Tammy Smith].[Mtr Size], [Tammy Smith].Manufacturer, [Tammy Smith].[Bronze/Plastic], [Tammy Smith].[Mtrs Issued], [Tammy Smith].[Returned Mtrs], [Tammy Smith].[Mtrs Returned], [Tammy Smith].[Mtrs Processed], [Tammy Smith].[Mtrs Scrap], [Tammy Smith].[Mtr Crate Cuts], [Tammy Smith].Sheets, [Tammy Smith].[Truck Inspection], [Tammy Smith].[Data Entry/CSIS], [Tammy Smith].[Equipment Repair], [Tammy Smith].[Test Machine], [Tammy Smith].[% Passed]
FROM [Tammy Smith]
WHERE ((([Tammy Smith].[Login Date]) Between [type from date] And [type to date])); 

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

At the first glance I would advise to avoid spaces and special characters in your fields' names:
   Crate #         dbLong
   # Mtrs Passed   dbLong
   % Passed        dbLong
   # FH Repaired   dbLong
 
I would use something like:
   Crate_No         dbLong
   No_Mtrs_Passed   dbLong
   Pcnt_Passed      dbLong
   No_FH_Repaired   dbLong
 
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

True normalization would take operations out of field names and put them into your data. IMO, there should be a table of unique operations so you can add or remove (inactivate) them at any time.

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

(OP)
Thank you everyone. That's exactly what I need to get going in the correct direction

RE: have normalization questions and need help

Also if you find yourself doing something like this:

tblEmployees
========================
empEmpID      primary key that never ever changes
empFirstName  First Name
empLastName   Last Name
empFullName   First and Last name
 
or

tblSomeTable
========================
SomeField1    Whatever
SomeField2    Whatever
SomeField3    Whatever
SomeField4    Whatever
 
or

tblMyTable
========================
Price    Price
Qnty     How Many
Tptal    Price * Qnty
 

Stop. You ar doing it wrong. sad

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close