Tracking multiple man #'s with one work order #

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I designed a database to track quality control inspections for a vehicle
maintenance shop and I have a problem. Several mechanics may put time on one
work order. How can I track several man numbers to one work order number?
Here is how I have it set up:

shopcodetbl:
[shop code] <-- primary key
[shop name]

Employee Man No tbl:
[man no] <-- primary key
[last name]
[first name]
[position]
[section]
[shop code]

service records tbl:
[id] <-- primary key
[reg no]
[w/o no]
[date inspected]
[w/o closed date]
[inspected by]
[pass] check box
[fail] check box
[man no]
[LOF] check box
[A/I] check box
[repack/brake check] check box
[visual insp] check box
[remarks]

I have one to one relationships between shop code [shop codes tbl:] and shop
code [employee man no tbl:] and between man no [employee man no tbl:] and man
no [service records tbl:]

Currently, I duplicate the inspection record to add multiple man numbers. It
work, but does not reflect the correct total number of inspections. Any help
on this is greatly appreciated. I am willing to email the database if needed
because there is alot going on that is just hard to type out. Thanks in
advance

Aloha,
Ron A.
 
Ron A. said:
I designed a database to track quality control inspections for a vehicle
maintenance shop and I have a problem. Several mechanics may put time on one
work order. How can I track several man numbers to one work order
Click to expand...
number?

It's going to be interesting, especially since you don't have a
WorkOrder table.


Here is how I have it set up:

shopcodetbl:
[shop code] <-- primary key
[shop name]

Employee Man No tbl:
[man no] <-- primary key
[last name]
[first name]
[position]
[section]
[shop code]

service records tbl:
[id] <-- primary key
[reg no]
[w/o no]
[date inspected]
[w/o closed date]
[inspected by]
[pass] check box
[fail] check box
[man no]
[LOF] check box
[A/I] check box
[repack/brake check] check box
[visual insp] check box
[remarks]

I have one to one relationships
Click to expand...

I see.

between shop code [shop codes tbl:] and shop
code [employee man no tbl:]
Click to expand...

So, only one man can work at one shop?

and between man no [employee man no tbl:] and man
no [service records tbl:]
Click to expand...

And so only one man can have a particular service record? What
happens when more than one mechanic works on vehicle (or does that
never happen)?

Currently, I duplicate the inspection record to add multiple man numbers. It
work, but does not reflect the correct total number of inspections.
Click to expand...

Ron A.,

The "inspection record"? There is no inspection table. What does
this refer to?

Is the [service records tbl:] the "inspection table". If so, it
should probably be named Inspections.

Aloha,
Ron A.
Click to expand...


Here is what I would do. I'll be limiting the number of columns in
the following tables.

Tables:

CREATE TABLE Shops
(ShopID AUTOINCREMENT
,ShopName TEXT(36)
,CONSTRAINT pk_Shops
PRIMARY KEY (ShopID)
)

CREATE TABLE Employees
(EmployeeID AUTOINCREMENT
,ShopID INTEGER NOT NULL
,LastName TEXT(36)
,FirstName TEXT(36)
,Position TEXT(36)
,Section TEXT(36)
,CONSTRAINT pk_Employees
PRIMARY KEY (EmployeeID)
,CONSTRAINT fk_Employees_Shops
FOREIGN KEY (ShopID)
REFERENCES Shops (ShopID)
)

CREATE TABLE Clients
(ClientID AUTOINCREMENT
,LastName TEXT(36)
,FirstName TEXT(36)
,CONSTRAINT pk_Clients
PRIMARY KEY (ClientID)
)

CREATE TABLE WorkOrders
(WorkOrderID AUTOINCREMENT
,ClientID INTEGER NOT NULL
,OpenDate DATETIME NOT NULL
,CloseDate DATETIME
,CONSTRAINT pk_WorkOrders
PRIMARY KEY (WorkOrderID)
,CONSTRAINT fk_WorkOrders_Clients
FOREIGN KEY (ClientID)
REFERENCES Clients (ClientID)
)


However, I am interested in knowing why the ServiceRecords table
records information about work orders (like [w/o close date]). That
should be in the WorkOrder table.

Two columns ([pass] and [fail]) are not required to indicate
inspection success. Only one is. If it is checked (or true), then
the inspection was a success. If it isn't, it wasn't. If
InspectionEnd is Null, the inspection is not over and the Pass column
wouldn't be checked for pass/failure at all.

CREATE TABLE Inspections
(InspectionID AUTOINCREMENT
,WorkOrderID INTEGER NOT NULL
,InspectionStart DATETIME NOT NULL
,InspectionEnd DATETIME
,Pass BIT
,CONSTRAINT pk_Inspections
PRIMARY KEY (InspectionID)
,CONSTRAINT fk_Inspections_WorkOrders
FOREIGN KEY (WorkOrderID)
REFERENCES WorkOrders (WorkOrderID)
)


CREATE TABLE InspectionEmployees
(InspectionID AUTOINCREMENT
,EmployeeID INTEGER NOT NULL
,CONSTRAINT pk_InspectionEmployees
PRIMARY KEY (InspectionID, EmployeeID)
,CONSTRAINT fk_InspectionEmployees_Inspections
FOREIGN KEY (InspectionID)
REFERENCES Inspections (InspectionID)
,CONSTRAINT fk_InspectionEmployees_Employees
FOREIGN KEY (EmployeeID)
REFERENCES Employees (EmployeeID)
)

This last table is what does it for you. You may have many employees
assigned to each inspection. (Or, at least, that is what I thought
you wanted.)

Also:

Each client may have many work orders (true in most auto mechanic
enterprises).

Each work order may have many inspections (which may or may not be
true, but might actually be desirable on some occasions).

Each shop may have many employees (which may not be true, but I
suspect is likely).

---------------------------------------

Create a blank MS Access database. You can copy and paste these DDL
SQL queries each into an MS Access Query, executing each one in order
to create the tables and the relationships.

Then go to the MS Access menu and select Tools>Relationships. When
the window opens, if all the tables aren't already displayed,
right-click and select "Show All". Study the relationships that are
displayed.


Sincerely,

Chris O.
 
Chris,

Thanks for the fast reply and your suggestion looks really close to my
needs. I will try to give you some more info on my current database. I am an
Inspector for a company that repairs their own equipment. Each vehicle is
assigned a registration number (example... 02C00055). When a vehicle comes
in for maintenance, a work order is opened and sent to a specific shop with
all the jobs that need to be done. I do outgoing quality control inspections
on the work that was done by the mechanics.

Currently, I use the database to track the QC inspections with Reg No, W/O
No, insp date, who inspected, pass or fail, reject items and who worked on
it. I also created some date queries and reports so I can track individual
performance by showing the number of inspection passed or failed by each
mechanic and fail rate.

I also used check boxes for showing when the vehicle comes in for scheduled
maintenance if it was just a visual, wheel bearing repack/brake inspection
and so on.

It way error in my first post on the relationships... they are one to many.
I hope this helps. I really do appreciate your time and assistance.
--
Aloha,
Ron A.


Chris2 said:
Ron A. said:
I designed a database to track quality control inspections for a vehicle
maintenance shop and I have a problem. Several mechanics may put time on one
work order. How can I track several man numbers to one work order
Click to expand...
number?

It's going to be interesting, especially since you don't have a
WorkOrder table.


Here is how I have it set up:

shopcodetbl:
[shop code] <-- primary key
[shop name]

Employee Man No tbl:
[man no] <-- primary key
[last name]
[first name]
[position]
[section]
[shop code]

service records tbl:
[id] <-- primary key
[reg no]
[w/o no]
[date inspected]
[w/o closed date]
[inspected by]
[pass] check box
[fail] check box
[man no]
[LOF] check box
[A/I] check box
[repack/brake check] check box
[visual insp] check box
[remarks]

I have one to one relationships
Click to expand...

I see.

between shop code [shop codes tbl:] and shop
code [employee man no tbl:]
Click to expand...

