Multiple Prices for same service. (1 Viewer)

[lanman]

Trying to set a cost vs billing database. I can do the cost part of it, because those prices stay the same. but when it comes to billing I have multiple contracts that all have different billing rates. And I want to set my dbase up so that once I state what type of service and what materials I am using on a single job I can put those totlas hours to multiply by both the cost of those services and the billing rate of those services without having to enter the info twice or having to manualy enter the billing rate for each job. My Operations Managers are going to use this database to enter each job and the time and materials for that job. So I just need a way that I can enter each clients billing info. so that when the Operations Managers enter the billing and materials for a job with that particular client the billing automatically totals. I have everything else done except this part. So any help would be much apprieciated.

 

[jdjewell]

Let's see here, if I understand correctly, you have a db structure something like this

             [U]JobService[/U]|           |[U]Service[/U]
             JobID     |           |ServiceID
             ServiceID |---------- |Description
             Hours     |           |Rate
            /
[U]Job[/U]       |/
JobID     |
TotalCost |\
            \
             [U]JobMaterial[/U]|           |[U]Material[/U]
             JobID      |           |MaterialID
             MaterialID |---------- |Description
             Qty        |           |Cost

Under this structure, a Job can have multiple services, a service can have multiple jobs, a job can have multiple materials and a material can have multiple jobs. The total cost field in the Job table is derived as follows:

TotalCost = ((Sum(Select JobMaterial.Qty * Material.Cost From JobMaterial INNER JOIN Material ON JobMaterial.MaterialID = Material.MaterialID WHERE JobMaterial.JobID = Job.JobID)) + (Sum(Select JobService.Hours * Service.Rate From JobService INNER JOIN JobService.ServiceID = Service.ServiceID WHERE JobService.JobID = Job.JobID)))

Keep in mind that this is pretty much pseudocode, as I am working on other things right now, but this should be the general idea.

 

[lanman]

jdjewell Thanks for you help,

Actually each job has just one service, multiple equipment, multiple materials. But for each company that has a job asigned to it will have different billint rates.

For Example. Client 'abc' needs to techs billed at "Sr. Tech - $35hr and Junior tech - $25hr". One peice of equipment billed at - $55hr, and lets say three diff. materials billed at $5 foot $10 foot and $2 each.

Then Client 'CBA' needs the same type of job but we have a contract with them and the billing is Sr.Tech $25 Jr.Tech $20, equipment at $40hr and materials at $4foot $7foot and $1.5each.

I need to some how set up a table that would have these rates assigned to each individual client. So when a OM enters the job it will auto calculate the correct billing for that client.

I already have it set up that it auto calcs the cost for the job, and if it is a "lump sum" job it compares the two totals and puts a side bar graph showing the differece between the two. But if it is a time and materials job I need it to show the difference between the cost and the billing for that company. that is were I am running into a brick wall. I don't know how to create a table to list all the billing rates for each Client and the materials for that billing rate. Hopefully this makes sence.

Thanks for your Help.

edit:
PS.
I know how to do all the calculations, I just need some type of table design that will hold all of the billing rates so that I can pull them or assign that bill rate to a client.

 

[jdjewell]

Not going to do another design...

I think, building off of my original design, that I would do the following:

1. Take Rate out of Service table and put it in JobService table
2. Add tables Company, CompanyEquipmentRate, CompanyServiceRate, CompanyMaterialRate, CompanyJob, JobEquipment and Equipment with following structures:

Company --> CompanyID and whatever other info. you track
CompanyEquipmentRate --> CompanyID, EquipmentID, Rate
CompanyServiceRate --> CompanyID, ServiceID, Rate
CompanyMaterialRate --> CompanyID, MaterialID, Rate
CompanyJob --> CompanyID and JobID
JobEquipment --> JobID, EquipmentID and Hours
Equipment --> EquipmentID, Description, Rate

You can now have multiple billing rates for the same piece of equipment, same material or same service, based upon the Company.

You can take this a step further, by changing CompanyID on the CompanyEquipmentRate, CompanyServiceRate and CompanyMaterialRate tables, to be CompanyJobID...thereby enabling your company to change the cost not only based upon company, but if you want to increase the rate because of a job...it's covered.

Let me know if you need more information about this. Your calculations (for example) would now look something like:

select (sum(CER.Rate * JE.Hours)) as EquipmentCost from Company C INNER JOIN CompanyEquipmentRate CER ON C.CompanyID = CER.CompanyID INNER JOIN Equipment E ON E.EquipmentID = CER.EquipmentID INNER JOIN JobEquipment JE ON JE.EquipmentID = E.EquipmentID INNER JOIN Job ON Job.JobID = JE.JobID

Again, this is pseudocode, but I hope you get the idea.

 

[lanman]

Thanks, I really appreciate your help.