Fairly New and Need Help (1 Viewer)

[Jack F]

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

Thank you. As I continue the development, I will work on better naming. So far, this is all concept and design stage. I agree, the future of the project as is, will definitely be a mess during maintenance as the data grows.

 

[LarryE]

If you are going to use forms and sub-forms (which I prefer) then the design will give you a logical sequence by which you learn which form is a subform of which parent form. I have taken MajP's design and rearranged it so all of the primary keys are on the left and foreign keys on the right, so the design is read from left to right:

So, when you design your forms, you would work backwards from right to left.

1. Create a continuous form for tblCallDetails input first
2. Create a single form for tblAppointments input. Then add the tblCallDetails form as a subform.
3. Create a single form for the tblCalls input. Then add the tblAppointments form as a subform.
4. Create a single form for the tblProperties input. Then add the tblCalls form as a subform.
5. Finally create a single form for the tblCustomers input. Then add the tblProperties form as a subform.

When you open your project, you just autoopen the tblCustomers form only. All the others are already there and linked with Master/Child foreign key fields. If you link forms this way, ACCESS will automatically add the correct foreign keys when new records are created on any of the forms and keep track of everything else with fewer problems. I always use as much native ACCESS functionality as possible.

I prefer this method over using menus especially when users need all information in front of them.

 

[Jack F]

If you are going to use forms and sub-forms (which I prefer) then the design will give you a logical sequence by which you learn which form is a subform of which parent form. I have taken MajP's design and rearranged it so all of the primary keys are on the left and foreign keys on the right, so the design is read from left to right:
View attachment 123078
So, when you design your forms, you would work backwards from right to left.

1. Create a continuous form for tblCallDetails input first
2. Create a single form for tblAppointments input. Then add the tblCallDetails form as a subform.
3. Create a single form for the tblCalls input. Then add the tblAppointments form as a subform.
4. Create a single form for the tblProperties input. Then add the tblCalls form as a subform.
5. Finally create a single form for the tblCustomers input. Then add the tblProperties form as a subform.

When you open your project, you just autoopen the tblCustomers form only. All the others are already there and linked with Master/Child foreign key fields. If you link forms this way, ACCESS will automatically add the correct foreign keys when new records are created on any of the forms and keep track of everything else with fewer problems. I always use as much native ACCESS functionality as possible.

I prefer this method over using menus especially when users need all information in front of them.

Thank you for your help and information.

 

[MajP]

In your business does the customer have multiple properties as well as a main property?
The way this is set up it would be like the customer is McDonalds and in the customer table you have a corporate address information for billing but you are going out and servicing individual McDonald properties.
If that is not the case then you may not need the address information in the customer table. Or even if that is the fact then you still remove that information here and add the corporate as another property. Then add a field to mark the property as something like "Billing", "Service" to differenitate.

 

[Jack F]

In your business does the customer have multiple properties as well as a main property?
The way this is set up it would be like the customer is McDonalds and in the customer table you have a corporate address information for billing but you are going out and servicing individual McDonald properties.
If that is not the case then you may not need the address information in the customer table. Or even if that is the fact then you still remove that information here and add the corporate as another property. Then add a field to mark the property as something like "Billing", "Service" to differenitate.

That is sometimes the case.

 

[MajP]

I will ask it a different way. Which address information do you envision in the customer table and what goes in the separate property table.
If these types of addresses are not fundamentally different then I would put everything in the properties table.

For example if I am a property management company then my address is fundamentally different than a property I manage. I could see those being in separate tables. You would not dispatch to my office but to my managed properties.
The way this db is proposed you really can only dispatch to something in the properties table. What you do not want to do is then duplicate the information in both the customer table and the properties table if you can avoid it.

 

[Jack F]

I will ask it a different way. Which address information do you envision in the customer table and what goes in the separate property table.
If these types of addresses are not fundamentally different then I would put everything in the properties table.

