By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

Calculating In Access

P: n/a
I have serious Access problems so please be gentle.

I have one table that stores the quantities and
net sales for thousands of Products.

I have another table that has fixed costs values
on a country level.

I need to apply a percentage (based on net sales) of
the fixed costs across the products.

This is no problem in Excel, how about in Access.


Product Table

Product Country Qty Sold Net Sales
Trash Can A US 1 5.00
Trash Can B GB 2 10.00
Paper Sack A US 3 3.00
Paper Sack B GB 4 4.00

Fixed Costs Table

Country Fixed Costs
US 20.00
GB 15.00

In Need Of:
Product Country Qty Sold Net Sales Fixed Cost
Trash Can A US 1 5.00 12.50
Trash Can B GB 2 10.00 14.29
Paper Sack A US 3 3.00 7.50
Paper Sack B GB 4 4.00 5.71

Formula = (Net Sales / (Total Country Net Sales))
* Fixed Cost

Any suggestions on how to do this in Access without
first doing it in Excel and importing?

Thanks In Advance (TIA)
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply

P: n/a
First, you need to create a query that rolls up the total NetSales by Country:

SELECT Country, SUM([Net Sales]) As NetTotal
FROM [Product Table]
GROUP BY Country

Save that as qryNetByCountry

Then create a query that joins the 2 tables and the new query together on on
the country field:

SELECT P.Product, P.Country, P.Qty, P.Net, (P.Net/N.NetTotal)*F.FixedCost As
FROM qryNetByCountry N INNER JOIN ([Product Table] P INNER JOIN [Fixed Cost
Table] F ON P.Country = F.Country) ON N.Country = F.Country

DISCLAIMER: I have not tested any this....use only as a guide. The improtant
thing to gather are:

1) Create the qryNetByCountry query
2) Join that query along with the other tables
3) Do your calculation.
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.