Table Issue - Relationships (1 Viewer)

NickFazer

New member
Local time
Today, 12:44
Joined
Apr 9, 2012
Messages
20
Hello

I have been creating a database to help manage call off products from suppliers and until now I have built a structure that works, as far as the flow of data is concerned. I am able to enter all the data directly in the tables cascading down from entering a PO with a Supplier to Calling Off product and then entering details of the product shipped. However when I tried to create a shipping order to the Supplier I was missing a Shipment table and I am struggling to decide how to link this in currently it is linked to the Supplier table but there isn't a direct relationship to the call off items. I have attached the relationship diagram. The point of the exercise is to be able to call of fruit and manage the shipping details and provide the storage suppliers with the information required before delivery and for me to be able to look up what orders are placed / shipped / delivered etc. Would appreciate some advise on the structure of the data, I am starting to think that I have approached this from the wrong angle.

I will of course answer any questions regarding the flow but the tables should have the correct data but maybe I have should link it together differently.
 

Attachments

  • FruitDBaseRelationships.pdf
    141.5 KB · Views: 121

jdraw

Super Moderator
Staff member
Local time
Today, 08:44
Joined
Jan 23, 2006
Messages
15,364
You should not be entering data directly into tables. That is a technique awaiting issues.
For clarity, please explain Call Off Products.

This didn't help me:

What is a call off purchase order?
A Call-off Order is an order created to cover multiple supplies or deliveries from a single company. A Call-off order may be applied in the following circumstances : • For a medium / long / regular term supply of the same services from the same supplier.
http://documents.manchester.ac.uk/display.aspx?DocID=8182
I suggest you give us some scenarios in plain English to exercise your existing relationships.
You create a Purchase Order with a Supplier for Products. Flesh out these processes in simpl terms so we get an understanding of WHAT should happen in this business. Once we understand WHAT is to be supported with this database, the model can be designed and tested before physical changes to your operational database.
Good luck.
 

NickFazer

New member
Local time
Today, 12:44
Joined
Apr 9, 2012
Messages
20
Hi jdraw

Thank you for your reply.

I understand that normally data isn't entered directly into tables, I was entering test data to make sure there were no issues in the relationships.

The process.

At the beginning of each fruit season we place contracts [Purchase Orders] to cover our requirements for the next year, we buy EACH product from MULTIPLE suppliers, EACH Purchase Order can have MULTIPLE fruit items on it. Over the course of the year I will CALL OFF fruit from these contracts in full container shipments, each container can be made up of MULTIPLE fruits from ONE supplier [Call Off]. It is not unusual on a container shipment that EACH fruit called off will have MULTIPLE Best Before End dates, which I need to identify by a Lot or Batch Number when the stock is booked into storage at its destination [Shipping Details].

I have attached the dbase file with some data in it, the capitalised text is just for clarity. I need to be able to produce a Call Off to send to the Supplier, enter the details for each fruit shipped Qty, BBE Date etc and the booking details to send to the storage location and be able to monitor what has been requested, shipped and delivered.