So, only one man can work at one shop?

and between man no [employee man no tbl:] and man
no [service records tbl:]
Click to expand...

And so only one man can have a particular service record? What
happens when more than one mechanic works on vehicle (or does that
never happen)?

Currently, I duplicate the inspection record to add multiple man numbers. It
work, but does not reflect the correct total number of inspections.
Click to expand...

Ron A.,

The "inspection record"? There is no inspection table. What does
this refer to?

Is the [service records tbl:] the "inspection table". If so, it
should probably be named Inspections.

Aloha,
Ron A.
Click to expand...


Here is what I would do. I'll be limiting the number of columns in
the following tables.

Tables:

CREATE TABLE Shops
(ShopID AUTOINCREMENT
,ShopName TEXT(36)
,CONSTRAINT pk_Shops
PRIMARY KEY (ShopID)
)

CREATE TABLE Employees
(EmployeeID AUTOINCREMENT
,ShopID INTEGER NOT NULL
,LastName TEXT(36)
,FirstName TEXT(36)
,Position TEXT(36)
,Section TEXT(36)
,CONSTRAINT pk_Employees
PRIMARY KEY (EmployeeID)
,CONSTRAINT fk_Employees_Shops
FOREIGN KEY (ShopID)
REFERENCES Shops (ShopID)
)

CREATE TABLE Clients
(ClientID AUTOINCREMENT
,LastName TEXT(36)
,FirstName TEXT(36)
,CONSTRAINT pk_Clients
PRIMARY KEY (ClientID)
)

CREATE TABLE WorkOrders
(WorkOrderID AUTOINCREMENT
,ClientID INTEGER NOT NULL
,OpenDate DATETIME NOT NULL
,CloseDate DATETIME
,CONSTRAINT pk_WorkOrders
PRIMARY KEY (WorkOrderID)
,CONSTRAINT fk_WorkOrders_Clients
FOREIGN KEY (ClientID)
REFERENCES Clients (ClientID)
)


However, I am interested in knowing why the ServiceRecords table
records information about work orders (like [w/o close date]). That
should be in the WorkOrder table.

Two columns ([pass] and [fail]) are not required to indicate
inspection success. Only one is. If it is checked (or true), then
the inspection was a success. If it isn't, it wasn't. If
InspectionEnd is Null, the inspection is not over and the Pass column
wouldn't be checked for pass/failure at all.

CREATE TABLE Inspections
(InspectionID AUTOINCREMENT
,WorkOrderID INTEGER NOT NULL
,InspectionStart DATETIME NOT NULL
,InspectionEnd DATETIME
,Pass BIT
,CONSTRAINT pk_Inspections
PRIMARY KEY (InspectionID)
,CONSTRAINT fk_Inspections_WorkOrders
FOREIGN KEY (WorkOrderID)
REFERENCES WorkOrders (WorkOrderID)
)


CREATE TABLE InspectionEmployees
(InspectionID AUTOINCREMENT
,EmployeeID INTEGER NOT NULL
,CONSTRAINT pk_InspectionEmployees
PRIMARY KEY (InspectionID, EmployeeID)
,CONSTRAINT fk_InspectionEmployees_Inspections
FOREIGN KEY (InspectionID)
REFERENCES Inspections (InspectionID)
,CONSTRAINT fk_InspectionEmployees_Employees
FOREIGN KEY (EmployeeID)
REFERENCES Employees (EmployeeID)
)

This last table is what does it for you. You may have many employees
assigned to each inspection. (Or, at least, that is what I thought
you wanted.)

Also:

Each client may have many work orders (true in most auto mechanic
enterprises).

Each work order may have many inspections (which may or may not be
true, but might actually be desirable on some occasions).

Each shop may have many employees (which may not be true, but I
suspect is likely).

---------------------------------------

Create a blank MS Access database. You can copy and paste these DDL
SQL queries each into an MS Access Query, executing each one in order
to create the tables and the relationships.

Then go to the MS Access menu and select Tools>Relationships. When
the window opens, if all the tables aren't already displayed,
right-click and select "Show All". Study the relationships that are
displayed.


