Query to compare multiple fields and update another field

[turdfergy]

Hi everyone,



I just joined the forum and look forward to some good feedback from everyone (I have read this forum a lot for ideas/help before and am an intermediate Access/SQL user).

Here's some basic information and I'll put an example below. I work for a cell phone carrier and working on some analysis to see how many customers have upgraded/downgraded their device. So I need to see if the customer has received multiple shipments, if the first device shipped is a "Flip Phone" and the second one shipped is a "Smartphone", I need to update another column to say "Upgrade". If it is a "flip" to "flip" with "exchange" as the order type, this is an "exchange" and "smartphone" to "flip" is a "downgrade".

So in a nutshell, I need to compare to see if the customer numbers are the same, if they are, check the order type for "warranty exchange", check first shipment device and compare to second shipment device, then update another field.

As a bonus, I have a table that has all the possible combinations of upgrades/downgrades/exchanges that I would like to utilize, but this is an "extra bonus" if anyone would know how to utilize this. Please let me know if more details, examples, etc. are needed and thanks for all the help in advance.

CustNo---PhoneType---OrderType---ToBeUpdatedField
001---Flip---Direct---Direct Order
001---Smart---Direct---Upgrade

123---Smart---Direct---Direct Order
123---Flip---Direct---Downgrade

456---Flip---Regular---Direct Order
456---Flip---Exchange---Exchange
456---Flip---Regular---Direct Order

789---Smart---Regular---Direct Order
789---Smart---Exchange---Exchange
789---Smart---Regular---Direct Order

[NassauBob]

Neat!

Well, do you have the ability to alter the data structure? If you do, you could assign a numeric value to the types of phone, i.e. flip = 1, and smart = 2, then compare the records that way (subtract the new from the old), and have the result populate a new field, called something like upgradeVal. Then, that new field should contain only three possible values: a positive number, a zero, or a negative number! For instance, if I originally had a smartphone, but I downgraded to a flip, the the result value would be positive, right? Because 2(smart) - 1(flip) equals 1(positive). If I had simply traded my smartphone for another, the result would be zero, and a flip for a smart would equal -1, a negative value!

In another small query, you could make three records, with two columns:

tblUpgradeVal
-1 upgrade
0 exchange
1 downgrade

Does this all make sense? It does inside my head! LOL

Now, that only gets you a quick and easy '*grade' type. You may have to create a subquery to only include those customers with multiple records, and with 'warranty exchange' in their order history.

Shane