QUESTION on Purchasing Database - RECEIVING PARTS

[kealaz]

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.

 

[Steve]

For each item you order, you have a one-to-many relationship with the
receipt of that item because you may have multiplr receipts of the item
until the order of the item is fulfilled. When you have a one-to-many
relationship, you need a table on the one side and a table on the many side.
Your TblOrderdetail table should look something like:
TblOrderDetail
OrderDetailID
OrderID
ItemID
Quantity
UnitCost

TblOrderDetail is the table on the one side so you need to create a table on
the many side:
TblOrderDetailReceipt
OrderDetailReceiptID
OrderDetailID
DateReceived
QuantityReceived

For data entry you would need a form/subform. Both the main form and the
subform would best be based on queries to be able to show appropriate data
such as the item name in the main form and date ordered, quantity ordered
and total received in the subform.

Steve
(e-mail address removed)

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.

 

[Fred]

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.

 

[kealaz]

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!!!

 

[Fred]

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.