Query help please.

[hmessing]

I am hoping someone can enlighten me on how to do this. I was wanting to put this expression (see below) in a query field but it's too long to fit apparently. I need to figure out total cost of a package based on number of acres. There are a lot of options so my expression becomes too lengthy. I've got a table that is called SES Packages that has item as row, package name as a field and then the quantity of each item in the package, as well as a cost per item column. I then have a query that figures out the total cost of each item row in the packages, this query is called SES Package Costs. Depending on the number of acres of a property it could need more than one type of package.



I'm sure there is a way to do this with either another table/query and some sort of lookup maybe. Right now I just can't wrap my brain around how to accomplish what I'm wanting to do. There also may be larger acreage options later on. I also have 8 different package options (Bronze, Bronze+Flow, Gold, Gold+Flow, Silver, Silver+Flow, Platinum and Platinum+Flow)

I hope all this makes sense TIA!

Bronze: IIf([Properties]![IrrigatedArea]<=1,[SES Package Costs]![SES-B16Cost],IIf([Properties]![IrrigatedArea] Between 1.01 And 1.76,[SES Package Costs]![SES-B24Cost],IIf([Properties]![IrrigatedArea] Between 1.75 And 2.51,[SES Package Costs]![SES-B36Cost],IIf([Properties]![IrrigatedArea] Between 2.5 And 4.51,[SES Package Costs]![SES-B48Cost],IIf([Properties]![IrrigatedArea] Between 4.5 And 5.51,[SES Package Costs]![SES-B48Cost]+[SES Package Costs]![SES-B16Cost],IIf([Properties]![IrrigatedArea] Between 5.5 And 6.26,[SES Package Costs]![SES-B48Cost]+[SES Package Costs]![SES-B24Cost],IIf([Properties]![IrrigatedArea] Between 6.25 And 7.1,[SES Package Costs]![SES-B48Cost]+[SES Package Costs]![SES-B36Cost],IIf([Properties]![IrrigatedArea] Between 7.0 And 9.1,[SES Package Costs]![SES-B48Cost]+[SES Package Costs]![SES-B48Cost],IIf([Properties]![IrrigatedArea] Between 9.0 And 9.76,[SES Package Costs]![SES-B48Cost]+[SES Package Costs]![SES-B48Cost]+[SES Package Costs]![SES-B16Cost],IIf([Properties]![IrrigatedArea] Between 9.75 And 10.51,[SES Package Costs]![SES-B48Cost]+[SES Package Costs]![SES-B48Cost]+[SES Package Costs]![SES-B24Cost],IIf([Properties]![IrrigatedArea] Between 10.5 And 11.26,[SES Package Costs]![SES-B48Cost]+[SES Package Costs]![SES-B48Cost]+[SES Package Costs]![SES-B36Cost],IIf([Properties]![IrrigatedArea] Between 11.25 And 13.26,[SES Package Costs]![SES-B48Cost]+[SES Package Costs]![SES-B48Cost]+[SES Package Costs]![SES-B48Cost],IIf([Properties]![IrrigatedArea] Between 13.25 And 14.26,[SES Package Costs]![SES-B48Cost]+[SES Package Costs]![SES-B48Cost]+[SES Package Costs]![SES-B48Cost]+[SES Package Costs]![SES-B16Cost],IIf([Properties]![IrrigatedArea] Between 14.25 And 15.1,[SES Package Costs]![SES-B48Cost]+[SES Package Costs]![SES-B48Cost]+[SES Package Costs]![SES-B48Cost]+[SES Package Costs]![SES-B24Cost],IIf([Properties]![IrrigatedArea] Between 15.0 And 15.76,[SES Package Costs]![SES-B48Cost]+[SES Package Costs]![SES-B48Cost]+[SES Package Costs]![SES-B48Cost]+[SES Package Costs]![SES-B36Cost],IIf([Properties]![IrrigatedArea] Between 15.75 And 17.76,[SES Package Costs]![SES-B48Cost]+[SES Package Costs]![SES-B48Cost]+[SES Package Costs]![SES-B48Cost]+[SES Package Costs]![SES-B48Cost]))))))))))))))))

[June7]

Try:
Bronze: Switch([IrrigatedArea] <=1, [SES-B16Cost], [IrrigatedArea] <= 1.75, [SES-B24Cost], [IrrigatedArea] <= 2.50, [SES-B36Cost], [IrrigatedArea] <= 4.50, [SES-B48Cost], [IrrigatedArea] <= 5.5, [SES-B48Cost]+[SES-B16Cost], [IrrigatedArea] <= 6.25, [SES-B48Cost]+[SES-B24Cost], [IrrigatedArea] <= 7.0, [SES-B48Cost]+[SES-B36Cost], [IrrigatedArea] <= 9.0, [SES-B48Cost]+[SES-B48Cost], [IrrigatedArea] <= 9.75, [SES-B48Cost]+[SES-B48Cost]+[SES-B16Cost], [IrrigatedArea] <= 10.50, [SES-B48Cost]+[SES-B48Cost]+[SES-B24Cost], [IrrigatedArea] <= 11.25, [SES-B48Cost]+[SES-B48Cost]+[SES-B36Cost], [IrrigatedArea] <= 13.25, [SES-B48Cost]+[SES-B48Cost]+[SES-B48Cost], [IrrigatedArea] <= 14.25, [SES-B48Cost]+[SES-B48Cost]+[SES-B48Cost]+[SES-B16Cost], [IrrigatedArea] <= 15.0, [SES-B48Cost]+[SES-B48Cost]+[SES-B48Cost]+[SES-B24Cost], [IrrigatedArea] <= 15.75, [SES-B48Cost]+[SES-B48Cost]+[SES-B48Cost]+[SES-B36Cost], [IrrigatedArea] <= 17.75, [SES-B48Cost]+[SES-B48Cost]+[SES-B48Cost]+[SES-B48Cost])

[hmessing]

It tells me that expression is too complex In my query I've got the properties table and the ses package cost query if that helps any.

[June7]

Nothing wrong with the Switch() function syntax. I don't know your data structure so no idea why the query is saying too complex. Unless the expression is just too many characters. Limit of 1024 characters in a query grid cell.

[hmessing]

It's just too long. I took some of it out and it works fine. I am able to get all but 2 of the options I wanted. Thanks for the help. It will work for now.

[June7]

Shorter field names would probably get it within limit. Construct fields in query with alias names and use those in the expression, like:

SELECT [IrrigatedArea] AS IA, [SES-B16Cost] AS B16, ... Switch([IA]<=1, [B16], ...) AS Bronze FROM ...;

[hmessing]

Ah good idea! thanks!