0

Objective:

- electronic form which displays 45 inspection points (rarely changes)
- 3 additional columns
  - Rating (0, 1, 2)
  - CorrectiveAction (Immediate, SOP, WO)
  - Notes
- Maintain inspection history
  - inspection date, production line inspected, production shift-crew inspected

Tables:

- Employee (empID (auto#), FName, LName, Shift, Line)
- Audit Facts (ID#, textdescrip)   tried with and without autonumber
- Audit Details (auditID (auto#), auditdate, rating, action, notes)  *(rating/action combobox)

A subform in a form has the 45 check points all tied to the Audit number. However, I cannot get the audit results to record, store and move into history.

every attempt i've made produces an audit number PER each 45 checkpts, as opposed to 

 Audit Date
   AuditID(auto#)
      AuditFact (list of 45 chckpts)

results

        Audit1, 10/02/2010 ->
           *AuditPt     Rate     Action*        
                IF1          0      WO
                IF2          2      SOP
                ... 
                IF45         1      Immediate

        Audit2, 12/15/2010 ->
           *AuditPt     Rate     Action*        
                IF1          1      WO
                IF2          0      SOP
                ... 
                IF45         0      Immediate
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
Lisa
  • 1

1 Answers1

0

Get your tables right & your forms will follow.

The original question isn't entirely clear, but I think I got these points:

  1. An "Audit" is the same as an "Inspection"
  2. The collection of 45 "inspection points" constitutes an Audit (not each one individually).
  3. "Ratings" and "Actions" apply to each inspeciton point.

So your tables want to be more like this:

Audit
  AuditId (can be autonumber, or not...)
  AuditDate

AuditItem
  AuditItemId (can be autonumber, or not...)
  AuditId (FK from Audit)
  InspPoint (e.g., the "IF1" from the "result" sample, or--better--an FK to it another table)
  Rating
  Action (Or--better--an FK to it another table)

Table "Employee" seems entirely irrelevant to this strucure.

Note that each Insection Point has its own row in AuditItem, for each Audit, and that those rows also carry the AuditID from Audit, which lets you pull them all together to make you output.

Hopefully this will make sense--if it doesn't, I recommend reviewing fundamental relational database design.

RolandTumble
  • 4,633
  • 3
  • 32
  • 37
  • thank you-tried tbls for each Rating, Action, Employee, InspectionPts...but no success in history retention. Employees are relevant mainly for Shift and ProductionLine (inspections performed daily for two shifts and 4 production lines = 8 inspections/day) – Lisa Dec 15 '10 at 23:56
  • So, Employee Ids may need to be added to the Audit table--which *has history built in*. Check the link. – RolandTumble Dec 16 '10 at 00:39
  • after all these yrs of Accessdesign and this 'simple' dbase has been a battle for near a month....and still, the table 'trees' do not expand out. – Lisa Dec 16 '10 at 23:32