Sincerely,

Chris O.
Click to expand...
 
Ron A. said:
Chris,

Thanks for the fast reply and your suggestion looks really close to my
needs.
Click to expand...

Ron A.,

You're welcome! :D

I will try to give you some more info on my current database. I am an
Inspector for a company that repairs their own equipment. Each vehicle is
assigned a registration number (example... 02C00055). When a vehicle comes
in for maintenance, a work order is opened and sent to a specific shop with
all the jobs that need to be done. I do outgoing quality control inspections
on the work that was done by the mechanics.

Currently, I use the database to track the QC inspections with Reg No, W/O
No, insp date, who inspected, pass or fail, reject items and who worked on
it. I also created some date queries and reports so I can track individual
performance by showing the number of inspection passed or failed by each
mechanic and fail rate.

I also used check boxes for showing when the vehicle comes in for scheduled
maintenance if it was just a visual, wheel bearing repack/brake inspection
and so on.

It way error in my first post on the relationships... they are one to many.
I hope this helps. I really do appreciate your time and assistance.
Click to expand...

Ok, here is my redesign based on your latest description.


Tables:

CREATE TABLE Shops
(ShopID AUTOINCREMENT
,ShopName TEXT(36)
,CONSTRAINT pk_Shops
PRIMARY KEY (ShopID)
)

CREATE TABLE Employees
(EmployeeID AUTOINCREMENT
,ShopID INTEGER NOT NULL
,LastName TEXT(36)
,FirstName TEXT(36)
,Position TEXT(36)
,Section TEXT(36)
,CONSTRAINT pk_Employees
PRIMARY KEY (EmployeeID)
,CONSTRAINT fk_Employees_Shops
FOREIGN KEY (ShopID)
REFERENCES Shops (ShopID)
)

CREATE TABLE Vehicles
(VIN TEXT(17)
,Make TEXT(255)
,Model TEXT(255)
,ManufactureYear CHAR(4)
,[reg no] TEXT(255)
,CONSTRAINT pk_Vehicles
PRIMARY KEY (VIN)
)

CREATE TABLE WorkOrders
(WorkOrderID AUTOINCREMENT
,VIN TEXT(17) NOT NULL
,OpenDate DATETIME NOT NULL
,CloseDate DATETIME
,CONSTRAINT pk_WorkOrders
PRIMARY KEY (WorkOrderID)
,CONSTRAINT fk_WorkOrders_Clients
FOREIGN KEY (VIN)
REFERENCES Vehicles (VIN)
)

CREATE TABLE InspectionTests
(InspectionTestID AUTOINCREMENT
,TestName TEXT(255) NOT NULL
,TestDescription TEXT(255)
,CONSTRAINT pk_InspectionTests
PRIMARY KEY (InspectionTestID)
)

CREATE TABLE InspectionPlans
(InspectionPlanID AUTOINCREMENT
,InspectionName TEXT(255) NOT NULL
,BeginUse DATETIME NOT NULL
,EndUse DATETIME
,CONSTRAINT pk_InspectionPlans
PRIMARY KEY (InspectionPlanID)
)

CREATE TABLE InspectionPlanDetails
(InspectionPlanID INTEGER NOT NULL
,InspectionTestID INTEGER NOT NULL
,BeginUse DATETIME NOT NULL
,EndUse DATETIME
,CONSTRAINT pk_InspectionPlanDetails
PRIMARY KEY (InspectionPlanID, InspectionTestID)
,CONSTRAINT fk_InspectionPlanDetails_InspectionPlans
FOREIGN KEY (InspectionPlanID)
REFERENCES InspectionPlans (InspectionPlanID)
,CONSTRAINT fk_InspectionPlanDetails_InspectionTests
FOREIGN KEY (InspectionTestID)
REFERENCES InspectionTests (InspectionTestID)
)