For example if I am a property management company then my address is fundamentally different than a property I manage. I could see those being in separate tables. You would not dispatch to my office but to my managed properties.
The way this db is proposed you really can only dispatch to something in the properties table. What you do not want to do is then duplicate the information in both the customer table and the properties table if you can avoid it.

The address can be fundamentally different. If you were a company and manage multiple properties, I would create a designation for each. However for billing, I would have a separate address for that. However, if you had a local office and a separate billing address, I can designate the local office address and "say" an out of state or even an across town billing address or PO box, then I would need several different designation property addresses. On the residential side, you may own 3 properties across the general town, city etc... and require billing to go to a central hub/location. I would need to define that.

 

[MajP]

So as it is set up you can use the customer table as your billing address, but you still have to add that address to the property table if you also dispatch to that property. If this just represents a mailing address then it does not have to be duplicated in the properties table. In the future you can add a button to "create Customer Property" from customer address. For the case where a customer address is a physical address to dispatch to. Then you would create a new customer add the customer address and hit the button to create a property from that address.

However if you are saying a Customer can have multiple properties and those properties can have a physical address and a mailing address then you will have to add another table.

 

[MajP]

Also I think in the table I call payment status, you need a field Resolved Date
If customer 1 is Past Due on 1/1/2026 and makes payment on 3/1/2026 you want to mark that as resolved. Now you have a history of late payments and how quickly they resolve the issue.
Also in the service call details you probably want a status field "Work Complete", "On hold - awaiting Parts", "Pending Client Approval", etc
You have a start and stop which only logs service time, but not if you completed the work and if follow up is necessary.

 

[MajP]

Do you plan to track work orders and parts against the service?

 

[Jack F]

Also I think in the table I call payment status, you need a field Resolved Date
If customer 1 is Past Due on 1/1/2026 and makes payment on 3/1/2026 you want to mark that as resolved. Now you have a history of late payments and how quickly they resolve the issue.
Also in the service call details you probably want a status field "Work Complete", "On hold - awaiting Parts", "Pending Client Approval", etc
You have a start and stop which only logs service time, but not if you completed the work and if follow up is necessary.

I have the information in accounting software. I do not necessarily need to duplicate it in the dispatch part. I do have a status field in the appointment setting information that designates complete, on hold, go back, etc...

 

[Jack F]

Do you plan to track work orders and parts against the service?

No, that is handled by a different person in the company. I come into play once the parts are here and ready to schedule.

 

[MajP]

Here is a working demo. This ban be a lot cleaner and add lots of bells and whistles but this is to demo trying to put all info and functions at your finger tips.

1. Need to create a new call. Pick a property (thus a customer).
2. Either create an appointment immediately or have a way to come back to the call log and create an appointment
3. Be able to see all calls, those assigned an appointment, those with completed service
4. Be able to navigate and update all of those
5. Select a call and see the information for that customer and for that property
6. be able to manage all customers and see their details easily
7. be able to add properties to a customer
8. be able to add/edit payment status.

Here is the Dashboard.

on the left is the call log. If the call has been has an appointment it is assigned, if a service call has been conducted it is Serviced, and Not assigned when no appointment.
When you click on a record in the call log it shows the customer details to the right and the property details below that.

Click New Customer Call to create a new call

Pick a customer and property for that customer. The date and times default to current date and time.
You can assign an appointment immediately or come back to it.

If you double click in the log it will pop open an existing call and you can assign the appointment or edit the call

I double click on the not assigned Wonder woman.

You can edit and edit customers

A list of customers comes up and then you can click to edit or hit button to add new.

It needs some additional work so that when you click in the call log the Details for that call a clear. I would put some information in the header. Currently the subforms all link but not clear what you are clicked on this. This is a start to show how you present information and provide the functions for add, edit, delete, assign etc. Making this clean and understandable is a lot of "art", so this is just a start.

 

[Jack F]

Here is a working demo. This ban be a lot cleaner and add lots of bells and whistles but this is to demo trying to put all info and functions at your finger tips.

