One or Two Tables And Quantity Question

[lisarisa333]

This is being crossposted from utteraccess.com as I'm desperately seeking some direction.
See that post here: https://www.utteraccess.com/forum/in...ic=2052764&hl=


Firstly, I'd like to thank you all for your contributions to this site!! I've learned quite a few things and y'all are awesome for taking time to help others the way that you do.


This is my first forum post / question. Please forgive me if I've done something wrong. Here goes:


I am designing a database to keep track of parts received to a purchase order then issued to a work order. Each part received to a purchase order is given a part tag number. When it's time comes, that part + part tag is issued to a work order.


Problems I’m facing and need help with:


1. Do I keep all transactions together in a single table, or do I separate them, one for “received to purchase order” and one for “issued to work order”? Which then ties in with #2 below, of not always issuing the same quantity received.
2. Quantity – We might receive 1 pack of 25 each of one part to a purchase order, but then must break the pack and only issue 12 each to the work order, leaving 13 each of the part in the “available” inventory. (this would be the only time we would have available inventory as nothing is kept as “stock” or needing “reorder levels”)
3. Now that I’m typing all this out and thinking of situations and possibilities…. Suppose I’d like to issue 12 of that 13 remaining in stock, to a new work order. Is that possible without first having “received to a purchase order”


My tables so far:


Part_tbl
PartID - autonumber - PK
PartNumber - short text
PartDescr - short text
PartPic - photo attach.


Received_tbl
RecdID - autonumber - PK
PartTagNum - short text
PartNumber - short text
Description - short text
RecdDate - date
RecdQty - number
RecdUOM - text
RecdUOMea - number
RecdTotal - number
PONumber - short text
Location - short text
RecdNotes - long text


Issued_tbl
IssdID - autonumber - PK
PartTagNum - short text
PartNumber - short text
Description - short text
IssdDate - date
IssdQty - number
IssdUOM - text
IssdUOMea - number
IssdTotal - number
WorkOrderNumber - short text
IssdNotes - long text






Thanks in advance for any assistance you all can provide,
Lisa =)

[pbaldy]

I lean towards one table with positive and negative quantities, but I wouldn't argue against two. Your fields are basically the same in the two tables, other than PO/workorder, which could be inferred from context.

To your other question, I'd just record the total quantity, disregarding "packs". If you receive a pack of 25 widgets, I'd record a quantity of 25, not 1. Then when you're calculating quantity on hand, it's simple math. You received 25 and issued 12, leaving 13 on hand.

[lisarisa333]

Thank you for your input Paul. I absolutely appreciate it. I think perhaps I am overthinking this entire thing!

[pbaldy]

No problem, post back if you get stuck. Inventory is definitely not a trivial undertaking.

By the way, typically the only "part" field I'd expect to find in the transaction table(s) would be the ID from the parts table. In other words, not the description field. You'd get that by joining the tables together in a query. Not sure what the PartTagNum fields are, but the same might apply.