Calculations in Access

[Niels]

For my DBA I need to do a calculation between several tables.
The idea is to add a margin over the purchase costs to get your selling price. The margin is project specific and stored in table tblMargin. The purchase prices are product specific and stored in tblArticle.

If the article is a member of an product group - say materials - then the DB has to get the materials margin for that specific project from tblMargin and add that to the purchase costs. 1 Project can consist out of several products from different product groups. So for eacht group it needs to get the correct margin and then add it to the purchase costs.

Any ideas cause I'm clueless on this one...

 

[ecniv]

Left join and us the NZ function to return 0 for the null fields on the right side of the join.

eg

Select table1.blah1, nz(table2.blah1,0)+table1.blah1 as TotalCost from table1 left join table2 on table1.id=table2.id

Vince

 

[DB7]

niels, I agree with Vince.

As another option, could you use DLookUp.
Not knowing, how you plan to implement this process, I'll give a random example.

Say a combobox chooses the product group...

dim iMargin, iPurchase As integer

iMargin = DLookUp("txtMargin", "tblMargins", "txtProject = '" & cboProduct & "'")

iPurchase = DLookUp("txtPurchasePrice", "tblArticles", "txtProduct = '" & cboProduct & "'")

txtTotalPrice = iMargin + iPurchase

Something along these lines?

P.S. I'm not getting notified, when a reply is made to my post.
I have Instant email Notification selected.
Any ideas please?

 

[DB7]

Absolutely Pat,
but, just so we don't confuse Niels, I meant DLookup, as opposed to a Query....wondering, if this will give him the results he wanted, in a less cumbersome manner? Hope so!

but either way, Good Luck!