Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2009
    Posts
    15

    Unanswered: Query to compare multiple fields and update another field

    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

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    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 - - -

  3. #3
    Join Date
    Dec 2009
    Posts
    15
    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?

  4. #4
    Join Date
    Oct 2009
    Posts
    340
    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.

  5. #5
    Join Date
    Dec 2009
    Posts
    15
    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.

  6. #6
    Join Date
    Dec 2009
    Location
    Antwerp (Belgium)
    Posts
    33
    what ntc is referring to is something in the area of :

    Code:
    select x1.Customer,x1.Shipnr,x2.Shipnr,x1.rank,x2.rank
      from Shipments x1,Shipments x2
      where x1.customer = x2.customer
      and x1.rank>x2.rank
    assuming shipments is some kind of view that "knows" the rank of the product that was shipped.

    This sql will give you all the shipment combinations for a customer.

    Hope this helps.

  7. #7
    Join Date
    Dec 2009
    Posts
    15
    Marc Grajower - I'm not sure I follow what you are saying with "x1". Can you please give me some more ideas on what you are trying to convey? Thanks!

  8. #8
    Join Date
    Dec 2009
    Location
    Antwerp (Belgium)
    Posts
    33
    Sorry, that code should have read :

    select x1.Customer,x1.Shipnr,x2.Shipnr,x1.rank,x2.rank
    from Shipments as x1,Shipments as x2
    where x1.customer = x2.customer
    and x1.rank>x2.rank

  9. #9
    Join Date
    Dec 2009
    Posts
    15
    What is "x1" and "x2"...tables? Just a placeholder?

  10. #10
    Join Date
    Dec 2009
    Location
    Antwerp (Belgium)
    Posts
    33
    well ... when you use the same table twice in a sql you need to "address" them. In the query builder access assigns them for you.

  11. #11
    Join Date
    Dec 2009
    Posts
    15
    OK - I follow you know. Sorry, wasn't sure what you meant by that. Thanks, and I'll let you know how it turns out.

  12. #12
    Join Date
    Dec 2009
    Location
    Antwerp (Belgium)
    Posts
    33
    No problem. Glad it's clear now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •