Fairly New and Need Help (4 Viewers)

[Jack F]

there is a sample customer service database template from microsoft.
the attached is the accdb from that template.

Thanks, I see a couple things I can incorporate but, I need it more for dispatch purposes.

 

[mike60smart]

Can you explain what you mean by Dispatch?

 

[mike60smart]

Hi Jack
I take it that 1 Service Request could end up having 1 or more actual appointments to fix the problem?

 

[Jack F]

Hi Jack
I take it that 1 Service Request could end up having 1 or more actual appointments to fix the problem?

Yes

 

[XPS35]

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.

• 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.

Based on these point a made this demo model:

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?

 

[Jack F]

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.

• 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.

Based on these point a made this demo model:
View attachment 123069
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?

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.

• 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.

Based on these point a made this demo model:
View attachment 123069
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.

 

[LarryE]

I am a service dispatcher for a small business. The database is an overhaul move away from MS Excel because, trying to do this in spreadsheets will be complicated for me. The idea of the database is to put all relevant customer information in one spot in front of me. So, the database will act as a dispatch resource and allow me to get the information I need at the time a service request is made. The customer can be commercial or residential. Relevant information: Customer Name, Customer Address, Does the customer have special requirements or restrictions to their order (Currently labeled as Notes)? Can I dispatch to the location or customer because of those restrictions or requirements? I assume this may need to be a nested subform but, I am not sure. Once I have the customer information in one spot in front of me, I can schedule and give the responding technician the information I see. So, there is less confusion for all sides.
Sample Data will look something close to: CustName: Scooby Doo / CustAddress: 101 Field Street / CustRequirements: Need PO# and pay immediately or CustRequirements: Cannot dispatch because of past due account / Notes: Cust needs XYZ service for or containing given information from CustRequest. I can't provide systems data because I have Personally Identifiable Information already inserted.

I would start with a design similar to this and go from there.

Of course you may want to add other relevant fields in the tables as required. Attached is the file.