Query to calculate cost from mutiple tables. 1

[tmcnutt]

I have the following 3 tables set up and need to know how to calculate the total price which is based on (BaseCost + Hours * Rate). Table 2 can have many different departments for the same part. Table 3 has different rates for each department. For example, I have PartID (7) which hase a BaseCost ($365) for Table 1. Table 2 has PartID (7), Hours (3) and Department (Mechanical) and also has same PartID (7), Hours (4) and Department (Electrical). Table 3 has Department (Electrical) and Rate ($40) and has Department (Mechanical) and Rate ($45). The total cost is the base cost plus hours * rate for all departments. For this example is should be 365 + (3 *45) + (4*40) = 660.

Table 1
PartID
BaseCost

Table 2
PartID
Hours
Department

Table 3
Department
Rate

Can you give me an example of SQL code? I have tried to set up the query, but I get 2 records returned which is the base cost + department cost for one department and the 2nd record is the different department cost = base cost. Also, how should the relationships be set up? I have 1 to many from Table 1 to Table 2 and a 1 to many from Table 3 to Table 2.

Thanks,
Tom

 

[PHV]

Maybe something like this:
SELECT A.PartID, A.BaseCost + SUM(B.Hours * C.Rate)
FROM (Table1 A INNER JOIN Table2 B ON A.PartID=B.PartID)
INNER JOIN Table3 C ON B.Department=C.Department
GROUP BY A.PartID, A.BaseCost;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[OhioSteve]

PHV gave you a sound example of a query against a well-designed database However, it sounds like you want to know if you have a well-designed database.

My first question would be, are these valid assumptions:

1) You want a database to answer the question, "how much should we charge for each job?"
2) Each department's employees charge a given hourly rate. The hourly rate is identical for each employee from a given department.
3) Charges are billed on a per hour, per employee rate. For example, if a given department's employees charge $10 an hour, and five of them work seven hours apeice, then the charges for labour total 10*5*7=350.
4) In addition to labour charges, you also charge for goods. Each part ID has a specific, fixed price.
5) You charge sales tax on each bill.
6) You don't have any discount programs.
7) You know enough about Access to create tables and to find the relationships window.
8) You understand relational terminology and data types.

 

[tmcnutt]

Answers to your questions.

1 Yes.
2 Yes.
3 No, Total would be 35 hours. Same result, but on keep total for each department.
4 Yes, material cost.
5 No.
6 No.
7 Yes.
8 Yes.

I know a little about Access and database concepts such as normalization even though is seems a little confusing the deeper the levels go. Basically what I want is to be able to calculate to cost for a part which is comprised of material cost, department hours * department rate. The hours are totaled for each department and not by individual within each department. There can be mutliple departments. Here is sample data which is pulled from 3 tables.

PartID Material Department Hours Rate
7 1200 Mechanical 5 50
" " Electrical 3 45

I would like to know how to create one a query that will sum up the cost for a particular part. (1200 + (5 * 50) + 3 * 45)) = 1585

In my previous post where I mention the relationships, I understand why there is a 1 to many between table1 and table2 in that order, but not clear on 1 to many from table 3 (labor rates) to table 2(department hours).
Thanks for your help.

Tom

 

[PHV]

Tom, what about my suggestion ?

 

[OhioSteve]

There are many possible solutions. Here is a solution that is normalized, and is simple to understand. I treated labour as just another item that one can buy. If you have questions about my solution, just ask.

table name: items
itemID (text, pk)
itemDescription (text)
itemCost (currency)
itemUnitOfMeasure (text)
quantity (integer)
sample records:
"widget", "red widgets", $4, "bundle", 10
"labour1","labour- installation dept", $6, "hour", 200
"labour2","labour- technical dept", $7, "hour", 200
"pecans","tasty pecans",$1,"sack",100

table name: sales
salesID (autonumber, pk)
salesDate (date/time)
sample record:
1,"1/1/2001"

table name: salesDetails
salesID (integer, fk, one half of pk)
itemID (text, fk, one half of pk)
quantity (integer)
sample record:
1,"labour1",7
1, "widget", 3

example of sql for a sale:
SELECT
items.itemDescription,
items.itemUnitOfMeasure,
salesDetails.Quantity,
items.itemCost,
[quantity]*[itemCost] AS subtotal
FROM items INNER JOIN
(sales INNER JOIN salesDetails ON
sales.saleID = salesDetails.saleID) ON
items.itemID = salesDetails.itemID;

 

[tmcnutt]

PHV,

I just got around to trying it and it worked! Now I have to study so that I can understand it, I am not proficient in SQL. Thanks so much for the help. I would like to have it where I can enter the PartID or PartName as a parameter so that I can look at the cost for a particular part. I tried adding a where statement and removing the group by statement, but I get an error. Thanks again for your code example.

Tom

 

[PHV]

I would like to have it where I can enter the PartID or PartName as a parameter
Say you have a combo (cboPart) in a form (frmCalcCost):
SELECT A.PartID, A.BaseCost + SUM(B.Hours * C.Rate)
FROM (Table1 A INNER JOIN Table2 B ON A.PartID=B.PartID)
INNER JOIN Table3 C ON B.Department=C.Department
WHERE A.PartID=[Forms]![frmCalCost]![cboPart]
GROUP BY A.PartID, A.BaseCost;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tmcnutt]

OhioSteve,

What if my labor rate changes for the "Mechanical" Department? I thought having LaborRates as a separate table would allow me to change the rate in one place instead of searching thru a table for all instances of the department and having to change the rate in several records?

Tom

 

[tmcnutt]

I added the "Having" statement to the end so that I can look up cost for a particular part. Thank all of you for your input.

Tom

 

[PHV]

I added the "Having" statement
Why a having clause when you don't test aggreage value.
Try, like I suggested you in my previous post, the WHERE clause, much efficient as only the filtered data are aggregated.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[OhioSteve]

The primary key for items is itemID. So you would just have one record for each type of item.