Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820

    Unanswered: How to match a date with a close date

    i've got to create a DB that will compare expected deliveries with what was really deleviered
    the data consits of 2 tables

    POSCH(ID,SCHDATE,SCHQTY)
    POREC(ID,RECDATE,RECQTY)

    what i've tried is taking a running total of the SCHQTY and RECQTY
    which are TOTSCH & TOTREC respectivly

    i've then run the following query to match the RECDATE to the SCHDATE

    SELECT POREC.ID, POSCH.SCHDATE, Min(POREC.RECDATE) AS RECDATE
    FROM POREC INNER JOIN POSCH ON POREC.ID=POSCH.ID
    WHERE (((POREC.TOTREC)>=[totsch]))
    GROUP BY POREC.ID, POSCH.SCHDATE;

    which works fine... then my boss metions that a partial delivery counts as a delivery meaning that my running totals method doesn't work

    so i need to be able to match the dates to each other some other way now here's the main snag a single SCHedualed request may match to multiple RECiepts ie the shipped the goods in over multiple deliveries eg we order 50000 KG of plastic and get two shipments of 24000 KG, and the amount ot time they're late/early by can varry wildly

    i've attached a dump of the the raw data in case that helps

    any input welcome as i've ran out of ideas
    Attached Files Attached Files
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  2. #2
    Join Date
    Feb 2002
    Posts
    403
    A quick look and I think you could consider an alternative approach, using the ID instead.

    You could set up a form or report that shows the original order quantity, total received to date (being today), and then a sub form showing delivery details.

    This should overcome the date comparison stuff and should prove more accurate for what you are trying to achieve.

  3. #3
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by dynamictiger
    A quick look and I think you could consider an alternative approach, using the ID instead.

    You could set up a form or report that shows the original order quantity, total received to date (being today), and then a sub form showing delivery details.

    This should overcome the date comparison stuff and should prove more accurate for what you are trying to achieve.
    i'm not sure i see what your getting at, how will that link the dates together for the further calcutaions i need to do? theres also the fact that the any scheduled that was received as multiple delivery will throw your method off won't it?
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  4. #4
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Come on someone must be able to tell me how to do this if it's possible
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    okay i seem to have found a fix
    i've increase the deliveries by 10% to screen out small errors which seems to be doing the job
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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