Domain aggregate

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have three tables, one with components, one with numbers of components
needed for a project, and one with price break information for the
components, in a one to many relationship with the component table. I have a
query to yield all of the components I need for a given number of products.
What I can't figure out how to do is then use a domain aggregate function to
then yield a unique record for each component incorporating price break
information. In other words, I need to have a field in the query which
yields the correct price for the number of components I need. How do I go
about this?
 
In
Wavequation said:
I have three tables, one with components, one with numbers of
components needed for a project, and one with price break information
for the components, in a one to many relationship with the component
table. I have a query to yield all of the components I need for a
given number of products. What I can't figure out how to do is then
use a domain aggregate function to then yield a unique record for
each component incorporating price break information. In other
words, I need to have a field in the query which yields the correct
price for the number of components I need. How do I go about this?
Click to expand...

What does your price break table look like? Do you have something like
this:

ComponentID
MinOrderQuantity
Price

?

Or maybe:

ComponentID
MaxOrderQuantity
Price

?

Or maybe:

ComponentID
FromOrderQuantity
ToOrderQuantity
Price

?

Or do you have something like one of those setups, but with a discount
amount/percentage instead of the raw price?
 
The table structure is as such:
Index: An autonumber field I include with every table. It is the primary key.
Component Number: a link to the primary key of the component table
Number: the number of components at which the price break occurs
Price: the per unit price for the specified component

Therefore, say I have component # 347. If it has three price breaks, the
table would look like this?

Component # Number Price
347 1 $5.00
347 10 $4.75
347 100 $4.50

Thus, if I want to order 17 components, I should have a field in my query
indicating a price of $4.75.
 
In
Wavequation said:
The table structure is as such:
Index: An autonumber field I include with every table. It is the
primary key. Component Number: a link to the primary key of the
component table
Number: the number of components at which the price break occurs
Price: the per unit price for the specified component

Therefore, say I have component # 347. If it has three price breaks,
the table would look like this?

Component # Number Price
347 1 $5.00
347 10 $4.75
347 100 $4.50

Thus, if I want to order 17 components, I should have a field in my
query indicating a price of $4.75.
Click to expand...

I'll assume that you have a table or query that gives you the total
number of each component required for a given project/product; that is,
fields like these:

ProjectID
ComponentNumber
NumberRequired

Suppose that table/query is named "ProjectComponents". You could build
a query like this:

SELECT
C.OrderID,
C.ComponentNumber,
C.NumberRequired,
(SELECT Min(Price) FROM PriceBreaks P
WHERE P.ComponentNumber = C.ComponentNumber
AND P.Number <= C.NumberRequired
) AS Price
FROM ProjectComponents C;

I believe that would work for you.
 
Back
Top