Criteria using a formulated field

[Sed]

OK, Your DB gave me the same error. In the design veiw of your query put this criteria in the last column CPM.... Between 1 and 3

 

[Sed]

You guys see what I'm talking about?

 

[dkinley]

Sed, I got this to work nesting the queries as I outlined it previously. Here it is ...

The first query "A" is ...

SELECT Table1.[Total Shipment Charge], Table1.FSC, Table1.Distance, IIf(IsNull([Allocated Overwidth Charge Cost])=True,0,[Allocated Overwidth Charge Cost])+IIf(IsNull([Allocated Stop Charge Cost])=True,0,[Allocated Stop Charge Cost])+IIf(IsNull([Allocated Tarp Charge Cost])=True,0,[Allocated Tarp Charge Cost]) AS ACC
FROM Table1;

and the second one is

SELECT A.[Total Shipment Charge], A.FSC, A.Distance, (((([Total Shipment Charge])-[ACC])-([FSC]))/([Distance])) AS CPM_Less_Fsc_Acc
FROM A
WHERE ((((((([Total Shipment Charge])-[ACC])-([FSC]))/([Distance]))) Between 1 And 3));

-dK

 

[Sed]

OK, Let me try it.

 

[Medea]

I'm confused!!!! Why are you guys getting an error and I'm not????? Any ideas DK?

 

[Medea]

I got the error too!

 

[dkinley]

Nope. Except I am opening your demo with AC'07. I am still researching original issue because I am not sure if nesting these is the correct path to take.

If I enter a Between or a > or any criteria, it prompts me for ACC.

All I do know that nesting queries, especially with IFFs piled on IFFs, spells BAD OPTIMIZATION.

-dK

 

[Medea]

Too cold to ski tonight....I will play with this more when I get home.

 

[Sed]

DK,

I'm not sure if this work on mind, I've already have a select, where on my qeuery. for Example, this is my select statement that I currently have.

SELECT TMS_Cleaned_Data.Week, TMS_Cleaned_Data.Equipment, TMS_Cleaned_Data.[Origin City], TMS_Cleaned_Data.[Destination City], TMS_Cleaned_Data.[Trucking Mode], TMS_Cleaned_Data.[Load Weight], TMS_Cleaned_Data.[Load Distance], TMS_Cleaned_Data.[Total Shipment Charge], TMS_Cleaned_Data.[Allocated Total Charges], TMS_Cleaned_Data.[Allocated Other Charges], TMS_Cleaned_Data.[Allocated Fuel Cost], TMS_Cleaned_Data.[Allocated Base Rate], TMS_Cleaned_Data.[Consolidation Savings], TMS_Cleaned_Data.[Allocated Overwidth Charge Cost], TMS_Cleaned_Data.[Allocated Stop Charge Cost], TMS_Cleaned_Data.[Allocated Tarp Charge Cost], TMS_Cleaned_Data.[Base Rate], TMS_Cleaned_Data.[Destination Country], TMS_Cleaned_Data.[Load Created Date], TMS_Cleaned_Data.[Destination Location], TMS_Cleaned_Data.[Destination PostalCode], TMS_Cleaned_Data.[Destination State], TMS_Cleaned_Data.[Destination Street], TMS_Cleaned_Data.Distance, TMS_Cleaned_Data.[Entered By], TMS_Cleaned_Data.[Load Carrier], TMS_Cleaned_Data.[Load Base Rate], TMS_Cleaned_Data.[Load No#], TMS_Cleaned_Data.[Load Extra Charges], TMS_Cleaned_Data.[Load Total Charge], TMS_Cleaned_Data.[Load Transit Time], TMS_Cleaned_Data.[Load Volume], TMS_Cleaned_Data.[Origin Company Name], TMS_Cleaned_Data.[Origin Country], TMS_Cleaned_Data.[Origin Location], TMS_Cleaned_Data.[Origin State], TMS_Cleaned_Data.[Origin Street], TMS_Cleaned_Data.[Origin Zip], TMS_Cleaned_Data.[Other Charges], TMS_Cleaned_Data.[Shipment carrier], TMS_Cleaned_Data.[Shipment No#], TMS_Cleaned_Data.[Shipment Volume], TMS_Cleaned_Data.[Shipment Weight], TMS_Cleaned_Data.Year, TMS_Cleaned_Data.Month, ([Shipment Weight])/100 AS CWT, ([Total Shipment Charge])/([Distance]) AS [Cost/Mile], ([Total Shipment Charge])/([CWT]) AS [Cost/CWT], ([Total Shipment Charge])/([CWT])/([Distance]) AS [Cost/CWT/Mile], ([Total Shipment Charge]-[ACC]-[Allocated Fuel Cost])/([Distance]) AS CPM_Less_Fsc_Acc, IIf(IsNull([Allocated Overwidth Charge Cost]),0,[Allocated Overwidth Charge Cost])+(IIf(IsNull([Allocated Stop Charge Cost]),0,[Allocated Stop Charge Cost])+(IIf(IsNull([Allocated Tarp Charge Cost]),0,[Allocated Tarp Charge Cost]))) AS ACC, ([Total Shipment Charge]-[ACC]-[Allocated Fuel Cost])/([CWT]) AS Cost_CWT, ([Total Shipment Charge]-[ACC]-[Allocated Fuel Cost])/([Distance])/([CWT]) AS Cost_CWT_Miles_Less_Fsc_Acc, ([Total Shipment Charge]-[ACC]-[Allocated Fuel Cost]) AS LineHaul
FROM TMS_Cleaned_Data
WHERE (((TMS_Cleaned_Data.[Destination City]) In ("BALTIMORE","BRUNSWICK","Calumet City","HESSTON","JACKSON","OXNARD","SAVANNAH","BELOIT","Charleston","East Chicago","Chicago","EDGEWOOD","Gibson City","HOUSTON","LA PORTE","SIOUX CITY","Dupont")) AND ((TMS_Cleaned_Data.[Trucking Mode])<>("Premium")) AND ((TMS_Cleaned_Data.[Total Shipment Charge])>0) AND ((TMS_Cleaned_Data.[Allocated Fuel Cost])>0) AND ((TMS_Cleaned_Data.Distance)>0) AND ((TMS_Cleaned_Data.[Load Carrier]) Not In ("Beloit Will Call","Sunflower Fleet")) AND ((TMS_Cleaned_Data.[Shipment carrier]) Not In ("Beloit Will Call","Sunflower Fleet")) AND ((TMS_Cleaned_Data.[Shipment Weight])>50))
WITH OWNERACCESS OPTION;

 

[Medea]

Occured to me on the way home....create a second query based on your first query. This works. Put your parameters on the 2nd query.

DK...you agree?

Trish

 

[dkinley]

Thats what I was referring to in post #23. In post #27 - I stated that I am not sure if this is the best way. If there are a staggering number of records, it could take awhile for the query to process.

Still looking ...
-dK

 

[Sed]

You guys are the best. thank you very much!!!