1. Need to create a new call. Pick a property (thus a customer).
2. Either create an appointment immediately or have a way to come back to the call log and create an appointment
3. Be able to see all calls, those assigned an appointment, those with completed service
4. Be able to navigate and update all of those
5. Select a call and see the information for that customer and for that property
6. be able to manage all customers and see their details easily
7. be able to add properties to a customer
8. be able to add/edit payment status.

Here is the Dashboard.
View attachment 123101
on the left is the call log. If the call has been has an appointment it is assigned, if a service call has been conducted it is Serviced, and Not assigned when no appointment.
When you click on a record in the call log it shows the customer details to the right and the property details below that.

Click New Customer Call to create a new call
View attachment 123102

Pick a customer and property for that customer. The date and times default to current date and time.
You can assign an appointment immediately or come back to it.

If you double click in the log it will pop open an existing call and you can assign the appointment or edit the call
View attachment 123103
I double click on the not assigned Wonder woman.

You can edit and edit customers
View attachment 123104
A list of customers comes up and then you can click to edit or hit button to add new.

It needs some additional work so that when you click in the call log the Details for that call a clear. I would put some information in the header. Currently the subforms all link but not clear what you are clicked on this. This is a start to show how you present information and provide the functions for add, edit, delete, assign etc. Making this clean and understandable is a lot of "art", so this is just a start.

Nice, thanks for sharing

 

[RonPaii]

on the left is the call log. If the call has been has an appointment it is assigned, if a service call has been conducted it is Serviced, and Not assigned when no appointment.
When you click on a record in the call log it shows the customer details to the right and the property details below that.

Click New Customer Call to create a new call
View attachment 123102

Betty, Fred and Scooby are going to be very upset that you doxed them like that

 

[Jack F]

Betty, Fred and Scooby are going to be very upset that you doxed them like that

 

[MajP]

Here are a couple fixes making it more intuitive

The selected row in the call log is gold making it clear which one you are looking at
The New call button is more prominent
I made the linking of the subforms easier by binding the recordsource of the main form to the same query as the call log subform.

 

[Jack F]

Here are a couple fixes making it more intuitiveView attachment 123107

The selected row in the call log is gold making it clear which one you are looking at
The New call button is more prominent
I made the linking of the subforms easier by binding the recordsource of the main form to the same query as the call log subform.

Those make it a bit easier and more intuitive.

 

[MajP]

Those make it a bit easier and more intuitive

This is kind of my point. If you have a form that does one thing only, it is real easy to use solutions right out of the box and it will be useable and understandable. It becomes kind of a catch 22. As you add more complexity to the form you need more bells and whistles to make it understandable. Then to make it more user friendly you need more features which require more code and design.
So for example all your calls are in the list, but that would not be very useable. You need to be able to see which ones need to be assigned an appointment and which ones a service call took place. That color coding is somewhat complicated because those statuses come from other tables, and they are not really statuses. They are determined by the existence of a record in a table. My point is that putting a lot of things on to a single form is not hard, but making it understandable and useable can be involved. So this is a challenging project.

 

[MajP]

So another example of a future feature that will be needed. Eventually your call list will get extremely large with old calls. That list will no longer be useable.
I can envision an option group at the top of the list to filter what you want to see.
Calls needing appointments
Calls with appointments
Open Callls (both of the above)
Closed Calls (ones with a service "call details")

The default is probably "open calls"

Also I did not add a place to put "Call Details", which if I understand is basically a record of service. When it started and when it completed. Also that table may need to get rethought. Currently a call comes in requesting service at a specific property. Then you schedule an appointment. You can schedule one or more appointments against that call. Which makes sense if the problem is not resolved. But if call details is the actual start and end times of the service (you would also need a actual date), the relationship is such that you could have multiple services against the same appointment. If that is not really the case and an appointment one service then those fields would go in the appointment table.
Apointmentdate
appointmentTime
ServiceDate
ServiceStartTime
ServiceEndTime.
If you make CallDetails a child table of an appointment but will never have more than one calldetail per appointment then that will create unneeded complexity. Instead of a couple more fields it is a whole new subform.