Access 2003 - Inventory - FIFO

[Guest]

Hi,

I am trying to create a database in Access 2003 to get rid of my excel
spreadsheets and enhance reporting. I work for a non-profit and have to track
every penny in the inventory system with almost not margin for unaccounted
for items. The items can be purchased by a specific program or by the agency.
Those items purchased by a specific program have to stay in the specific
program. Those items purchased by the agency can be used for any program but
have to be tracked. All items need to be tracked on a first-in-first-out
basis in order to insure financial accuracy (ultimately this accounting of
costs will be billed back to the funding source for reimbursement)

Right now I have different sets of tables (one set pertaining to the
purchasing of the items, the orginial program(s) it's being assigned to, etc
and one set of tables relating to the allocation of that material, designated
program, etc.)

Where I'm getting stuck is, when I assign an allocation, how do I force the
entry to look for the oldest purchased item (and associated item number) and
use that first?

So for example:

I have 10 widgets at $4.00 ea. (purchased by the agency)
I then by 15 more Widgets at $2.00 ea. (purchased by the agency)

Program A, uses 13 widgets at customer A's house. When I make this
allocation, I need the database to use the first 10 at the $4.00ea. pricing
model and then fullfill the rest of the "order" with the second purchase. So
on and so forth.

Any suggestions would be greatly appreciated.

 

[Steve]

Create a query of unallocated widgets and sort by date purchased ascending.
This will put widgets in FIFO order. Now allocate the first 13 widgets to
customer A's house.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Jamie Collins]

All items need to be tracked on a first-in-first-out
basis in order to insure financial accuracy (ultimately this accounting of
costs will be billed back to the funding source for reimbursement)

Right now I have different sets of tables (one set pertaining to the
purchasing of the items, the orginial program(s) it's being assigned to, etc
and one set of tables relating to the allocation of that material, designated
program, etc.)

Where I'm getting stuck is, when I assign an allocation, how do I force the
entry to look for the oldest purchased item (and associated item number) and
use that first?

For ideas, see:

FIFO and LIFO
by Joe Celko
http://www.dbazine.com/ofinterest/oi-articles/celko32

Jamie.

--

 

[Guest]

Thank you for the response. I'm still a little confused though. Ultimately,
here is what i want to accomplish: I want to have a form that has each
property address as a separate record. For each property address I have a
sub-form that shows instances of service (i.e 5/15/07 Program A, 6/20/07
Program B) Then for each instance of service (or each program) I want to have
another sub-form that will open in a datasheet format. This datasheet I want
to be as simple as

Date Item # Item Description Qty Available Qty allocated

I want the qty available to display how many total pieces are available and
the qty allocated field to automatically assign materials starting with the
oldest order and deliver an error message if someone accidentally assigns too
many pieces when there aren't enough pieces to fill the allocation.

Then when I run a report by building and instance of service will display a
list of materials, quantities and total dollar values. Can i do all of this
with the query you mentioned in your reply? I guess I'm not sure how to set
it all up.

Thanks again for your help!