Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Same Line Updating (A2K)

    This is one of those, if you stare at it long enough, the solution will completely evade you type problems:

    Purchase Order Reconciliation Scenario:

    A part has been ordered generating a PO detail line.
    It needs to be reconciled on receipt of Parts with respect to a BackOrder situation.
    It can happen that only a part of that order is received, so that it is necessary to keep the SINGLE record updated as to what is still on BackOrder.

    PONo: 1
    PartNo: 1
    QtyOrdered: 100
    QtyRcvd: 20

    So:

    QtyOrdered-QtyRcvd = BackOrdered: 80
    100-20 = 80

    Later on, more of the same PartNo for the same PONo is received and the SAME DETAIL LINE is being accessed for reconciliation purposes:

    PONo: 1
    PartNo: 1
    QtyOrdered: 100 (Still the original Qty)
    QtyRcvd: 40 ( The new qty received to be entered in the SAME FIELD which had previously contained 20)

    So using the original formula:

    QtyOrdered-QtyRcvd = BackOrdered: 60
    100-40 = 60

    Which of course is an incorrect BackOrdered because it does not reflect the first qty of 20 received.

    So, says I blindly, is there a formula where I can satisfy the above scenario of updating the BackOrdered within the SAME detail line?

    Thanks in advance for any help with this,
    Cheers,
    Andy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Same Line Updating (A2K)

    You probably don't want to hear this, but in my opinion, the QtyRcvd field does not belong in the PO detail record. The best solution would be to have a table where each QryRcvd for a part / order is in a separate record. That way, you can easily calculate how many are still on back order, and you'll have a history of the QtyRcvd values.

    A less dependable alternative would be to use a BackOrder field in the PO detail record instead of a QtyRcvd field. To update this field, use an unbound QtyRecvd text box on the form, with a command button to subtract the QtyRcvd value entered by the user from BackOrder. The problem is that you won't have a history of the various QtyRcvd values, so it is impossible to verify the correctness of BackOrder.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Same Line Updating (A2K)

    Hans,

    Actually, your answer was more than appropriate in that it confirmed another avenue I was going down that was going to allow me to increment the inventory OnHand information as well as satisfy the PO needs referred to. I was approaching that as a second step, but I'll hopefully be able to do this all in one fell swoop. The QtyRcvd field as you allude to, already exists. All I have to do now is get them to get along with each other. I'll let you know how this went sometime in the hopefully not too distant future. I'll probably come back to you with some more questions while getting there.

    Thanks again,
    Cheers,
    Andy

  4. #4
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Same Line Updating (A2K)

    Hans,

    The following does pertain to my original problem, but is so fundamental and basic I almost hesitate to ask the question.

    I
    Cheers,
    Andy

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Same Line Updating (A2K)

    I assume that one PartNo can belong to several PONo's, so if you join the two, Access has no way of knowing which QtyRcvd belongs to which PONo. If a QtyRcvd is always for a specific PONo, the second table (query?) should contain PONo as well as MstrPartNo. Otherwise, don't combine them in one query.

Posting Permissions

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