CREATE TABLE Inspections
(InspectionID AUTOINCREMENT
,WorkOrderID INTEGER NOT NULL
,InspectionPlanID INTEGER NOT NULL
,InspectionStart DATETIME NOT NULL
,InspectionEnd DATETIME
,CONSTRAINT pk_Inspections
PRIMARY KEY (InspectionID)
,CONSTRAINT fk_Inspections_WorkOrders
FOREIGN KEY (WorkOrderID)
REFERENCES WorkOrders (WorkOrderID)
,CONSTRAINT fk_Inspections_InspectionPlans
FOREIGN KEY (InspectionPlanID)
REFERENCES InspectionPlans (InspectionPlanID)
)

CREATE TABLE InspectionDetails
(InspectionID INTEGER NOT NULL
,InspectionTestID INTEGER NOT NULL
,Pass BIT NOT NULL
,Comments TEXT(255)
,CONSTRAINT pk_InspectionDetails
PRIMARY KEY (InspectionID, InspectionTestID)
,CONSTRAINT fk_InspectionDetails_Inspections
FOREIGN KEY (InspectionID)
REFERENCES Inspections (InspectionID)
,CONSTRAINT fk_InspectionDetails_InspectionTests
FOREIGN KEY (InspectionTestID)
REFERENCES InspectionTests (InspectionTestID)
)


CREATE TABLE InspectionEmployees
(InspectionID AUTOINCREMENT
,EmployeeID INTEGER NOT NULL
,CONSTRAINT pk_InspectionEmployees
PRIMARY KEY (InspectionID, EmployeeID)
,CONSTRAINT fk_InspectionEmployees_Inspections
FOREIGN KEY (InspectionID)
REFERENCES Inspections (InspectionID)
,CONSTRAINT fk_InspectionEmployees_Employees
FOREIGN KEY (EmployeeID)
REFERENCES Employees (EmployeeID)
)


Changes:


Clients was changed into Vehicles.

An InspectionTests table was added. It stores information on the
various tests that can be conducted in an Inspection.

An InspectionPlans table was added. There appear to be pre-set things
that are done for any particular inspection. The plans are named
here.

An InspectionPlanDetails table was added. This table collects all the
inspection tests that will be performed in any particular inspection
plan.

Inspections was altered to include the primary key of InspectionPlans.
This way, we know all the tests that will be performed for a certain
inspection.

An InspectionDetails table was added. This table records the results
of each test done in an inspection.

So, four new tables, and two alterations.


With this setup you can ever guaranteeing that each particular
inspection is finished by making sure that an inspection cannot be
finished until its inspection details records match the inspection
plan specified (five tests in the plan, five tests in the details).
Or at least you can issue an error message to a separate "messages"
table that gets printed out later (this is if you enter the computer
data later based on paper records filled out while working . . . I
don't know what your paper/computer/data-entry set up is in your
shop), so that you know that the inspection wasn't properly completed.
But that's a set of questions for the formscoding newsgroup.

Create a new blank database, load and execute the DDL SQL queries,
study the relationships.


Sincerely,

Chris O.
 
Again, thanks a million! The inspections are more detailed that I need. Let
me try and explain how I enter the data.

The mechanic brings me a paper copy of a work order created by a seperate
system. I then go out to the vehicle and ensure each job has been completed
within standards. I then log the inspection using: Vehicle registration #,
work order #, date of inspection, date w/o closed, inspector man #,
pass/fail, mechanics man #, remarks (for detailed info on discrepancies that
failed the vehicle) and for informational purposes (not required) I have
check boxes to show if the vehicle had a LOF, annual, wheel bearing repack,
brake inspection or a visual only if low mileage since last LOF.

Using the info in the tables I have created queries and reports to show fail
rates by man #'s and to print archive listings of inpections. I hope this
explains my process well enough. Again, I can email my current database so
you can see it first hand. Also, If can change my current database to allow
me to add muliple man #'s to one inspection, that would be fine too. Thanks
again for any assistance that can be provided.
--
Aloha,
Ron A.


Chris2 said:
Ron A. said:
Chris,

Thanks for the fast reply and your suggestion looks really close to my
needs.
Click to expand...

Ron A.,

You're welcome! :D

