Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-17-2019, 10:15 AM   #1
Zydeceltico
Newly Registered User
 
Join Date: Dec 2017
Location: Pittsburgh, PA
Posts: 575
Thanks: 424
Thanked 6 Times in 6 Posts
Zydeceltico is on a distinguished road
Table design receiving partial orders

Hi all -

Now I've been tasked with updating a Parts Receiving database. What it really means is a table rework.

I have it mostly worked out but I am unsure of a "best practice" to accommodate partial PO shipments. We buy small hardware from outside sources in quantities in the tens of thousands. This would all be on one PO.

But the shipments might come in lots of 2000 or something and they'll show up on different dates until the total quantity of the PO is reached.

So we may issue a single PO for 20,000 widgets and receive 5,000 on 6/1; 10,000 of them on 6/17; and 5,000 of them on 10/31.

Before I blindly dive too deep into this I thought I would get request some sage advice from those who may have trod a similar road before.

Thanks a bunch for any and all insight and/or direction,

Tim

Zydeceltico is offline   Reply With Quote
Old 05-17-2019, 10:44 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,727
Thanks: 40
Thanked 3,481 Times in 3,368 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Table design receiving partial orders

you need one table for your orders and another for your shipments with a link to the orders table

edit - perhaps more than one

order header
order items
shipment header
shipment items
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
Zydeceltico (05-17-2019)
Old 05-17-2019, 11:03 AM   #3
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,786
Thanks: 17
Thanked 341 Times in 338 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Table design receiving partial orders

To elaborate a bit,

You have a PO, the Purchase itself, with things like Date, Vendor, and other authorization information (who, why, what)
You'd have line items with the individual item (by item number) and quantity.

So far, pretty straight forward.
You get a shipment in. The Shipment will normally have tracking number and shipper as well as carrier.
Shipments also have line items.
line item should have supplier, Purchase order, item, and quantity.

You are normally going to only have ONE PO in one shipment, but I'd double check with your receivers to see if you ever get a carrier who brings you 10,000 widgets that match to more than one PO. For the moment, I'll assume no but we can deal with that later if it happens.

You'd then, for each shipment line item, attach it to its corresponding PO line item to show what was actually received. This does mean you can have one (or more) shipment line items for a given PO line item.

Important question, do you need to deal with substitutions? I.e. they no longer sell 5lt kegs of widget juice, but are sending 1lts instead? Having dealt with this for retail, it can be a major headache.

Mark_ is offline   Reply With Quote
The Following User Says Thank You to Mark_ For This Useful Post:
Zydeceltico (05-17-2019)
Old 05-17-2019, 11:09 AM   #4
Zydeceltico
Newly Registered User
 
Join Date: Dec 2017
Location: Pittsburgh, PA
Posts: 575
Thanks: 424
Thanked 6 Times in 6 Posts
Zydeceltico is on a distinguished road
Re: Table design receiving partial orders

Quote:
Originally Posted by Mark_ View Post
Important question, do you need to deal with substitutions? I.e. they no longer sell 5lt kegs of widget juice, but are sending 1lts instead? Having dealt with this for retail, it can be a major headache.
No, never. All I widgets are our patent. No subs.
Zydeceltico is offline   Reply With Quote
Old 05-17-2019, 11:41 AM   #5
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,812
Thanks: 70
Thanked 1,953 Times in 1,901 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Table design receiving partial orders

Here is a link to Barry Williams' site with a data model for Partial Deliveries. It may give you some insight.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
Zydeceltico (05-17-2019)
Old 05-17-2019, 12:04 PM   #6
Zydeceltico
Newly Registered User
 
Join Date: Dec 2017
Location: Pittsburgh, PA
Posts: 575
Thanks: 424
Thanked 6 Times in 6 Posts
Zydeceltico is on a distinguished road
Re: Table design receiving partial orders

Quote:
Originally Posted by jdraw View Post
I've only glanced at the model - - - - -but am immediately intrigued by the Products table for a completely different reason - - is it self-referencing?
Zydeceltico is offline   Reply With Quote
Old 05-17-2019, 12:36 PM   #7
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,786
Thanks: 17
Thanked 341 Times in 338 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Table design receiving partial orders

He is doing one of several ways to "Self Reference", normally found if you have parts that are not common across several products.

I normally use a 2nd table that has "Parent Product" and "Child Product" along with "Quantity". Means you can have a Nut, a Bolt an "Nut and Bolt". Nut as a record becomes a child record to "Nut and Bolt" with a quantity of 1. Same with Bolt.

Also Nut can become a child record to "Kids bracelet" with a quantity of 40 when your kid decides to string 10 of them on a rubber band to make a bracelet. Allows any product to be made of any number of other products while also being a component of any number of other products.

Mark_ is offline   Reply With Quote
The Following User Says Thank You to Mark_ For This Useful Post:
Zydeceltico (05-17-2019)
Old 05-17-2019, 12:39 PM   #8
Zydeceltico
Newly Registered User
 
Join Date: Dec 2017
Location: Pittsburgh, PA
Posts: 575
Thanks: 424
Thanked 6 Times in 6 Posts
Zydeceltico is on a distinguished road
Re: Table design receiving partial orders

Quote:
Originally Posted by Mark_ View Post
He is doing one of several ways to "Self Reference", normally found if you have parts that are not common across several products.

I normally use a 2nd table that has "Parent Product" and "Child Product" along with "Quantity". Means you can have a Nut, a Bolt an "Nut and Bolt". Nut as a record becomes a child record to "Nut and Bolt" with a quantity of 1. Same with Bolt.

Also Nut can become a child record to "Kids bracelet" with a quantity of 40 when your kid decides to string 10 of them on a rubber band to make a bracelet. Allows any product to be made of any number of other products while also being a component of any number of other products.
I have a very similar condition. I went with the Parent/Child Tables idea. It works great for me.

Zydeceltico is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Entering orders into the orders table megatronixs Modules & VBA 9 03-07-2014 02:52 AM
Database design for tracking tracking deliveries against orders TimTDP General 3 04-07-2013 01:19 PM
I need to Design Fee Receiving system shakeelmscw Tables 4 11-27-2012 02:00 AM
Orders/Delivery Notes with partial fulfillment capability jt196 Tables 11 05-03-2010 03:31 AM
Table Structure advice- Receiving Join Key not in recordset error. sw1979 Tables 1 07-11-2008 06:49 AM




All times are GMT -8. The time now is 05:01 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World