Results 1 to 2 of 2

Thread: Access Help

  1. #1
    Join Date
    Jan 2014
    Posts
    3

    Unanswered: Access Help

    I'm new to using access and I have been given what I thought was a minor project. Currently I have data importing into Access from our billing system - this data comes across with either 1 or 2 lines of data. Line one is always the "service" portion of the billing whereas line 2 is a quantity based charge.

    Incoming Data looks like this
    CUSTOMER_NO Quantity Charge CODE## TICKET
    189601 1 $100.00 40C 579701
    189601 8.07 $807.00 DTN 579701

    Outgoing data request:

    CUSTOMER_NO Service Weight Charge Total
    189601 $100.00 8.07 $807.00 $907.00

    Any advice on how I would go about combining these rows? There are two "weight" based codes - the DTN you see above as well as TNY. I assume the solution would involve some if, then statement else the weight/charge = 0.

    Thanks in advance for any help.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If you are certain about `its only one or two lines per customer and the weighted riw is either dtn or tny then you could do what you want through a JOIN. Im guessing you are probably going to be using the query designer...
    So place the table containing your incoming data on the query designer TWICE.
    Drag and drop the customer no from one table to the other
    Right click the resulting line (the relationship) and define the realtionship as all rows from the first table and all that match from the other.

    Drag and drop the columns you want from each table

    In the first table define the code criteria as
    Code:
    NOT IN("DTN", "TNY")
    in the second table define the code criteria as above EXCEPT dont use the NOT

    Then save and run the query.
    Bear in mind it may take a few iterations to get the query right
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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