Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Sales and Warehouse expiry dates

Posted on 2003-03-17
10
Medium Priority
?
341 Views
Last Modified: 2010-04-04
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.
0
Comment
Question by:cnroubek
8 Comments
 
LVL 10

Assisted Solution

by:kacor
kacor earned 100 total points
ID: 8157758
Hi Chris,

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

wbr

Janos
0
 
LVL 10

Expert Comment

by:kacor
ID: 8157911
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
 

Author Comment

by:cnroubek
ID: 8158410
The DataBase is access. What I need is the code itself. The general principle is clear to me.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Accepted Solution

by:
bogiboy earned 100 total points
ID: 8161165
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
 
LVL 10

Expert Comment

by:kacor
ID: 8164520
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
 
LVL 10

Expert Comment

by:kacor
ID: 8164565
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
 
LVL 10

Expert Comment

by:kacor
ID: 8208840
Hi cnroubek,

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

wbr

Janos
0
 

Expert Comment

by:CleanupPing
ID: 9316731
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
This is an update to some code that someone else posted on Experts Exchange. It is an alternate approach, I think a little easier to use, & makes sure that things like the Task Bar will update.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question