Unanswered: Query to compare multiple fields and update another field
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.
a db doesn't know that flip to smart is an upgrade
you are going to need to assign a rank value to each phone type
also you show what appears to be records, which is fine, but in a db there is no order to the records - - they can be sorted and resorted however one wishes....so there is no way for the db to know that the Smart purchased happened before or after the Flip purchase unless there is some sort of date or order field.
finally; and this is a biggie....it is challenging to make a value in a field that is dependent on a preceding record. not impossible - but requires advanced sql to do a join on oneself and conditional language... plus the dilemma is you show some 2 events, some 3 events - and so one presumes out there may be more than 3 events in some cases - - I think your best bet is to make a Report; and then group around the CustNo...and make a total per customer where your ranking values can be summed - - -
NTC - I have a table which lists the associated upgrades/downgrades/warranty exchanges/etc. Is there any way that I could utilize that with some ranking system?
The one thing I'm worried about is the assortment. I forgot to mention that I am setting up another field called shipment number (along with currently there being a date field). This will mark the first, second, third, etc. shipment for each customer. And yes, there will be some just 1 events and others up to 9 events. Any more ideas based on this new info?
assuming you have a date field - then you will be able to group on CustID and then sort by date - - which will result in the stacked ordering that you showed in your example post above - -- this is an important start....
Upgrade, Downgrade, Exchange - - are a subjective opinion. Unless you enter this explicitly during data entry the only way for a db to determine it is via math. So you would have to give each handset a numerical value.
Even with this you have a challenge - - you are looking for up/down of each event rather than overall. It can be done with sql - but it is an advanced task that I don't have at my fingertips.
NTC - I like the thought of assigning a numerical value for each headset. I have a field for first, second, third shipment (and can assign a rank value for that). I think the maximum number of headsets for a customer is about 10. So I guess I would be able to say that "flip" = 1, "smart" = 2, etc. and "first shipment" = 1, "second shipment" = 2, etc.....if first shipment = 1 AND flip AND second shipment = 2 AND smart, then upgrade? I would need to have an extremely large number of situations, but if that seems like the only possible way, then I will do that.