Fairly New and Need Help (5 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.

 

[MajP]

To add to larry's design. Do not use special characters except an underscore
PO_No not PO#
COD_Bankrupt not COD/Bankrupt

However that COD/Bankrupt tables is not descriptive or designed correctly. You have hardwired your statuses as fields. Those should be choices.

tblPaymentStatus
-StatusID
-CustomerID_FK ' it is IMO very confusing to have PKs and FKs with the same name in different tables
-StatusDescription ' "Past Due", "Bad Check", "Bankrupt", .... "Credit Hold", "Account Verification" .....
-DateOfAction
-StatusNotes ' IMO it will get very confusing with different Notes in different tables with the same name. TechnicianNotes, CustomerNotes...

 

[Jack F]

To add to larry's design. Do not use special characters except an underscore
PO_No not PO#
COD_Bankrupt not COD/Bankrupt

However that COD/Bankrupt tables is not descriptive or designed correctly. You have hardwired your statuses as fields. Those should be choices.

tblPaymentStatus
-StatusID
-CustomerID_FK ' it is IMO very confusing to have PKs and FKs with the same name in different tables
-StatusDescription ' "Past Due", "Bad Check", "Bankrupt", .... "Credit Hold", "Account Verification" .....
-DateOfAction
-StatusNotes ' IMO it will get very confusing with different Notes in different tables with the same name. TechnicianNotes, CustomerNotes...

Thank you. I will adjust the names. I agree, in the future as the data grows, it will get confusing.

 

[MajP]

You do not have to prefix your objects but Access makes it confusing if you do not. If your make a report or query or form based on a table using the wizard you get the table name as the default. So now you have lots of different objects forms, report, query, table all with the same name. So most people do some version of
tblYourTable
qryYourQuery
rptYourReport
mdlYourModule
or
Table_YourTable
Rept_YourReport
etc

So now you have IMO a big problem. You have a table AppointmentDate with a field AppointmentDate, same with call date. I would never ever name a table and a field the same
tblAppointmentDate and field AppointmentDate


Another thing that avoids confusion is to make the table name and pk name the same concept
so
tblProperties would have PropertyID, but you have LocationID. It is minor but when you start building forms, queries, and reports it does not automatically pop out what field name I should be looking for in what table.

My naming convention is the PK is SomeID and the foreign key in another table is SomeID_FK. Again it is a minor thing but makes it pop what I am looking at when coding or building.

Any spaces or special characters will just cause pain in any name to include field names.

 

[Jack F]

You do not have to prefix your objects but Access makes it confusing if you do not. If your make a report or query or form based on a table using the wizard you get the table name as the default. So now you have lots of different objects forms, report, query, table all with the same name. So most people do some version of
tblYourTable
qryYourQuery
rptYourReport
mdlYourModule
or
Table_YourTable
Rept_YourReport
etc

So now you have IMO a big problem. You have a table AppointmentDate with a field AppointmentDate, same with call date. I would never ever name a table and a field the same
tblAppointmentDate and field AppointmentDate


Another thing that avoids confusion is to make the table name and pk name the same concept
so
tblProperties would have PropertyID, but you have LocationID. It is minor but when you start building forms, queries, and reports it does not automatically pop out what field name I should be looking for in what table.

My naming convention is the PK is SomeID and the foreign key in another table is SomeID_FK. Again it is a minor thing but makes it pop what I am looking at when coding or building.

Any spaces or special characters will just cause pain in any name to include field names.

Thank you for pointing this out. It makes sense in the bigger picture. I will do some adjusting as I continue developing the database.

 

[MajP]

People have different naming conventions the trick is to be consistent and make names that will make building and maintaining the db easier. There is good and bad, but no exact right or wrong.

This is mine.

Table names IMO need to be descriptive and plural. It is not a table of Appoint Date it is a table holding Appointments. Same with Calls it is not a table holding just call dates it is a table holding info about Calls.
I do the _FK on my foreign keys it make my life simpler others will not. I do see a mistake. Should be PO_NO_Required it looks like not required.
I but the underscore in Call_ID if not it gets confusing to read CallID with how many l. Also i deleted the fields PastDue, BadCheck, Bankrupt and those are pull downs in PaymentStatus.

 

[Jack F]

People have different naming conventions the trick is to be consistent and make names that will make building and maintaining the db easier. There is good and bad, but no exact right or wrong.

This is mine.
View attachment 123074

Table names IMO need to be descriptive and plural. It is not a table of Appoint Date it is a table holding Appointments. Same with Calls it is not a table holding just call dates it is a table holding info about Calls.
I do the _FK on my foreign keys it make my life simpler others will not. I do see a mistake. Should be PO_NO_Required it looks like not required.
I but the underscore in Call_ID if not it gets confusing to read CallID with how many l. Also i deleted the fields PastDue, BadCheck, Bankrupt and those are pull downs in PaymentStatus.

Thanks for the input. I see what you are saying. I like the designation _FK. I used a check box set up for PO_NO_Required. Hopefully it will make it easier in a form.

 

[MajP]

To design this I would have
Dashboard with buttons to get to the different forms
frmTechnicians to add edit technicians
frmCustomers with 2 continuous subforms for properties and payment status. However that could be a lot. I probably have a single form for adding customers first. Then another form frmCustomerProperties that has an unbound combobox to pick an existing customer and add properties or show their properties. Probably do the same with payment status

If you are taking customer calls and immediately creating an appointment then that would be on the same form.
Main form is Calls
Subform is appointment
But you would have pop ups from this form so you can see all the customer details. Building the dispatch form will take a little Art to be able to see all the information in one place

Also above the table name is tblCallDetails but I think it migh be tblServiceDetails. You take a call at the dispatch, you create an appointment, and you dispatch a service call. May be more descriptive.

 

[Jack F]

To design this I would have
Dashboard with buttons to get to the different forms
frmTechnicians to add edit technicians
frmCustomers with 2 continuous subforms for properties and payment status. However that could be a lot. I probably have a single form for adding customers first. Then another form frmCustomerProperties that has an unbound combobox to pick an existing customer and add properties or show their properties. Probably do the same with payment status

If you are taking customer calls and immediately creating an appointment then that would be on the same form.
Main form is Calls
Subform is appointment
But you would have pop ups from this form so you can see all the customer details. Building the dispatch form will take a little Art to be able to see all the information in one place

Also above the table name is tblCallDetails but I think it migh be tblServiceDetails. You take a call at the dispatch, you create an appointment, and you dispatch a service call. May be more descriptive.

I agree, take a call and make an appointment if there are no restrictions. The forms and subforms may be beyond my knowledge right now. I am working on trying to learn how to build nested subforms. So, that way, I should in theory be able to put all the information in 1 place and in front of me. That is about the only way I can think to do it.

 

[MajP]

The forms and subforms may be beyond my knowledge right now

People here will be more than willing to demo or help with that. Also IMO nested subforms can be not very intuitive. It takes a little bit of work but linked subforms (synchronized subforms) are often easier to understand. Especially if you want to show things from more than 2 tables.
This is not the best example but you can get the idea

On the right is a list of students and their tests that they are assigned (not started, in complete, complete). You can choose a class or even an exam and filter the list. The selected record is gold.
So if I select Duncan Baker exam 2624 then the details for that exam appear on the right for every question. Also the summary information is shown above for that exam. So there are 3 subforms being linked. Student exams, exam details, and exam summary.

 

[Jack F]

People here will be more than willing to demo or help with that. Also IMO nested subforms can be not very intuitive. It takes a little bit of work but linked subforms (synchronized subforms) are often easier to understand. Especially if you want to show things from more than 2 tables.
This is not the best example but you can get the idea
View attachment 123077

On the right is a list of students and their tests that they are assigned (not started, in complete, complete). You can choose a class or even an exam and filter the list. The selected record is gold.
So if I select Duncan Baker exam 2624 then the details for that exam appear on the right for every question. Also the summary information is shown above for that exam. So there are 3 subforms being linked. Student exams, exam details, and exam summary.

I am thinking subforms as well, if not trying expanded datasheet. I am pulling information from 5 different tables. The goal is to reduce that to veiw in one place, such as a pop-up or modul. This example is closer to achieving that.

 

[MajP]

Often novices try to do to much in one place where you are adding, editing, navigating main records and child records. It get hard to design and confusing. The Art is to be able to see some information but very easily get back and forth from the details. So it is a combination of summary information all on the dashboard but compartmentalized forms that do one thing. I often separate my new record creation from forms used to navigate to a record. Example I probably have a form to add a new customer separate from a form to navigate to a customer. I rarely would allow creation of a customer and customer properties at the same time. I find forms that add main and child records to be somewhat confusing.

 

[Jack F]

Often novices try to do to much in one place where you are adding, editing, navigating main records and child records. It get hard to design and confusing. The Art is to be able to see some information but very easily get back and forth from the details. So it is a combination of summary information all on the dashboard but compartmentalized forms that do one thing. I often separate my new record creation from forms used to navigate to a record. Example I probably have a form to add a new customer separate from a form to navigate to a customer. I rarely would allow creation of a customer and customer properties at the same time. I find forms that add main and child records to be somewhat confusing.

That is somewhat how I wound up seeking help. It was getting beyond my knowledge which meant my basic design concept was becoming more faulty.

 

[The_Doc_Man]

There is good and bad, but no exact right or wrong. (from MajP's post)

@Jack F - Actually, one little nit-pick. It is exactly wrong to define a naming convention, start to use it, and then abandon it even though you didn't abandon the project. Inconsistency in naming when you COULD have done something to avoid it is wrong - and YOU will be the one who suffers later when you have to do maintenance on a badly entangled set of code with names that don't follow the rules.

MajP is absolutely right, though. As far as naming conventions are concerned, you get to choose because it is your project, and in that context, there is no good or bad. If you are comfortable with it and can stick with the convention, it is by definition good - at least for you.