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
|
|||
|
|||
QUESTION on Purchasing Database - RECEIVING PARTS
I have a form [frmPO_ISSUE] with a subform [frmORDER_DETAILS] to issue
purchase orders. The information from this/these form(s) is stored in two tables [tblPOHIST and tblBUYHIST]. tblPOHIST holds information about the purchase order. tblBUYHIST holds information about the individual parts ordered on the purchase order. With my database, I am currently able to successfully enter material requests, purchase requests and process them to be able to cut purchase orders and issue them to our vendors. Yay! Now, I am working on the other side of that transaction. I need to be able to receive the purchase orders (in the database). I need to come up with a way to receive the individual items as they come in. I have no clue where to start, or how to do this. Here are some of the parameters that I need to account for. If there are multiple items that are on a purchase order and only some are received, I need to be able to check those items in and have the PO remain open. Once all of the items are received, the PO should be closed. If a partial order of one item comes in, I need to check in the items received and leave the remaining open. (i.e. if I order 100 pcs. of one part, and we receive 35, the PO needs to remain open until the remaining 65 is received) Ideally I would like to be able to keep a history of when each item comes in. Being able to check this information is a great resource for me when I'm estimating average lead time of parts and I use this information for scheduling and project management tasks. I would also like to be able to run a report to look up all items currently open. In the current system (dbaseIV) I am able to do this, it is of great help, and I find that I use this feature quite a bit. When all items are received, the PO should be "closed out" automatically. My questions: 1) Where do I start??? -- from a design standpoint, what is the best way to tackle this beast? 2) Can I track the information by adding fields to my already existing tables? or should I track this information by creating new tables and storing information in it? Thank you very much for any and all assistance you can provide. |
#3
|
|||
|
|||
QUESTION on Purchasing Database - RECEIVING PARTS
As you noted, you're looking for the place to start rather than instructrions
for your entire new project. I'm just addressing the structure side. You need to start by defining your entities. Including adding some rigorousness to the definitions of what you already databased. This will also depend on how smart/dependable your data entry people are. One approach would be to add some fields to your existing tables. Like: (shorten my long names) - Status on overall PO (Open, Entirely Cancelled, TotallyReceivedAndClosed - Status on individual line items (Open, Cancelled etc.) - "Quantity Still open" or "Total quantity received" - Receiving notes And if you have smart/dependabel data entry people you could have them edit those fields when received. - - - - - - A more thorough approach would be to add an "ItemReceipt" table which has a record for each instance of receiving a quantity of an item against an open PO. It would be on the "many" side of a linkage to your PO items table. These could then be executed as once-and-only-once transactions against quantities on open line items etc, but that would be complicated. Alternatively, you could just group these by which PO item they are under/linked to, and compare the total to the total ordered to determine status. |
#4
|
|||
|
|||
QUESTION on Purchasing Database - RECEIVING PARTS
"Fred" wrote:
A more thorough approach would be to add an "ItemReceipt" table which has a record for each instance of receiving a quantity of an item against an open PO. It would be on the "many" side of a linkage to your PO items table. These could then be executed as once-and-only-once transactions against quantities on open line items etc, but that would be complicated. Hi Fred! Thank you so much for answering my post. This [what you described above] is exactly what I need to accomplish. This is how the database was designed before (in dbaseIV) and if I can get something similar happening in Access, then I'm sure I will be able to get all the data I need. (i.e. the receiving history I talked about in my OP) I realize that it is a complicated endeavor, but I am up for the challenge. Can you (or anyone else) point me in the right direction? I have a framework to work from. This is working in our old database (dbaseIV) and I refer to that often. In it, they have a table tblRCVRHIST that stores each transaction. How do I use the information gathered in that table [tblRCVRHIST] to determine the status of the purchase order in my other tables [tblPOHIST and tblBUYHIST]. Thank you VERY much for any and all assistance you can provide!!! |
#5
|
|||
|
|||
QUESTION on Purchasing Database - RECEIVING PARTS
First, to clarify, I described two different methods. And, of course, there
are others, such as a system where the third table is ALL transactions of the item on the PO, including the original posting. It d it looks like we're talking about the second one on my Your starting point/foundation will be table definitions and then table structure. Start with a "what is a record" definition for your tblRCVHIST table. Obviously it will include instance of receipt of a part number against an open PO. If you leave it at that, then all of the "exceptions" (changing the quantities on an open order etc.) will need to get handled by editing that line in the PO. Next, if you don't already have one, create a Primary Key (PK) field (e.g. BuyHistID) for your tblBuyHist table. Then add that same field into your tblRCVHIST table. Then link those two fields. That should give you a structure to support what you are trying to do. Then, after that, you'll need to decide exactly what you mean by "determine the status". Of course I'm not talking going beyond the obvious. Regarding status, at the line items can have 4 statuses (none fille, partially filled, exactly filled, overfilled) statuses. When you move up to the overall PO level the range of possibilities gets bigger/more complex. You'll also have to decide what you want to have happen regardingn status. E.G. just do a printout that shows them all, or have & update status fields on the overall PO and / or PO line items. |
Thread Tools | |
Display Modes | |
|
|