Calculating In Access

[Heath]

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.

Example.

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:
calculated
field
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)

[DCM Fan]

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.F ixedCost As
NetFixedCost
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.