J
jag
I'm not an expert at Access, nor a novice. Sort of in between - so I
thought. Anyway, here is my problem. I have obviously confused myself. I
have a db I created that I cannot get the relationships correctly sorted in
my mind.
There are several tables, but the entire db in based on a service address,
rather than a client. The tables are listed below according to their
priority:
1. Service Address - a request is made for service at an address.
2. Work Order - a work order is entered based on the service add, customer
or site name is not always required.
3. Permit - a permit is sometimes required for a service address. Work
orders will be created for the permits to the service address, and a
customer name info is used necessary.
4. Job# - Some times there are job numbers assigned to the larger projects.
Eventually work orders will be created for the jobs.
5. Customer information - sometimes the work orders are requested by a
caller, but not always so customer info is not always needed. But, customer
information is always required for a permit.
Now, this is my confusion on the relationships.
Service Address table
ServID: 1 to many with the Work Order Table ServID
?ServID: 1 to many with PermitID? (currently not using it this way)
?ServID: 1 to many with JobID? (currently not using it this way)
Work Order table
WOrderID : 1 to many with Permit WOrder ID
1 to many with Job# WOrderID
?1 to many with Customer with WOrderID?
Permit table:
PermitID:
ServID: 1 to many with Service Address ServID
WOrderID: 1 to many with Work Order WOrderID
CustID: ?1 to many with Customer's CustID?
Job table:
JobID: 1 to many with WorkOrder WOrderID
When I try to make the 1 to many for the Permit to the Customer, I get an
error relating to referential. Can anyone advise if these relationships
are wrong? Would it be more correct to just have the customer table have a
servID and relate the tables? Also, I believe I need to Work Order and
Permit tables related their their table ID not service address id?
I hope I haven't made this too confusing, as I think I keep adding to my own
confusion so it's hard to explain.
thought. Anyway, here is my problem. I have obviously confused myself. I
have a db I created that I cannot get the relationships correctly sorted in
my mind.
There are several tables, but the entire db in based on a service address,
rather than a client. The tables are listed below according to their
priority:
1. Service Address - a request is made for service at an address.
2. Work Order - a work order is entered based on the service add, customer
or site name is not always required.
3. Permit - a permit is sometimes required for a service address. Work
orders will be created for the permits to the service address, and a
customer name info is used necessary.
4. Job# - Some times there are job numbers assigned to the larger projects.
Eventually work orders will be created for the jobs.
5. Customer information - sometimes the work orders are requested by a
caller, but not always so customer info is not always needed. But, customer
information is always required for a permit.
Now, this is my confusion on the relationships.
Service Address table
ServID: 1 to many with the Work Order Table ServID
?ServID: 1 to many with PermitID? (currently not using it this way)
?ServID: 1 to many with JobID? (currently not using it this way)
Work Order table
WOrderID : 1 to many with Permit WOrder ID
1 to many with Job# WOrderID
?1 to many with Customer with WOrderID?
Permit table:
PermitID:
ServID: 1 to many with Service Address ServID
WOrderID: 1 to many with Work Order WOrderID
CustID: ?1 to many with Customer's CustID?
Job table:
JobID: 1 to many with WorkOrder WOrderID
When I try to make the 1 to many for the Permit to the Customer, I get an
error relating to referential. Can anyone advise if these relationships
are wrong? Would it be more correct to just have the customer table have a
servID and relate the tables? Also, I believe I need to Work Order and
Permit tables related their their table ID not service address id?
I hope I haven't made this too confusing, as I think I keep adding to my own
confusion so it's hard to explain.