I will try to give you some more info on my current database. I am an
Inspector for a company that repairs their own equipment. Each vehicle is
assigned a registration number (example... 02C00055). When a vehicle comes
in for maintenance, a work order is opened and sent to a specific shop with
all the jobs that need to be done. I do outgoing quality control inspections
on the work that was done by the mechanics.

Currently, I use the database to track the QC inspections with Reg No, W/O
No, insp date, who inspected, pass or fail, reject items and who worked on
it. I also created some date queries and reports so I can track individual
performance by showing the number of inspection passed or failed by each
mechanic and fail rate.

I also used check boxes for showing when the vehicle comes in for scheduled
maintenance if it was just a visual, wheel bearing repack/brake inspection
and so on.

It way error in my first post on the relationships... they are one to many.
I hope this helps. I really do appreciate your time and assistance.
Click to expand...

Ok, here is my redesign based on your latest description.


Tables:

CREATE TABLE Shops
(ShopID AUTOINCREMENT
,ShopName TEXT(36)
,CONSTRAINT pk_Shops
PRIMARY KEY (ShopID)
)

CREATE TABLE Employees
(EmployeeID AUTOINCREMENT
,ShopID INTEGER NOT NULL
,LastName TEXT(36)
,FirstName TEXT(36)
,Position TEXT(36)
,Section TEXT(36)
,CONSTRAINT pk_Employees
PRIMARY KEY (EmployeeID)
,CONSTRAINT fk_Employees_Shops
FOREIGN KEY (ShopID)
REFERENCES Shops (ShopID)
)

CREATE TABLE Vehicles
(VIN TEXT(17)
,Make TEXT(255)
,Model TEXT(255)
,ManufactureYear CHAR(4)
,[reg no] TEXT(255)
,CONSTRAINT pk_Vehicles
PRIMARY KEY (VIN)
)

CREATE TABLE WorkOrders
(WorkOrderID AUTOINCREMENT
,VIN TEXT(17) NOT NULL
,OpenDate DATETIME NOT NULL
,CloseDate DATETIME
,CONSTRAINT pk_WorkOrders
PRIMARY KEY (WorkOrderID)
,CONSTRAINT fk_WorkOrders_Clients
FOREIGN KEY (VIN)
REFERENCES Vehicles (VIN)
)

CREATE TABLE InspectionTests
(InspectionTestID AUTOINCREMENT
,TestName TEXT(255) NOT NULL
,TestDescription TEXT(255)
,CONSTRAINT pk_InspectionTests
PRIMARY KEY (InspectionTestID)
)

CREATE TABLE InspectionPlans
(InspectionPlanID AUTOINCREMENT
,InspectionName TEXT(255) NOT NULL
,BeginUse DATETIME NOT NULL
,EndUse DATETIME
,CONSTRAINT pk_InspectionPlans
PRIMARY KEY (InspectionPlanID)
)

CREATE TABLE InspectionPlanDetails
(InspectionPlanID INTEGER NOT NULL
,InspectionTestID INTEGER NOT NULL
,BeginUse DATETIME NOT NULL
,EndUse DATETIME
,CONSTRAINT pk_InspectionPlanDetails
PRIMARY KEY (InspectionPlanID, InspectionTestID)
,CONSTRAINT fk_InspectionPlanDetails_InspectionPlans
FOREIGN KEY (InspectionPlanID)
REFERENCES InspectionPlans (InspectionPlanID)
,CONSTRAINT fk_InspectionPlanDetails_InspectionTests
FOREIGN KEY (InspectionTestID)
REFERENCES InspectionTests (InspectionTestID)
)

CREATE TABLE Inspections
(InspectionID AUTOINCREMENT
,WorkOrderID INTEGER NOT NULL
,InspectionPlanID INTEGER NOT NULL
,InspectionStart DATETIME NOT NULL
,InspectionEnd DATETIME
,CONSTRAINT pk_Inspections
PRIMARY KEY (InspectionID)
,CONSTRAINT fk_Inspections_WorkOrders
FOREIGN KEY (WorkOrderID)
REFERENCES WorkOrders (WorkOrderID)
,CONSTRAINT fk_Inspections_InspectionPlans
FOREIGN KEY (InspectionPlanID)
REFERENCES InspectionPlans (InspectionPlanID)
)

