Database design for snow plowing billing

[Stephenson]

Hello All,

I'm attempting to build a very basic DB for billing our snow plowing accounts. For each account there is a set price for plowing and shoveling. These prices do go up based on snow depth. Example, "phase 2" is an additional 35%, "phase 3" is an additional 70%. Applying deicer and sand is based on the amount used.

I have a table for Accounts and one for AccountLog. My form records to the table AccountLog that has yes/no fields for plowing, shoveling...The actual rates are fields in the table Accounts.
I'm unclear the best way to put this into a report. Maybe I need to modify my input process/tables. I like the simplicity of using checkboxes to show if there has been plowing, shoveling etc., but I'm not sure how to apply this to the report.

Is there a good way to build a report by using checkboxes in this manner or does my form/table need to be changed?

[June7]

This 'checkbox' structure is not a normalized database.

Conventional structure would be a table for recording transactions of each service event. And since each service can have multiple charges, a second table for the service event details. Like:

tblCustomers
CustomerID (primary key)
CustomerName
CustomerAddress

tblServices
ServiceID (primary key)
ServiceDescription
ServiceRate

tblServiceInvoice
InvoiceID (primary key)
ServiceDate
AccountID

tblServiceDetails
InvoiceID (foreign key)
ServiceCode (foreign key)

[Stephenson]

Thank you June7,

I'm going to rework my tables as you suggest.

Each account has a specific plow rate shovel rate. It is not by any unit. It is a predetermined price. I'm not sure if this affects your table design.

[ssanfu]

For each account there is a set price for plowing and shoveling.

So each customer (account) can have a different price for each job type?
When I plowed (commercially, for two seasons), I charged by lot size, plus the hand work. I didn't do sanding or deicing.

I played around with your dB...... doesn't look the same now.
You table design was like a spreadsheet - short and wide. A database table will be designed tall and narrow.

Maybe you can combine June's suggestions and my example.....
Remember, I did not draw a design on paper - this is just off the top of my head. So it is really rough; more code is needed.


Good luck.....


EDIT:
Be aware that in your dB, there were lots of reserved words used (the highlighted ones):
Public Sub First()
Public Sub Last()
Public Sub Load(Form)
Public Sub Save()
Public Sub Current(Form)
Public Sub Search(Form)

Delete (button on form)

Should not use reserved words as object names or subroutine names.

And every code page should have "OPTION Explicit" at the top.

[Stephenson]

Steve,

Thank you for having another look. (Please understand much of this was setup before my current employment) We do all our rates off of a county satellite view that can calculate area. We separate the different plowing areas; parking lots, driveways, roads, etc. From there we base our rates on the difficulty of the job. Needless to say this currently is a very ugly spreadsheet as you keenly noticed. Unfortunately the only rates I have now are based on the owners "gut".

As an attempt to better our system I'm trying to use the current rates to do billing in an attempt to show them how useful Access can be. Therefore I have to come here for the expertise from people such as yourself.

In addition to all of this our rates change based on snow level as it is more difficult to remove greater amounts of snow. I do not see in your example a way to compensate for this. Maybe I'm just missing it.

Thank you for your contribution. I'm so happy to have the help.

[June7]

Two ways to handle changing prices:

1. a new record in tblServices for every possible rate, save the ID from this table as a FK in tblServiceDetails

2. save the rate charged with each record into tblServiceDetails - this can be done by manually entering the amount or by selecting rate item from combobox and code saves the rate to record

[Stephenson]

I was trying to make it easy as possible for the person imputing the data. If there was service I only wanted them to enter the account name (customer), what service they received, the amout of deicer or sand used and what snow phase it was. As far as any numbers/amounts I want that to be held behind the form.

[ssanfu]

In addition to all of this our rates change based on snow level as it is more difficult to remove greater amounts of snow. I do not see in your example a way to compensate for this. Maybe I'm just missing it.

Nope. Obviously, I don't know all of your requirements and I skipped that. Wouldn't be hard, but......well, it was just a demo.

If there was service I only wanted them to enter the account name (customer), what service they received, the amout of deicer or sand used and what snow phase it was. As far as any numbers/amounts I want that to be held behind the form.

So in my form, actually the subform, after selecting the "Type" (Plow, shovel,...), code could hide or unhide controls.
Then in the before update event of the form, the total amount (cost) could be calculated and entered into the cost control.
Normally calculated amounts are not stored, but it sounds like rates could change.

Right now I would focus on getting the table structure correct (as possible ).
Then the queries, followed by the forms and lastly the reports.

[June7]

So, instead of user selecting service item from combobox, user enters several factors which determine the rate to be charged. As suggested, code can calculate the rate. Whether or not to save the calculated value or to calculate dynamically when needed is another issue.

[Stephenson]

I'm working on the tables as recommended (My hardest point) and also looking at code as you suggest June7. I think I'm getting somewhere.

Thank you both for the help. I will post back soon with more.

[Stephenson]

Thanks to you advice Steve and June7 I was able to make some progress today with my database. I still have plenty of issues, but hope I'm headed in the right path. Please let me know if I'm way off track.

