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