If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access 2003 - Inventory - FIFO
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. -- Sherri |
#3
|
|||
|
|||
Access 2003 - Inventory - FIFO
On Jul 23, 3:34 pm, shercala
wrote: 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. -- |
#4
|
|||
|
|||
Access 2003 - Inventory - FIFO
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! -- Sherri "Steve" wrote: 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 "shercala" wrote in message ... 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. -- Sherri |
Thread Tools | |
Display Modes | |
|
|