[ssanfu]

Please let me know if I'm way off track.

Well, I do see some major issues.

A)--------------------------------------
RE: Deicing cost.
You have used a calculated field for deicing cost. Today the cost is $2.65 if plowed times the amount used or $3.10 times amount used if not plowed first.
Lets say that Dec 1st, you buy more deicing "stuff" (fluid or solid granules?) at a higher price. So you increase the customer prices to $2.85 if plowed or $3.20 times amount used if not plowed.
Now it is Dec 3rd and you use deicer, then enter the amount into the database. You compare the cost to last month for the same customer. Whoa..... what happened? The amount used in Nov is the same as Dec - and the cost is the same also!! What happened?

Using calculated fields has two effects:
1) ALL records (historical and current) get recalculated when you change the (in this case) cost.
2) Since the cost ($) is hard coded in the table, you have to get into design view to change the price.

2 of many reasons why I NEVER use calculated fields.


B)--------------------------------------
You have hard coded the % increase in phases in the code. If the % increase was in a table, anyone could change the %.


C)--------------------------------------
The Jobs table is still designed like a spreadsheet. I know it seems easier, but you will more than likely end up .
Not the way I would design the dB, but


D)--------------------------------------
In the Customer table, PlowRate and ShovelRate are Text fields; both fields should be Currency

E)--------------------------------------
In "Private Sub cboAccount_AfterUpdate()", amounts are entered for Plowed and Shoveled. Do not need this sub.......


F)--------------------------------------
"Private Sub frmPhase_AfterUpdate()" doesn't do what you expect.
In your dB, make the hidden text boxes visible.
Select customer Sixth, enter a date, then check "Plowed". An amount appears for "Shoveled???
, seems to me you would be overcharging the customer.

Now uncheck Plowed. Amount doesn't change to zero.


One solution.... (see attached dB)
Added two fields to the customer combo box row source. (won't need the DLookup() function)
Changed the code for "Private Sub frmPhase_AfterUpdate()"
Changed code for check boxes Plowed and Shoveled.



Have fun.....

(BTW - check spelling for Shoveled)

[Stephenson]

Steve,

Thank you so much for the review. This is much needed as you can tell. I appreciate your advice and will do me best to learn from it.

One question off the bat is how do I make my tables less like spreadsheets? Should I split some of the fields into another table?

[ssanfu]

how do I make my tables less like spreadsheets? Should I split some of the fields into another table?

Look again at the dB in post #4. Look at the relationships. Look at the tables

Again: "You table design was like a spreadsheet - short and wide. A database table will be designed tall and narrow."

The Customers (Accounts) table.
This table should be attributes about the customer. Would you think that the customer car make should be in this table? How does the "plowrate" describe an attribute of the customer? So "plowrate" should be in its own table. (and the car make would be in a different table )


Look at your original form. You have "Services" - Plowed, Shoveled, Apply Deicer, Apply Sand.
In a spreadsheet, you would have 4 columns.
In a database, you would have 1 field (column) - Plowed, Shoveled, Apply Deicer, Apply Sand are data in that field just like the city field in the customer table. There are many cities. Should we have a field (column) for each city in your state??

That means that if you perform 3 services, Plowed, Shoveled, Apply Deicer,
in a spreadsheet, there will be 1 row
in a database, there will be 3 records.


Getting off of my , if you are more comfortable with a "spreadsheet" design. nothing is stopping you from using it.
If it works for you right now, then use it. You can always redesign it later on when you have more time.


Keep posting and I'll keep commenting....

[Stephenson]

Hello Steve.

I have been excited to get back to my database and give your suggestions a try.

A) Most if not all of my effort has gone into building my tables so it's not so obvious I'm more accustom to Excel. I'm not sure if there are too many and if I've separated them how I should. Aside from not knowing what I'm doing in general, I've decided my troubles with forcing tables into spreadsheets is that I'm unaware of how to handle my data after it's been entered into the tables. I have been looking more at the entire process to get my head more in the game. You've been a great help with this.

B) As for my forms I have a few questions and lets assume a single form setup with subs as needed.

1) How would I add a value for both plowing and shoveling in my AddRates form without advancing to a new form to enter the data separate. Do I just ad a subform to do this or is there a
smarter way to accomplish this?

2) I admit our current pricing setup is off, so please set this aside. I have individual plow and shovel rates for each customer, but the removal and piling fees are the same for all customers.
How do I set my tables up to fit this type of data. I figure there is a better way to do this rather than make a table dedicated to the two.

3) You showed me something I had no idea I could do (and am excited about) by changing the control source for cboAccount, then using the columns in VBA. I have switched my calculated fields
to another table and am have tried to write an SQL (?) statement to get them to work a similar fashion you did. Do you have any suggestions to get this to work?

4) Loud and clear to not use calculated fields in a table. Where is it best to do my calculations. I have had a difficult time finding information on this. I worry about slowing down my application
with a poor design. If you have suggestions/examples of where to do calculations please let me know.

C) I believe I fixed my spelling of shoveled everywhere, so please, oh please get back on your soapbox.

Thank you again for the help.