CREATE TABLE InspectionDetails
(InspectionID INTEGER NOT NULL
,InspectionTestID INTEGER NOT NULL
,Pass BIT NOT NULL
,Comments TEXT(255)
,CONSTRAINT pk_InspectionDetails
PRIMARY KEY (InspectionID, InspectionTestID)
,CONSTRAINT fk_InspectionDetails_Inspections
FOREIGN KEY (InspectionID)
REFERENCES Inspections (InspectionID)
,CONSTRAINT fk_InspectionDetails_InspectionTests
FOREIGN KEY (InspectionTestID)
REFERENCES InspectionTests (InspectionTestID)
)


CREATE TABLE InspectionEmployees
(InspectionID AUTOINCREMENT
,EmployeeID INTEGER NOT NULL
,CONSTRAINT pk_InspectionEmployees
PRIMARY KEY (InspectionID, EmployeeID)
,CONSTRAINT fk_InspectionEmployees_Inspections
FOREIGN KEY (InspectionID)
REFERENCES Inspections (InspectionID)
,CONSTRAINT fk_InspectionEmployees_Employees
FOREIGN KEY (EmployeeID)
REFERENCES Employees (EmployeeID)
)


Changes:


Clients was changed into Vehicles.

An InspectionTests table was added. It stores information on the
various tests that can be conducted in an Inspection.

An InspectionPlans table was added. There appear to be pre-set things
that are done for any particular inspection. The plans are named
here.

An InspectionPlanDetails table was added. This table collects all the
inspection tests that will be performed in any particular inspection
plan.

Inspections was altered to include the primary key of InspectionPlans.
This way, we know all the tests that will be performed for a certain
inspection.

An InspectionDetails table was added. This table records the results
of each test done in an inspection.

So, four new tables, and two alterations.


With this setup you can ever guaranteeing that each particular
inspection is finished by making sure that an inspection cannot be
finished until its inspection details records match the inspection
plan specified (five tests in the plan, five tests in the details).
Or at least you can issue an error message to a separate "messages"
table that gets printed out later (this is if you enter the computer
data later based on paper records filled out while working . . . I
don't know what your paper/computer/data-entry set up is in your
shop), so that you know that the inspection wasn't properly completed.
But that's a set of questions for the formscoding newsgroup.

Create a new blank database, load and execute the DDL SQL queries,
study the relationships.


Sincerely,

Chris O.
Click to expand...
 
Chris,

I totally understand where you are coming from and will be hard at work this
weekend with the valuable info you provided. Thanks again.
--
V/R
Ron A.


Chris2 said:
Ron A. said:
Again, thanks a million! The inspections are more detailed that I need. Let
me try and explain how I enter the data.

The mechanic brings me a paper copy of a work order created by a seperate
system. I then go out to the vehicle and ensure each job has been completed
within standards. I then log the inspection using: Vehicle registration #,
work order #, date of inspection, date w/o closed, inspector man #,
pass/fail, mechanics man #, remarks (for detailed info on discrepancies that
failed the vehicle) and for informational purposes (not required) I have
check boxes to show if the vehicle had a LOF, annual, wheel bearing repack,
brake inspection or a visual only if low mileage since last LOF.
Click to expand...

Ron A.,

Ok, I see now.

Just drop the WorkOrders table and retain the column for that value in
the Inspections table.

Without the FK constraints you'll just have to trust to fate that the
values are entered correctly. I'm not sure how you'll verify this.

The Vehicles table, if no VIN is kept on hand, can go with the
registration number you have as the primary key.


I hope this explains my process well enough.
Click to expand...

One of the tough parts about newsgroup help is that you never really
can explain entire processes well enough in this medium.

