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 =)