Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

have normalization questions and need help 2

Status
Not open for further replies.
Lhuffst

Lhuffst

Programmer
Jun 23, 2003
503
US
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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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 can do the documentation for you
Or at least write it like
Code: 
tableName
  FieldName1 (primary key)
  FieldName2 (numeric, boolean, date, text,...)
  ...


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

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

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)
Click to expand...
Not sure what that means.
4. If the operators are late, would you put that info in a separate table?
Click to expand...
No you would tag it within the table in a field

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


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

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.

 
Upvote 0 Downvote
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]));

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

[pre]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[/pre]

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
 
Upvote 0 Downvote
Thank you everyone. That's exactly what I need to get going in the correct direction
 
Upvote 0 Downvote
Also if you find yourself doing something like this:

[pre]
tblEmployees
========================
empEmpID primary key that never ever changes
empFirstName First Name
empLastName Last Name[red]
empFullName First and Last name[/red]
[/pre]
or

[pre]
tblSomeTable
========================
SomeField1 Whatever
SomeField2 Whatever
SomeField3 Whatever
SomeField4 Whatever
[/pre]
or

[pre]
tblMyTable
========================
Price Price
Qnty How Many
Tptal Price * Qnty
[/pre]

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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

MrStohler
  • Locked
  • Question
Beginner Q: How to separate data from Excel into related tables and maintain relationship? 1
Replies
5
Views
217
SkipVought
S
oxicottin
  • Locked
  • Question
Creating tables and relationships for new database
Replies
2
Views
439
MajP
MajP
Kirk Thomas
  • Locked
  • Question
Table Relationships newbie needs help
Replies
8
Views
157
johnherman
johnherman
MattGreer
  • Locked
  • Question
Is it "bad" to have a Lookup table/query in a table? 2
Replies
4
Views
111
MattGreer
MattGreer
Magnolia1
  • Locked
  • Question
Hello, I need help with my query. I
Replies
2
Views
79
Magnolia1
Magnolia1

Part and Inventory Search

Sponsor

Back
Top