Hi all,

Good day.

I have working on Store Management System (SMS) and in the last stage of 
completing it. I have developed all the basic needs such as Receiving, 
Issuance and Reporting. Every stock item received will be given a unique 
GRN No and every stock item issued will be given a unique IV No.

I find really hard to generate report to show the items in FIFO method 
with its respective purchase price.

If anyone out there have developed such a system using FIFO method, please 
advice me how can I get this done.

Thanks,
Maha

I haven't built a system like that before, but if you have the time to go
into more detail about your requirements I can try to help from a database
design / SQL code perspective...



-----Original Message-----
From: Maha Arupputhan Pappan [mailto:mahap@p...]
Sent: Tuesday, October 22, 2002 2:44 AM
To: Access
Subject: [access] Store Management System - Some Advice


Hi all,

Good day.

I have working on Store Management System (SMS) and in the last stage of 
completing it. I have developed all the basic needs such as Receiving, 
Issuance and Reporting. Every stock item received will be given a unique 
GRN No and every stock item issued will be given a unique IV No.

I find really hard to generate report to show the items in FIFO method 
with its respective purchase price.

If anyone out there have developed such a system using FIFO method, please 
advice me how can I get this done.

Thanks,
Maha

Thanks Dave,

I appreciate your help. Let me be specific.

I wish to make the applicatin to run in FIFO method. That will be when I 
purchase an article, I must update the stock level and then I issue the 
article out of the store. When issuing the article, the system must apply 
the FIFO method.

Example, I purchased 5 pcs of ART1 for the price of $5 each on 23-Oct-02. 
I update the stock level upon delivery and the system assigns a unique GRN 
No. For this example, let me just say it is GRN1. Now, I will issue them 
out of store with a unique IV No for each issue.

Let me just say, Mr. X required 2pcs of ART1. Well, now I will issue 2pcs 
of ART1 @ $5 each and the IV No is IV1. Later, Mr. Y required 3pcs of ART1 
and again I issue that 3pcs of ART1 @ $5 and the IV No is IV2. Then, Mr. Z 
required 2pcs of ART1 and now I don't have stock to issue. Thus, I 
purchase 5pcs of ART1 @ $7 each. Thereafter, I update the stock and issue 
the 2pcs of ART1 @ $7 to Mr. Z and the IV No is IV3.

This is where I am stranded. Please help.

Thanks,
Maha

What type of data do you need to get out of this system later? Do you need
to know that the 5 items from GRN1 went to IV1 (2 items) and IV3 (3 items),
or that IV7 had items from GRN3 (1 item) and GRN4 (1 item) for example?

(This is simplified if each individual piece is given a unique identity of
its own but I don't believe that is possible in systems like the one you
have.)

My first idea is to link this data to the order.  Order #O8 had 3 items, 2
sourced from GRN8 and 1 sourced from GRN9, and disbursed as IV6.  It seems
orders and IV's have a one-to-one relationship for any given type of item?

tell me more...




-----Original Message-----
From: Maha Arupputhan Pappan [mailto:mahap@p...]
Sent: Tuesday, October 22, 2002 11:13 PM
To: Access
Subject: [access] RE: Store Management System - Some Advice


Thanks Dave,

I appreciate your help. Let me be specific.

I wish to make the applicatin to run in FIFO method. That will be when I 
purchase an article, I must update the stock level and then I issue the 
article out of the store. When issuing the article, the system must apply 
the FIFO method.

Example, I purchased 5 pcs of ART1 for the price of $5 each on 23-Oct-02. 
I update the stock level upon delivery and the system assigns a unique GRN 
No. For this example, let me just say it is GRN1. Now, I will issue them 
out of store with a unique IV No for each issue.

Let me just say, Mr. X required 2pcs of ART1. Well, now I will issue 2pcs 
of ART1 @ $5 each and the IV No is IV1. Later, Mr. Y required 3pcs of ART1 
and again I issue that 3pcs of ART1 @ $5 and the IV No is IV2. Then, Mr. Z 
required 2pcs of ART1 and now I don't have stock to issue. Thus, I 
purchase 5pcs of ART1 @ $7 each. Thereafter, I update the stock and issue 
the 2pcs of ART1 @ $7 to Mr. Z and the IV No is IV3.

This is where I am stranded. Please help.

Thanks,
Maha