I am looking at this stage for some reassurance that the structure works or advise on how it could be im[roved before I get into creating the queries / forms and reports etc.

Regards

Nick
 

Attachments

  • FruitManagement.accdb
    2.8 MB · Views: 109

jdraw

Super Moderator
Staff member
Local time
Today, 08:44
Joined
Jan 23, 2006
Messages
15,364
A good start in my view, but the devil is always in the details.
A few things to be aware of:
-every table should have a Primary Key
-table and field names should not have embedded spaces ( will lead to syntax issues)(your use of "_" shows your awareness of this)
-tables should be normalized (tblRecipe is not normalized)
-each table should be accounted for in your model/relationships window
-Do you have descriptions for each table and field? (easier for you and any who use/do maintenance)
-recommend you review this link and do not use Lookups at table field level (fruit type
-use real lookup/reference tables
-you don't need a fully populated database for testing( syndrome is you've done so much work, you don't have time/resources to improve design)
-create some sample scenarios to reflect the basic queries/reports your final system must support
-build and vet a model before getting too deep into physical database and populating tables with data

General:
Who will use the database?
Do you have related documentation(user manual, technical overview, maintenance procedures...)?
What is the current status of the project?

Do you have scenario(s) to exercise this:
I need to be able to produce a Call Off to send to the Supplier, enter the details for each fruit shipped Qty, BBE Date etc and the booking details to send to the storage location and be able to monitor what has been requested, shipped and delivered.

There are several models at Database answers.org re Customers/Orders/Products
Good luck.
 

NickFazer

New member
Local time
Today, 12:44
Joined
Apr 9, 2012
Messages
20
I will take your points on board and review the Primary Key and syntax for table names. Most of the data has been imported from Excel but I will put in descriptions for Field Names and include all tables in the relationship window going forward. The Recipe table is for the next stage once this part is completed, tested and working and can be addressed later.

General

Initially it will be used by myself as far as data input is concerned. I will need to disseminate information from the database but that will be in the form of pdf reports or linked [read only] spreadsheets. I am not under any immediate deadline for completion but it would save me a lot of time gathering different data from multiple locations, this will put in one place.

Scenario:

I need to restock on 2 types of Orange pulp FruitA and FruitB . Both of these I can get from Supplier 1, so I look up the Purchase Orders still Open for Supplier 1 to check the outstanding balances available to bring in and decide how much of FruitA and FruitB I need to fill the container

e.g 15000kg FruitA and 7000kg Fruit B

I then send that Call Off request to Supplier1, advising where the container is to be delivered to.

Once the container has been loaded Supplier1 will send me the details of the shipment, Container No e.g GESU 4666563/1, the quantity of each fruit and the BBE dates of each fruit

e.g FruitA 14980kg - 10500kg BBE Date 30/04/21, 4480kg BBE Date 15/05/21
Fruit B 6990kg all BBE Date 01/05/21

I then need to enter the data for the fruit into the Call Off and Shipping tables [via a form of course] and that will be sent out to the storage facility where the container will be sent to and internally to Quality Assurance together with their documents. On this delivery there will be 3 Lot Numbers required 2 for FruitA as there are 2 BBE Dates for that fruit and 1 for Fruit B which is all 1 BBE Date. The fruit can then be booked in on arrival.

Thank you for taking the time to look at this, I was happy to see that you think it is at least going in the right direction.
 

mike60smart

Registered User.
Local time
Today, 12:44
Joined
Aug 6, 2017
Messages
1,899
Hi Nick

I am a little puzzled at the moment as to the flow of Purchases.

Scenario is:-

You produce a Purchase order for a Number of Products from a Supplier.

The Supplier sends Shipping details

Where does the Fruit get stored?
 

NickFazer

New member
Local time
Today, 12:44
Joined
Apr 9, 2012
Messages
20
Hi Mike

Fruit is stored off site either frozen, chilled or ambient depending on what it is. I didn't show these tables on the original relationship diagram. Please find attached v2 schema. Tables XPO x 2 are 3pl storage destinations.
 

Attachments

  • FruitDBaseRelationships.pdf
    133.3 KB · Views: 167

mike60smart

Registered User.
Local time
Today, 12:44
Joined
Aug 6, 2017
Messages
1,899
Hi Nick

OK But you have no means of identifying which POrder they relate to?
 

NickFazer

New member
Local time
Today, 12:44
Joined
Apr 9, 2012
Messages
20
Hi Mike

The storage location would have the PO quoted on the the call off document before stock arrives, the storage facility only need the PO to put it on their Goods In paperwork provided to us after receipt, the store book the stock in on their own system using our product codes.

Basically I provide our supplier with the PO for up to a years worth of product, then product is called off against that PO throughout the next year and the Shipment details for each call off of stock are sent to the storage facilities to enable them to recieve the goods.
 

mike60smart

Registered User.
Local time
Today, 12:44
Joined
Aug 6, 2017
Messages
1,899
Ok So you Produce a POrder for X amount of Fruit to last the next x number of Months

I would have thought this would be classed as Goods Received

You then over time Call Down the Products as required and these would be Goods Used

I would imagine your best option is to make a Transaction table which has a Field which is Either Goods Received or Goods Used thus enabling you to have a Running Stock Total ?
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:44
Joined
Jan 23, 2006
Messages
15,364
RE:
Scenario:

I need to restock on 2 types of Orange pulp FruitA and FruitB . Both of these I can get from Supplier 1, so I look up the Purchase Orders still Open for Supplier 1 to check the outstanding balances available to bring in and decide how much of FruitA and FruitB I need to fill the container

e.g 15000kg FruitA and 7000kg Fruit B

I then send that Call Off request to Supplier1, advising where the container is to be delivered to.


So using the database you posted in #3.
You have to identify your FruitA and FruitB --let's use a real example-- from your existing records.
You review Purchase Orders from Supplier 1 to identify "available product balance based on your contract"
(seems this is Total Contract Amount - already acquired amount (for each Product fruitA/fruitB) to determine how much of each is still available; then of that amount(s) how much do you need to fill a Container (How exactly do you do this in plain English)?
You can't request more than is available based on your Contract --or can you adjust a Contract while it is in effect( details would be helpful).

You send the Purchase Order with these Product details and Quantities of each to Supplier 1, and also identify where the Container/shipment is to be delivered.
(Somewhere in these processes you record your Open PurchaseOrder details, and can determine the existing Balance of product at Supplier1 in case you have another request of Supplier 1 before this specific Order is fulfilled/received.) (It is these details that will help with identifying tables and relationships)

If you use examples from your supplied database, and describe what you need to get out of the system, readers can help with design and testing/vetting of the evolving database. Get the model(blueprint) designed and vetted before getting too deep into physical database.

JUST SAW MIKE'S POST.

Agree that some sort of transaction table for inventory activity is good approach. See Allen Browne AppInventory
 
Last edited:

NickFazer

New member
Local time
Today, 12:44
Joined
Apr 9, 2012
Messages
20
OK

At some point today I will repost the DBase File with just the required data in the file to map the process from start to finish complete with descriptive comments in the tables.


Many Thanks
 

NickFazer

New member
Local time
Today, 12:44
Joined
Apr 9, 2012
Messages
20
I have attached a cut down version of my dbase model with one completed process added into the tables.

A Purchase order is placed with BORDAS - Ref No 080000, PO_ID 51. This PO has two lines on it for two different fruits different quantities but they are to be shipped throughout the year in container loads XFOB15TT20 is for 100000kgs and XFOB30MC20 for 20000kgs, these appear in the PO_Line_Details table.

I then sent a Call Off request asking for 10,000kg of XFOBI5TT20 and 1,000kg of XFOB30MC20 to be shipped to XPO CANNOCK, this is in the Call_Off table. Once the supplier has confirmed the container is loaded they tell me the exact quantity loaded, which can and often is different to the original Call Off quantity, this amount is entered into the Shipping_Details , I also get the Container No and the all of the Production and Best before dates of each fruit, I need to put each BBE Date for each fruit on a separate Lot No therefore in the Shipping Details table there are 3 entries 2 for XFOB15TT20 because 3 pallets have a BBE Date of 0/10/2021 and 7 pallets of BBE date 05/10/2021. The second fruit is all the same BBE date.

Once I have all the details required I send this information to the Storage supplier and request a Booking in Date / Time and Reference for the Suppliers haulier to arrive with the goods.

Simples.

There is a lot of to and fro passing data and I can't see a way round that at the moment but it is surprising as to what is involved when it is broken down. I suppose one level could be eliminated if I amend the PO_Line_Details, when the quantity to be sent is confirmed rather than entering it in Shipping Details.

Hope this is clearer.
 

Attachments

  • FruitManagementTest.accdb
    1.5 MB · Views: 112
Last edited:

NickFazer

New member
Local time
Today, 12:44
Joined
Apr 9, 2012
Messages
20
I have attached a cut down version of my dbase model with one completed process added into the tables.

A Purchase order is placed with BORDAS - Ref No 080000, PO_ID 51. This PO has two lines on it for two different fruits different quantities but they are to be shipped throughout the year in container loads XFOB15TT20 is for 100000kgs and XFOB30MC20 for 20000kgs, these appear in the PO_Line_Details table.

I then sent a Call Off request asking for 10,000kg of XFOBI5TT20 and 1,000kg of XFOB30MC20 to be shipped to XPO CANNOCK, this is in the Call_Off table. Once the supplier has confirmed the container is loaded they tell me the exact quantity loaded, which can and often is different to the original Call Off quantity, this amount is entered into the Shipping_Details , I also get the Container No and the all of the Production and Best before dates of each fruit, I need to put each BBE Date for each fruit on a separate Lot No therefore in the Shipping Details table there are 3 entries 2 for XFOB15TT20 because 3 pallets have a BBE Date of 0/10/2021 and 7 pallets of BBE date 05/10/2021. The second fruit is all the same BBE date.

Once I have all the details required I send this information to the Storage supplier and request a Booking in Date / Time and Reference for the Suppliers haulier to arrive with the goods.

Simples.

There is a lot of to and fro passing data and I can't see a way round that at the moment but it is surprising as to what is involved when it is broken down. I suppose one level could be eliminated if I amend the PO_Line_Details, when the quantity to be sent is confirmed rather than entering it in Shipping Details.

Hope this is clearer.
 

Attachments

  • FruitManagementTest.accdb
    1.5 MB · Views: 100

mike60smart

Registered User.
Local time
Today, 12:44
Joined
Aug 6, 2017
Messages
1,899
Hi Nick

How do you currently do the data input for the process you have listed?

Are you doing this directly into the tables?
 

mike60smart

Registered User.
Local time
Today, 12:44
Joined
Aug 6, 2017
Messages
1,899
Hi Nick

I believe the following layout would make it easier to understand Quantities Called & Remaining but I am sure it can be improved.
 

Attachments

  • FruitManagementTest.zip
    54.4 KB · Views: 106

jdraw

Super Moderator
Staff member
Local time
Today, 08:44
Joined
Jan 23, 2006
Messages
15,364
@nick and Mike

When you post with attached databases please, clearly identify and describe what is attached and supply a version number or similar. It's difficult enough to collaborate via posts in a forum, so some description of the changes/approach etc of the attached file would make the process a little more efficient.

In earlier post Nick wrote "... contracts [Purchase Orders] .." Do you have contracts and Purchase orders or just 1 entity?

I suggest we work on the latest example and see if the tables/relationships can handle the scenario.
 

NickFazer

New member
Local time
Today, 12:44
Joined
Apr 9, 2012
Messages
20
Hi jdraw

OK, will remember for future postings.

Regrading Purchase Orders they are one and the same, some are placed with an end date 12 month or more some times in the future [contracted volume] and others are for goods to be delivered in one shipment
 

NickFazer

New member
Local time
Today, 12:44
Joined
Apr 9, 2012
Messages
20
Hi Nick

I believe the following layout would make it easier to understand Quantities Called & Remaining but I am sure it can be improved.
Hi Mike

That looks great and is definitely most of the way to where I want to go with this, I will study it properly and see what I need to do to finish it off. I am very encouraged that my structure for the tables seems to work.

Many Thanks
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:44
Joined
Jan 23, 2006
Messages
15,364
Nick --Fruit Management --CallOffContracts.
I have read your posts and have drafted a set of business rules as I understand things. It would be helpful to you and readers if you could review these draft rules and edit/delete/add others as necessary to ensure we have a common understanding of the business.

Draft rules: (these are preliminary and need to be refined and vetted)
Our company deals with 1 or many Supplier(s)
1 or many Contract(s) are set up with a Supplier
A Contract defines the Products and Quantities to be Supplied (other Terms/conditions?)
A CallOff is a PurchaseOrder against an active Contract
1 or many PurchaseOrder(s)/CallOff(s) may be placed against a Contract with Supplier
A PurchaseOrder may be for a proposed Quantity of 1 or Many Product(s)
A PurchaseOrder identifies the ShipTo Location for
A Supplier ships 1 or many Product(s) as a Shipment
A Shipment has 1 or many ShipmentDetails
A Shipment identifies the actual shipped Quantity of Product
A Shipment identifies the Container holding the Product
A Container hold Lots/Batches of shipped Product
A Shipment identifies the BestBeforeDate of each Lot of shipped Quantity of Product
The PO proposed quantity of Product may differ from the actual shipped quantity of Product
The actual shipped quantity of Product is subtracted from the Contracted quantity
A Lot(Batch) is a quantity of shipped Product with a common ProductType and BestBeforeDate
A Shipment is separated into 1 or more Lot(s) based on ProductType and BestBeforeDate (by whom?)
A Shipment may contain 1 or many Pallets
A Pallet may contain 1 or Many Lots


From this statement:
Once I have all the details required I send this information to the Storage supplier and request a Booking in Date / Time and Reference for the Suppliers haulier to arrive with the goods.

It is not clear :
-who divides a shipment into Lots.
-who the Storage supplier is and relationship to Supplier
-can a shipment involve multiple Storage suppliers?
-details of Storage handling (if relevant?)
-your interaction with Supplier re haulier
-details of Booking/Appointment (who does what, where, when?)

Any info you have to flesh out these rules and related processes will be helpful in building a model to support your business.

Good luck.
 

Users who are viewing this thread

Top Bottom