Complicated formula problems

[Gina]

I'm fairly new to Access, so please bear with me.
I'm trying to use Access to quote prices for a group of
metal enclosures, some of which share common parts.
We usually quote several different enclosures at once.
I have 5 db's joined: Quotes, Line Item, Assembly,
Component Assembly, and Components.
I currently have a report that will list all the
components necessary to build any group of enclosures.
The report is grouped on ComponentPartNo, and the detail
lists AssemblyPartNo, QtyQuoted, QtyPer(the quantity of
the components required for each enclosure), and
QtyQuotedxQtyPer (a calculated field saved in a query).
The first problem is that I want to sum QtyQuotedxQtyPer
in my query, and I keep getting that aggregate function
error message.
The second problem is much larger: based on the sum of
QtyQuotedxQtyPer (TotalReqd) AND the ComponentPartNo, I
want to write formulas that will return a dollar value.
I think it will look something like this:

If(MACompAssy.ComponentPartNo = "2029-GF-4-S0001" and
TotalReqd >= 2 and TotalReqd < 5, 245.73, If
(MACompAssy.ComponentPartNo = "2029-GF-4-S0001" and
TotalReqd >= 5 and TotalReqd < 10, 161.35, If
(MACompAssy.ComponentPartNo = "2029-GF-4-S0001" and
TotalReqd >= 10 and TotalReqd < 15, 133.04, If
(MACompAssy.ComponentPartNo = "2029-GF-4-S0001" and
TotalReqd >= 15 and TotalReqd < 20, 123.72, If
(MACompAssy.ComponentPartNo = "2029-GF-4-S0001" and
TotalReqd = 20, 119.1, 0)))))

I have about 35 Components that I would need to write
formulas like the above for, and then somehow I would need
to sum those 30 formula fields to return a total price
(and ideally I could show my results in a report view).

Can someone give me any insight as to how I could
accomplish this, if at all? Any and all suggestions would
be greatly appreciated!

 

[Larry Linson]

Sounds like classic "bill of materials processing" (which is a science of
its own) and that is far too broad for a newsgroup answer.

Searching Access newsgroups at http://groups.google.com for "bill of
materials" and searching the http://msdn.microsoft.com and
http://support.microsoft.com for the same might get you some useful
information.

Larry Linson
Microsoft Access MVP