Thanks, I see a couple things I can incorporate but, I need it more for dispatch purposes.
Fairly New and Need Help (1 Viewer)
- Thread starter Jack F
- Start date
mike60smart
Registered User.
- Local time
- Today, 22:43
- Joined
- Aug 6, 2017
- Messages
- 2,308
Can you explain what you mean by Dispatch?
mike60smart
Registered User.
- Local time
- Today, 22:43
- Joined
- Aug 6, 2017
- Messages
- 2,308
Hi Jack
I take it that 1 Service Request could end up having 1 or more actual appointments to fix the problem?
I take it that 1 Service Request could end up having 1 or more actual appointments to fix the problem?
XPS35
Active member
- Local time
- Today, 23:43
- Joined
- Jul 19, 2022
- Messages
- 217
I'm sorry to say this Jack, but I don't think your model is correct at all. To name just a few key points.
But I still have some doubts. Specifically, whether there really is a one-to-many relationship.
Can a customer have multiple CODs?
And can a customer have multiple addresses?
I also doubt the need to distinguish between commercial and residential. There's only one field difference.
Do you need notes in all tables?
- You create relationships on non-key fields. If you have a customer table with, for example, CustID as the key, you include CustID as the foreign key in linked tables and link the tables on those fields.
- CustID, FirstName, and LastName appear in all tables. The names should only appear in the customer table.
- Relationships represent real-world logic. For example: a customer has one or more requirements. The logic behind the relationships that run via the street name is completely unclear (to me).
- You should create relationships between the main table (customer) and the related tables, not between the related tables themselves.
But I still have some doubts. Specifically, whether there really is a one-to-many relationship.
Can a customer have multiple CODs?
And can a customer have multiple addresses?
I also doubt the need to distinguish between commercial and residential. There's only one field difference.
Do you need notes in all tables?
Thanks and I see your point. A 1 to many does clean up the model much better than my idea. Yes, a customer can have multiple COD's. They may have multiple commercial address or be a private real esate investor, being required to pay cash at the time of service. Perhaps, they do not have a credit account with us. The difference between commercial and residential is more record keeping and auditing purposes. So, notes do need to be on each page. Part of the database's purpose is to tie all of this information in one central place at the point of scheduling an appointment. The responding technician can then have or get the information. I as the dispatcher, can present it to them or give it to other parts of our operation on request. Currently, I have to go to 5 different places to get all the information I need and in front of me. It is easy for stuff to get missed and is time consuming. That is why I am trying to move away from Excel to Access. It would be hard for me to attempt it in Excel, because I don't code. Thank you for your input and I hope this will shed some light on things.
