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.
cnroubekAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kacorretiredCommented:
Hi Chris,

what kind of database you use? My answer depends on.

wbr

Janos
0
kacorretiredCommented:
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
0
cnroubekAuthor Commented:
The DataBase is access. What I need is the code itself. The general principle is clear to me.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

bogiboyCommented:
Using ADO:

cmdWH, cmdSL:TADODataSet;
cmdWH.CommandText:='SELECT TOP 1 * FROM WH WHERE Proc_code='+IntToStr(yourProdID)+' ORDER BY Expiry_Date DESC';
cmdSL:='SELECT Quantity FROM SL WHERE Prod_code='+IntToStr(yourProdID)
cmdWH.Open;
cmdSL.Open;
cmdWH.Edit;
cmdWH.FieldByName('Quantity').AsCurrency:=cmd.FieldByName('Quantity').AsCurrency -cmdSL.FieldByName('Quantity').AsCurrency;
cmdWH.Post;
cmdSL.Close;
cmdWH.Close;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kacorretiredCommented:
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
   
0
kacorretiredCommented:
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
0
kacorretiredCommented:
Hi cnroubek,

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

wbr

Janos
0
CleanupPingCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.