Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2006
    Posts
    2

    Unanswered: Subtract QtyAvailable from previous record

    How can I subtract the QtyNeeded in the current record from the QtyAvailable in a previous record containing the same Part# using a query giving me the balance in the current record?

    ParentPart*****Part#*******QtyNeeded*****QtyAvaila ble
    2936 ******** 1269 ********* 12 ************15
    6387 ******** 1269 ********* 13 ***********-10
    7899 ******** 1269 ********* 20 ***********-30


    Thanks in advance for help on this.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You can't. Not unless you have a method of tracking record sequence that you're not telling us about.

    For all practical intents and purposes, the database is unaware of physical sequence within a table...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Mar 2006
    Posts
    33
    Provided Answers: 1
    the problem here is to want to track the last record in the database having the same part#

    although its better to have a table seperate with the part# as the primary key and update the sale or qty ordered maybe in the afterupdate event
    you can then easily refer to it using the dlookup() any time.

    alternatively in this case you can make an aggregate query with the LASTOF() function and use the dlookup() on that.

  4. #4
    Join Date
    Mar 2006
    Posts
    2
    Thanks I'll give that a try

Posting Permissions

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