If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Rate tables
I'm working on building a database for our accounting team and they want to
use it to track Services revenue. The customer buys an amount of consulting $'s and they either are bound to our "standard rates" or can negotiate the rates we charge for Engineers, Consultants, etc. One of their requests is to create/store standard or custom rates for each Project/Invoice and when the hours are entered into the system for each role, they run up against these custom rates to determine the usage against their balance of consulting $'s they purchased upfront. I guess my question lies in the fact that I can't figure out how I would store the custom rates for each order in a table. Any suggestions? Here's what it currently looks like but it's going to be unwieldy to enter these for a thousand or more invoices each month. I would rather they enter in the rates against the hours in the Excel upload but they're insisting the rates be stored in the database. Is this common to store rates like this in a database? ProjectID C_ID CC Consultant Sr_Cnslt Proj_Mgr Trainer ABIO0004 1969 100 200 200 225 220 ABQS0006 2558 100 200 200 225 220 ABQS0007 2558 100 200 200 225 220 ABQS0008 2558 100 200 200 225 220 ABQS0009 2558 200 1000 1000 1200 1150 |
#2
|
|||
|
|||
Rate tables
On Mon, 25 Aug 2008 21:16:13 -0700, larochy
wrote: I'm working on building a database for our accounting team and they want to use it to track Services revenue. The customer buys an amount of consulting $'s and they either are bound to our "standard rates" or can negotiate the rates we charge for Engineers, Consultants, etc. One of their requests is to create/store standard or custom rates for each Project/Invoice and when the hours are entered into the system for each role, they run up against these custom rates to determine the usage against their balance of consulting $'s they purchased upfront. I guess my question lies in the fact that I can't figure out how I would store the custom rates for each order in a table. Any suggestions? Here's what it currently looks like but it's going to be unwieldy to enter these for a thousand or more invoices each month. I would rather they enter in the rates against the hours in the Excel upload but they're insisting the rates be stored in the database. Is this common to store rates like this in a database? ProjectID C_ID CC Consultant Sr_Cnslt Proj_Mgr Trainer ABIO0004 1969 100 200 200 225 220 ABQS0006 2558 100 200 200 225 220 ABQS0007 2558 100 200 200 225 220 ABQS0008 2558 100 200 200 225 220 ABQS0009 2558 200 1000 1000 1200 1150 Not in a properly normalized database, no. What if you add a new rate category? Add a new field to your table, change all your queries, change all your forms, change all your reports!? Ouch! "Fields are expensive, records are cheap". Rather than a spreadsheet design like this consider having a normalized set of tables: RateTypes RateType Text primary key (e.g. Consultant, Senior Consultant, ...) BaseRates RateType Text link to RateTypes BaseRate Currency the default rate for this service EffectiveDate Date/Time EndDate Date/Time ProjectRates ProjectID link to the Projects table primary key RateType link to Rates BilledRate Currency filled in with VBA code in a form from BaseRates, and available for modification if you choose to charge project ABQS0009 a 500% premium for being such pains in the neck) -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|