1. Is the GRN number that is assigned unique for each purchase that you
receive, and does it automatically increment itself?  In other words, if
you receive 10 ART1 at $2 each on 21-Oct-02 and then receive 15 ART1 at
$5.00 each on 23-Oct-02, is a new GRN number generated for each receipt?
Does your Received table store the GRN Number? (tbl_Purchase)

PartNo	RcptDateTime		Qty	Cost	GRN No
ART1		21-Oct-02/10:00pm		10	2.00	GRN1
ART2		23-Oct-02/11:00am		15	5.00	GRN2


2. When you receive product from your vendors, is the GRN number and
product cost placed into a separate table from the received table and
inventory table? (I'll call this one tbl_FIFO)

GRN No	Cost
GRN1		2.00
GRN2		5.00
  

3. How is the product you receive stored in the Inventory table?  Using
the sample above, you should have two records in the Inventory table for
ART1 and they would look something like this: (tbl_Inventory)

PartNo	GRN No	Qty
ART1  	GRN1		10
ART1		GRN2		15

If the GRN number is stored in the Received table, and you have a table
like tbl_FIFO and your inventory is similar to that in 3, then you
should have no problem insuring that only First-In and First-Out is
accomplished.  You would use the GRN Number as the key field to insure
the oldest received property in inventory is pulled and the GRN Number
would also provide you with the Cost of the selected property via the
tbl_FIFO table.

Does this make sense?


John V. Ruff - The Eternal Optimist :-)
Always Looking For Contract Opportunities

www.noclassroom.com
Live software training
Right over the Internet

Home:  xxx.xxx.xxxx
Cell:  xxx.xxx.xxxx
9306 Farwest Dr SW
Lakewood, WA 98498




-----Original Message-----
From: Maha Arupputhan Pappan [mailto:mahap@p...] 
Sent: Wednesday, October 23, 2002 4:13 AM
To: Access
Subject: [access] RE: Store Management System - Some Advice


Thanks Dave,

I appreciate your help. Let me be specific.

I wish to make the applicatin to run in FIFO method. That will be when I

purchase an article, I must update the stock level and then I issue the 
article out of the store. When issuing the article, the system must
apply 
the FIFO method.

Example, I purchased 5 pcs of ART1 for the price of $5 each on
23-Oct-02. 
I update the stock level upon delivery and the system assigns a unique
GRN 
No. For this example, let me just say it is GRN1. Now, I will issue them

out of store with a unique IV No for each issue.

Let me just say, Mr. X required 2pcs of ART1. Well, now I will issue
2pcs 
of ART1 @ $5 each and the IV No is IV1. Later, Mr. Y required 3pcs of
ART1 
and again I issue that 3pcs of ART1 @ $5 and the IV No is IV2. Then, Mr.
Z 
required 2pcs of ART1 and now I don't have stock to issue. Thus, I 
purchase 5pcs of ART1 @ $7 each. Thereafter, I update the stock and
issue 
the 2pcs of ART1 @ $7 to Mr. Z and the IV No is IV3.

This is where I am stranded. Please help.

Thanks,
Maha

> 1. Is the GRN number that is assigned unique for each purchase that you
receive, and does it automatically increment itself?  In other words, if
you receive 10 ART1 at $2 each on 21-Oct-02 and then receive 15 ART1 at
$5.00 each on 23-Oct-02, is a new GRN number generated for each receipt?
Does your Received table store the GRN Number? (tbl_Purchase)

PART#	RECEIVE_DATE	QTY	COST	GRN#
ART1	21-Oct-02	10	2.00	GRN1
ART2	23-Oct-02	15	5.00	GRN2

Hey Guys,

1. Yes, the GRN No is automatically increment itself.
2. Yes, a new GRN No is generated for each receipt.
3. Yes, the Receive Table stores the GRN No.

Dave, this is the situation and John you got it right.

Based on this example, let me briefly explain the issuance:

PART#	ISSUE_DATE	QTY	COST	GRN#
ART1	21-Oct-02	5	2.00	GRN1
ART1	23-Oct-02	5	2.00	GRN1

Thus, ART1 is completely issued from GRN1 and the same goes to ART2 AND 
GRN2.

PART#	ISSUE_DATE	QTY	COST	GRN#
ART2	23-Oct-02	5	5.00	GRN2
ART2	24-Oct-02	5	5.00	GRN2
ART2	25-Oct-02	5	5.00	GRN2

Seek your professional advice to proceed.

Thanks,
Maha