Returning different fields based on criteria

[Guest]

I'm reposting this because I put in the wrong forum.

I'm looking to write a query to return the amount we should be billing our
customers for freight.

I have a table in which each record is a package. Each record contains our
job number, a normal freight amount, a discounted freight amount and a
package weight, along with other stuff. Customers qualify for the discounted
rate if the entire shipment is >= 150 pounds.

I need to come up with a query that returns the sum of the normal freight
amounts if the sum of weights is less than 150 pounds, or it returns the sum
of the discounted rates if the sum of weights is >= 150 pounds. I've got the
grouping and summing working OK, but I'm pretty clueless on how to set up
that condition.

Thanks in advance,
Steve

 

[Marshall Barton]

I'm reposting this because I put in the wrong forum.

I'm looking to write a query to return the amount we should be billing our
customers for freight.

I have a table in which each record is a package. Each record contains our
job number, a normal freight amount, a discounted freight amount and a
package weight, along with other stuff. Customers qualify for the discounted
rate if the entire shipment is >= 150 pounds.

I need to come up with a query that returns the sum of the normal freight
amounts if the sum of weights is less than 150 pounds, or it returns the sum
of the discounted rates if the sum of weights is >= 150 pounds. I've got the
grouping and summing working OK, but I'm pretty clueless on how to set up
that condition.

I can't tell for sure without seeing the query, but I think
you can use a calculated field along these lines:

IIf(sum(weight) > 150, sum(discountedfreight),
sum(normalfreight))