The help I was providing was not meant to be instructions on what you
should do in your database. (It could have worked that way, but I
understand that it won't.)

The help I was providing was meant to educate in the most general way
on how to modify your system to do what you need to do.

What you need to do is study the relationships set up in the the
tables I provided.

The design of the table InspectionEmployees, and the relationships
established on it from the Inspections table and the Employees table
are the part that demonstrates how to do what you want to do.

You need to look at that, compare it to your database's design, and
adapt it to your purposes.


First, before trying anything, create a copy of your database (and
preferably store it in an entirely different folder/directory), and
work *only* on the copy.

You have an [Employee Man No tbl:] table and a [service records tbl:]
table.

At a minimum you need to add a table with [man no] and [id] as
columns.

[New Table:]
[id]------\
[man no]---Both columns are the Primary Key.

Add the table to your relationship's window. Drag and drop both
columns to their respective tables in order to establish the
relationships.

This will allow multiple employees to be involved with an inspection
(service record check).

You would need to remove the [man no] column out of [service records
tbl:] when you do this. You wouldn't just delete the column. You
would copy the [service records tbl:] contents of [id] and [man no]
over to [New Table:] first so that your information was preserved
(using an append query).

You would then modify your forms and any associated programming to
accommodate the change. (Questions on those subjects should be posted
to microsoft.public.access.forms,
microsoft.public.access.formscoding, and possibly
microsoft.public.access.modulesdaovba)

Once this is done, you can work on moving your design changes into
production/use.


Sincerely,

Chris O.
Click to expand...
 
Ron A. said:
Again, thanks a million! The inspections are more detailed that I need. Let
me try and explain how I enter the data.

The mechanic brings me a paper copy of a work order created by a seperate
system. I then go out to the vehicle and ensure each job has been completed
within standards. I then log the inspection using: Vehicle registration #,
work order #, date of inspection, date w/o closed, inspector man #,
pass/fail, mechanics man #, remarks (for detailed info on discrepancies that
failed the vehicle) and for informational purposes (not required) I have
check boxes to show if the vehicle had a LOF, annual, wheel bearing repack,
brake inspection or a visual only if low mileage since last LOF.
Click to expand...

Ron A.,

Ok, I see now.

Just drop the WorkOrders table and retain the column for that value in
the Inspections table.

Without the FK constraints you'll just have to trust to fate that the
values are entered correctly. I'm not sure how you'll verify this.

The Vehicles table, if no VIN is kept on hand, can go with the
registration number you have as the primary key.


I hope this explains my process well enough.
Click to expand...

One of the tough parts about newsgroup help is that you never really
can explain entire processes well enough in this medium.

The help I was providing was not meant to be instructions on what you
should do in your database. (It could have worked that way, but I
understand that it won't.)

The help I was providing was meant to educate in the most general way
on how to modify your system to do what you need to do.

What you need to do is study the relationships set up in the the
tables I provided.

The design of the table InspectionEmployees, and the relationships
established on it from the Inspections table and the Employees table
are the part that demonstrates how to do what you want to do.

You need to look at that, compare it to your database's design, and
adapt it to your purposes.


First, before trying anything, create a copy of your database (and
preferably store it in an entirely different folder/directory), and
work *only* on the copy.

You have an [Employee Man No tbl:] table and a [service records tbl:]
table.

At a minimum you need to add a table with [man no] and [id] as
columns.

[New Table:]
[id]------\
[man no]---Both columns are the Primary Key.

Add the table to your relationship's window. Drag and drop both
columns to their respective tables in order to establish the
relationships.

This will allow multiple employees to be involved with an inspection
(service record check).

You would need to remove the [man no] column out of [service records
tbl:] when you do this. You wouldn't just delete the column. You
would copy the [service records tbl:] contents of [id] and [man no]
over to [New Table:] first so that your information was preserved
(using an append query).

You would then modify your forms and any associated programming to
accommodate the change. (Questions on those subjects should be posted
to microsoft.public.access.forms,
microsoft.public.access.formscoding, and possibly
microsoft.public.access.modulesdaovba)

Once this is done, you can work on moving your design changes into
production/use.


Sincerely,

Chris O.
 
Back
Top