Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2012
    Posts
    3

    Unanswered: Match Table Fields

    Before

    The following table shows how the Credit transaction (ID 1) has a corresponding offsetting entry in (ID 3).

    ID-----Cost Center----Line of Business---Debit------Credit-----Event ID
    1------10056----------Retail-------------$5,000----$0.00------
    2------10799----------Retail-------------$1,450----$0.00------
    3------10056----------Retail-------------$0.00-----$5,000-----
    4------10022----------Commercial--------$3,000----$0.00-----


    Every transaction comes into the table as either a Debit or a Credit. Each transaction has a unique ID and it is assigned to a Cost Center and a Line of Business.

    Our goal is to produce a query that will extract transactions that maybe related. In other words, to find a debit amount with an offsetting credit amount in order to assign them an Event ID number in another column. In order to make a match, both transactions (the Credit and the Debit) must have the same Cost Center, belong to the same Line of Business and both the Debit and the Credit amount must be the same value.

    After

    The query results only shows transactions that are related.

    ID-----Cost Center----Line of Business---Debit------Credit-----Event ID
    1------10056----------Retail-------------$5,000----$0.00------
    3------10056----------Retail-------------$0.00-----$5,000-----

    Thanks for your help!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think its possible to retrieve rows as per you specifcation
    however I'd be very very wary about assigning an Event ID automatically, unless you don't care beyond the simple statement X & Y match

    to get the data do a join on the same table

    eg
    Code:
    SELECT C.ID, C.[Cost Center], C.[Line Of Business], C.Credit, D.ID
    from MyTable as C
    join MyTable as D on D.[Cost Center] = C.[Cost Center]
    AND D.[Line Of Business] = C.[Line Of Business]
    AND D.Debit = C.Credit
    WHERE is NULL(C.[Event ID]) and is null(D.[Event ID]
    ...oh and strong suggestion ditch the spaces between column names (and table names) if you haven't already

    you could put in other terms (say where the Credit record ID is within n of the Debit record ID)

    dump the results to a report or form and then let the user marry the two up

    if you only ever got a single match then you could assign you event ID using say a composite of the ID's eg 1.3 or 000001.000003 or you coudl encode the number using soem other form eg hexadecimal or just post the Credit row ID as the Debit Row's Event ID and vice versa
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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