asked on

Sales and Warehouse expiry dates

I have 2 tables Warehouse (WH) and Sales (SL)
WH has fields: Prod_Code, Expiry_Date & Quantity
SL has fields: Prod_Code, Quantity.

I am trying to achieve the following: For every sale that I make, I want the quantity sold to be subtracted from the WH from the same Prod_Code which will expire sooner. This way, I follow FIFO.

Can someone PLEASE help me with this??
I am a roockie and I am trying to make this work!

Thank you in advnace
Chris.

SOLUTION

kacor

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kacor

In general:
1. You have tomake a view of WH indexed by date (ascending)
2. You subtract the needed quantity from the first item. If it won't be sufficient you subtrract the rest from the second etc

Janos

cnroubek

ASKER

The DataBase is access. What I need is the code itself. The general principle is clear to me.

ASKER CERTIFIED SOLUTION

bogiboy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kacor

Hi cnroubek,

to do are the followings:

1. Create ODBC connection to your file (If you need I'll tell you step by step)
2. Database structure is modified as follows.
added to WH
WH_ID Counter keyed
QuantitySold (similar as Quantity)
added to Sales
Checked Boolean
3. WH indexed by Expiry_Date, so the data appear according to Expiry_Date.
4. I placed the following components on the form (you can place them on a separate DataModule)
tblWH (TTable) srcWH (TDataSource)
tblSales srcSales
two DataGrids TButton
5. Create Master/Detail connection between the tables. Master is Sales. For tblWH select: MasterSource=srcSales, MasterFields=Prod_Code, so in the WH table will appear only the posts which have the prod_code as in the selected Sales record.
6. The OnClick procedure of the Button is as follows:

procedure TForm1.Button1Click(Sender: TObject);
var
Rest, QtySold: Integer;
begin
//if the selected item was processed Checked is true
if not(tblSales.FieldByName('Checked').AsBoolean) then
begin
tblWH.Edit;
//if the needed quantity is greater then the last item
if tblSales.FieldByName('Quantity').AsInteger >
tblWH.FieldByName('Quantity').AsInteger -
tblWH.FieldByName('QuantitySold').AsInteger
then
begin
Rest := tblSales.FieldByName('Quantity').AsInteger -
tblWH.FieldByName('Quantity').AsInteger +
tblWH.FieldByName('QuantitySold').AsInteger;
tblWH.FieldByName('QuantitySold').AsInteger :=
tblWH.FieldByName('Quantity').AsInteger;
end
else
begin
Rest := 0;
tblWH.FieldByName('QuantitySold').AsInteger :=
tblWH.FieldByName('QuantitySold').AsInteger +
tblSales.FieldByName('Quantity').AsInteger;
end;
tblWH.Post;
tblSales.Edit;
tblSales.FieldByName('Checked').AsBoolean := True;
tblSales.Post;
end;
//then continue with the Rest
end;

I hope this helps

wbr

Janos

kacor

Remarks:
1. Create ODBC connection to your DataBase
3. WH indexed by Expiry_Date, so the data appear according to Expiry_Date ascending (in Access).

To be able to edit a dataset (table in this case) it must be set in edit mode then the changes must be posted

wbr
Janos

kacor

Hi cnroubek,

if you feel so that you've got the needed question from somebody please accept it

wbr

Janos

CleanupPing

cnroubek:
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.