Relationships

[CAL]

I'm running into a problem with my relationships and I was
wondering if anyone would be so kind as to take a look at
my table and let me know if they see anything that would
cause my problem.

The goal is to create a hierarchy of:
Customers
which have many
Locations
which have many
Equipment
which have many
Workorders

The Workorders table looks like it has a subform of
equipment which in turn has a subform of workorders.. this
is driving me mad.. any ideas?

The files are located at
www.photobydesign.net/ServiceDB/smallrelationshiptable.pdf
and
www.photobydesign.net/ServiceDB/screenshot.pdf


Thank you!

 

[Tim Ferguson]

Equipment
which have many
Workorders

Well, this means that each Workorder relates to exactly one Equipment, but
each Equipment has lots of Workorders. Is this what you want?

I have a hunch that you really have a many-many relationship between
Equipments and Workorders. It's not really easy to tell.

Another point: please do not even attempt to create a database using table
datasheets. You can do whatever you want to do using forms (and at first
sight it does not seem very complex), but the table view is just not up to
anything useful. Nor is it meant to be.

B Wishes


Tim F

 

[Guest]

Ideally, each Workorder would reference only one piece of
equipment. Our technicians go out on a workorder with the
intention of servicing only one machine. Each piece of
equipment can be serviced multiple times on seperate
workorders.

Realistically, there are situations when they try to put
multiple machines on one workorder, but we want to avoid
this.

I'm not creating my database using the table datasheets...
I created the tables in design view and then tried to
start creating forms to enter the data. I was in the
datasheet trying to figure out why I can't create a form
to create a workorder that references equipment. Which I
still have not been able to figure out.
Danke!

 

[Tim Ferguson]

Realistically, there are situations when they try to put
multiple machines on one workorder, but we want to avoid
this.

FGS, model your database on what actually happens (or even on what might
possibly, perhaps in a parallel universe, happen) rather than on what
management would like to pretend happens. If it's a many-to-many
relationship, then that's what you should model, and have another table
like this:

CalledOutOn
===========
*WorkOrderNumber FK references WorkOrders
*EquipmentID FK references Equipments
Reason // = "P" for primary or "O" for opportunistic
Completed // True if the service was all sorted out or
// False if it needs a repeat or its own WO etc.

and so on. Hope that makes sense


Tim F