Originally Posted by
jzwp11
Technically speaking, if an item has more than 1 associated cost, that describes a one-to-many relationship, so the two costs should be separate records in a related table. I'll ignore that for now. You could use an expression
SELECT ..., IIF(Cost1>=Cost2,Cost1,Cost2) AS ItemCost, (Qty * ItemCost) as TotalItemCost
Now since you say that the Revenue field is text, you cannot do (Qty * Revenue) without first converting the revenue to a number. (cannot do mathmetical computations on a text value).
Now as to scrubbing the letters from some alphanumeric values in the OrderID field, there are several text manipulation functions available within Access. So is there a defined pattern? Obviously the number of letters at the beginning of the string can vary. Can letters appear within the string? At the end of the string? Is the numeric portion always the last six characters?