Hello,

I am new to access and programming.

I have a combine file for sales and purchases. I want to allocate Locationwise productwise purchases to sales on First in First out basis and update the sales transaction with barcode and purchase unit price, also while allocating purchase if 2 records of sales found for 1 purchase then split the record and update barcode and purchase unit price respectively. Allocation should not be done for the sales prior/earlier to the first purchase date of the product.

Is this achievable in Access. I have to use Access as the line items are more than what excel provide.



File:

LOCATION PEODUCTCODE DOCDATE DOCTYPE SALEINVNO SALEINVVALUE SALEINVQTY SALEINVUNITPRICE PURINVNO PURINVVALUE PURINVQTY PURINVUNITRATE PURCHASEBARCODE
Loc A XXXX 9/15/2007 SALES 10024 100.00 10.00 10.00
Loc A YYY 9/15/2007 SALES 10024 200.00 10.00 20.00
Loc A ZZZZ 9/15/2007 SALES 10024 300.00 10.00 30.00
Loc A NNN 9/15/2007 SALES 10024 400.00 10.00 40.00
Loc A XXXX 9/16/2007 PURCHASE 90034 200.00 30.00 6.67 XXXX90034
Loc A YYY 9/16/2007 PURCHASE 90034 400.00 30.00 13.33 YYY90034
Loc A ZZZZ 9/16/2007 PURCHASE 90034 500.00 30.00 16.67 ZZZZ90034
Loc A NNN 9/16/2007 PURCHASE 90034 1000.00 30.00 33.33 NNN90034
Loc A XXXX 9/17/2007 SALES 10045 120.00 10.00 12.00
Loc A ZZZZ 9/18/2007 SALES 10047 400.00 10.00 40.00
Loc A XXXX 9/19/2007 PURCHASE 50045 400.00 55.00 7.27 XXXX50045
Loc A XXXX 9/20/2007 SALES 10049 310.00 30.00 10.33
Loc A NNN 9/21/2007 SALES 10053 2500.00 25.00 100.00
Loc A XXXX 9/22/2007 SALES 10053 500.00 45.00 11.11
Loc B XXXX 9/15/2007 SALES 2001 11.00 1.00 11.00
Loc B YYY 9/15/2007 SALES 2002 22.00 1.00 22.00
Loc B XXXX 9/15/2007 PURCHASE 2001 50.00 10.00 5.00 XXXX2001
Loc B YYY 9/16/2007 PURCHASE 2005 100.00 8.00 12.50 YYY2005
Loc B ZZZZ 9/16/2007 PURCHASE 2006 222.00 14.00 15.86 ZZZZ2006
Loc B NNN 9/16/2007 PURCHASE 2009 333.00 12.00 27.75 NNN2009
Loc B XXXX 9/17/2007 SALES 2021 100.00 10.00 10.00
Loc B YYY 9/18/2007 SALES 2024 140.00 8.00 17.50
Loc B YYY 9/19/2007 PURCHASE 26666 200.00 15.00 13.33 YYY26666
Loc B YYY 9/20/2007 SALES 10049 140.00 7.00 20.00
Loc B NNN 9/21/2007 SALES 10053 600.00 12.00 50.00


Thank you,
Nil