Solved Allocate Amounts From Total to Specific Deliveries Multiple Forms and Continuous Form Input

[LGDGlen]

The project i am working on is to migrate the company business from a excel spreadsheet into an access database to improve data quality, data consistency and improve the multi user experience as there are now 4 people continuously updating and using 1 HUGE excel spreadsheet which is creaking under the weight of this.

As it is we have consignments, deliveries, products and invoice information relating to delivered products as follows:

A Consignment
Made up of products coming by air or ship to a warehouse at dock/airport

Consignment Deliveries
A consignment can be broken up into 1 or many deliveries to a client

Delivered Products
A delivery can be made up of 1 or many products that are part of the consignment

Multiple invoices can be sent to the company based around all manner of actions carried out for the final product delivery, things like transport, customs clearance, packing etc.

Invoices will be supplied with a total amount and then be split across multiple deliveries which potentially could be across multiple consignments.

I need to log specific amounts that relate to a delivered product and attach that to a main invoice summary and total amount.

Currently I have a form which allows the user to filter the consignments to highlight specific ones that an invoice will relate to, select them and then generate an invoice summary record and then entries into a bridging table linking the invoice summary record to all the relevant consignments:

The user can then bring up a second form which will allow them to allocate the total amount across the delivered products, using the bridging table and the PK/FK links between consignments, deliveries and delivered products and filtering the delivered data and select specific delivered lines to allocate the specific amounts to:

So what i want to happen is:

• The user can enter values in the invoice amount for any delivery line they want to, but the amount can not exceed the total amount for the invoice.
• If the user comes back to the invoice it shows all relevant deliveries whether they have amounts or not and shows the amounts for the deliveries the user has added the amounts to
• A delivery can belong to multiple invoices and each invoice it links to will have different invoiceable amounts

I hope this is all makes sense.

My thought is that i need a 3rd table that has:

• PK - autonumber
• FK - invoice ID
• FK - consignment ID
• FK - delivery ID
• FK - delivered product ID
• Total Amount

If this is possible then as i see it:

• The second form shows a place to enter an amount but no record is written to the 3rd table unless there is an amount
• Amounts written will be shown and be editable when you select an invoice

Again i'm a total n00b with this and learning as i go so i may have gone off in the wrong direction completely so if i have i am happy to learn where i've misunderstood things, but if this makes sense i'd really like to understand how it would be possible to create what i'm looking to create

thanks in advance

Glen

 

[jdraw]

I recommend that you build a data model - get your tables and attributes and relationships identified. Then using test data and sample scenarios, vet/test your model. See stump the model. This will give you a blueprint for your database that you know from testing that supports your requirement.

 

[LGDGlen]

@jdraw thank you for your feedback it is appreciated and whilst i'm not one for expecting someone to do the work for me i really need some pointers as to what i've described above and the possibilities of what i want to achieve. i've done a considerable amount of table design work and tried to maintain good design with normal forms etc but the simplicity of transporting goods and the complexity of the information to be stored as required by the specific business i'm in means there are certain areas of database development that elude me, specifically what i've described above.

 

[MarkK]

I would ask narrower, more specific questions. I would talk about tables, not about forms. You really need to be certain that your business rules can be accurately represented by your table structures, and none of that is clear in your post.
Show your tables. Show your relationships.

 

[LGDGlen]

Here's is my table design and relationships as it stands right now. slightly simplified as there are a number of dimension tables with constant data used by a number of the tables that i've not included:

as an example of the dimension tables:

happy to supply more information if you need it

 

[LGDGlen]

my specific questions are:

• I think I need a X table that has an amount for each delivery linked to Invoice/Consignment/Delivery/DeliveredProduct tables, but not sure
• How do display a field to enter data into without creating records in that X table until a user enters values in field

I was just trying to give as much background and information as i believed to be helpful in my original posting, i may have missed the point with what i offered but i'm learning the etiquette of this forum as well as access database creation as i go so apologies

 

[MarkK]

You need a many-to-many between delivery and product. One product might be delivered many times. One delivery might contain many products. This requires another table between Delivery and Product, commonly named DeliveryDetail. That table holds the quantity of the product, for instance that instance of a delivery contains.

Also, I think you have over-named everything. Consider using singular for table names, like change FACT_Deliveries to 'Delivery'. Also, if a field is already in a table, you don't need to prefix that field with a table identifier, IMO.

You never need to link to a boolean value. It is already only a yes or a no.

What is your most granular unit of billing? Is it the consignment? The delivery? You may need a table that keeps track of that accounting, so that rows in other tables incur billing and invoices are recorded against those. When you create a invoice, what is on it? A consigment? A Delivery? Parts of a delivery? I don't think your model addresses this very clearly.

hth

 

[LGDGlen]

So FACT-DeliveryProducts is the many-to-many for the products:

the field naming is kind of a hang over from my development days of documenting code with good naming

the yes/no is hangover from my initial misunderstandings of display vs stored data, i will inevitably remove it but it was there as it was part of my connectivity diagram

granularity is the delivery product table which is what i want to link invoiced data against

the problem is that there will be multiple invoices against the same delivery product record attached to an invoice which covers multiple consignments, and initially the only info we have is the consignments that the invoice will be against and the total amount we will then need to split that total amount across the delivered products list that linked to the deliveries which is in turned linked to the consignments

its just the most complex situation for something so simple

 

[jdraw]

As I mentioned and Markk elaborated on, the key is to understand the business and its rules and develop a model of your proposed tables and relationships. The tables and relationships are critical to database - if these do not support your business rules, you'll be in a never ending cycle of workarounds. Better to get that database design matched to your business requirements sooner, rather than later.
You have provided good info, but we need more in order to offer more focused advice.

If you would, could you describe:
-a day at the office or whatever timeframe that shows what happens between receipt of a consignment, and all steps before all activity with that consignment is completed. In effect, we'd like to see the process(es) end to end to help us with communications and understanding. I'm sure you're intimately familiar with "what may seem mundane", but for readers not familiar with you, your business and the details of consignment/delivery/invoicing etc, we need some more info.

Can you show us some sample Invoice, and/or consignment documents etc? Remove anything confidential/private.

 

[LGDGlen]

i will try and give more detail and see how we go. there really isn't a "day in the life" as things can take both weeks or hours with the changing nature of the deliveries.

Consignments

Consignments are made up of multiple amounts of fruits (products) packed into a container and put on a boat or in a plane so we receive a notification that in say 2 weeks we will be able to deliver to clients the fruit so we create a consignment record with the details of when the container will arrive at the docks and what products will be arriving.

Multiple consignments can be sent throughout the month using specific transport companies.

Deliveries

Once the consignment arrives at the port it will be broken into deliveries to clients. These deliveries would have been worked out over the time it took to get the consignment from its source to the port. So over a period of a couple of weeks we sell the products to clients and as such create a delivery records attached to the consignment and then attach subsets of the consignment products to the delivery.

Deliver Product To Client

Once the product is put on a truck we add additional information to the delivery for the truck tracking information and actual arrival dates and who its going to etc.

Client Checks, Rejections and Sales

Once at a clients point of business fruit is checked, some may be rejected and may need to be delivered to other clients or the local market, the fruit that is kept is paid for to us, we take our cut and then the returns are sent to the growers.

Invoicing

Freight companies will send an invoice once a month covering all consignments they have transported for us and we need to allocate the total amount across the consignments and deliveries based on the number of cases of products so we can monitor costs and the profitability of specific fruit products for pricing discussions with the growers etc.

Invoices for things like clearance checks will come in for potentially specific parts of a consignment but equally for multiple consignments and again we need to allocate the amounts across the delivered products

We will invoice clients for the amount of product they kept and this information needs to be logged against delivered product, there is also credit notes that can be issued to and/or from companies we deal with and these need to be logged against specific delivered product

----------------------------------------------------------------------

Currently ALL of this is maintained in a spreadsheet and each delivered product is a line with repeated client information for the delivery and repeated information for the consignment. The invoicing information has ended up being a large number of columns of repeated information. Sometimes if they have run out of available columns they have added comma separated invoice information so invoice ref 1 refers to amount 1, ref 2 (separated with a comma) refers to amount 2 etc etc.

I have extracted the information that the users of the spreadsheet enter, broken it up into the unique parts and created tables to represent that with the relationships. So as documented previously we have:

• Consignment table: containing the unique information for a consignment that is supplied to us that is currently repeated multiple times for a delivery
• Delivery table: containing the unique information for a delivery to a client, a client may have 1 product but also can have many products depending on what they want from the consignment, but the same lorry would deliver it and it goes to the same destination (as an example)
• Delivered Product table: containing the unique information of the specific product delivered to the client. this would be number of cases, number of pallets, prices agreed for the product etc.

What i need to be able to do is create invoice information that is linked to a number of consignments with a total amount and then allocate that total amount across the delivered products that ended up at a client. So as described above i have created:

• Consignment allocation form:
  • this shows ALL consignments and allows filtering based on a number of different criteria
  • once filtered the user selects from the filtered list the consignments they wish to allocate the invoice to
  • selected invoice general information (who its from, status, amount etc) is entered and an invoice record is created along with a bridging table to link this invoice to all the consignments selected
• Delivered Product allocation form
  • Each invoice record is shown along with delivered products that relate to it
    • the delivered products are obtained by following the FKs back to the consignment
  • the user can filter the delivered products based on a number of different criteria
  • the user can select specific delivered products once filtered
  • **** the user can then allocate the invoice total to the selected products ****

So the point i am at is allocating the total amount across the selected products

----------------------------------------------------------------------

I hope this all makes sense, please let me know if there is anything that doesn't.

 

[LGDGlen]

reading a bit more into things should the Delivered Product allocation use some sort of temporary table generating records when the form is open pulling data from multiple tables including the amounts already written and the write the data back to the actual table for any records where the user has entered values into the amount fields

i'm learning and reading and learning as i go here so again this might not be the best way but as i envisage it (from what i've read)

• FACT-DeliveredProductInvoiceAmount table:
  • PK - autonumber
  • FK - invoice ID
  • FK - consignment ID
  • FK - delivery ID
  • FK - delivered product ID
  • Amount
• temp-DeliveredProductInvoiceAmount table:
  • PK - autonumber
  • FK - invoice ID
  • FK - consignment ID
  • FK - delivery ID
  • FK - delivered product ID
  • Amount

When the form opens i create the temporary table and create records for all the delivered products, then load the data from the FACT table so any data previously entered is available.

When the form closes anything that has an amount and is a new record (as in doesn't exist in the FACT table) new records are created, any record that already exists if the amount is different it is updated

Again i don't know if this is the right way to do this at all but just doing forum searches i'm getting potential answers but whether they are the right way to go is the question

 

[jdraw]

Here is a very overview draft of what I understood from your posts. My intent was to understand the processes and to propose some tables and relationships to assist with communication. You know the details better than any reader. You can comment/ignore/adjust as you see fit. I also recognize it is difficult to step back when you are working with large, detailed spreadsheet and trying to deal with database concepts.
I hope it's helpful.

 

[LGDGlen]

@jdraw thank you for your efforts it is really appreciated and i see where you are coming from with the diagram but it leads to the question i think i'm wrestling with and that is:

I need to be able to display all the ProductInDelivery for multiple consignments and have a data entry field that can be used to split the total amount across the ProductInDelivery records selected by a number of different standard methods or by hand.

So what i end up with is a continuous form with fields brought in from multiple tables to identify the deliveries so they can be filtered and then i have no idea how to have a field for the related costs when a related cost record wouldn't exist until an amount is entered

(i really hope i have described that correctly but if not here are a couple of screen shots showing the process i have at the moment which is i know not quite what you've described but might help)

User navigates to a specific Invoice record and can see all the consignment/delivery/delivered products information that is required to filter:

They can then say they need to filter by the vehicle registration so get to the relevant deliveries to allocate the amounts to and then select them like:

The user can then click the

• Split By Cases button to generate:
  • 11643.27
  • 42677.73
• Split By Pallets button to generate:
  • 11643.27
  • 42677.73
• Split Equally to generate:
  • 27160.5
  • 27160.5
• Or manual amounts

So based on your diagram you have RelatedCosts which i believe would be setup like this:

• PK - amount_id - Autonumber
• FK - invoice_id - Number
• FK - del_prod_id - Number
• Amount - Double

Which I have done and created the relationship between the tables but when i add the field to the continuous form now no records are shown

again i might have misunderstood things so happy to learn where i'm going wrong

 

[jdraw]

Can you post a copy of your test database? And can you describe how pallets and cases are related to a consignment and to a client/customer.
You should be able to take some test scenarios, along with some test data and "confirm your database structure". This is where changes can be made without major impact on your project. The key is to work your model with various scenarios and prove to yourself that the data values needed for a particular scenario are available. That could be a mock-up form or report/invoice or a calculation.

Continuous forms and temp tables represent HOW something could be done. I think you're still dealing with WHAT is required for what process/scenario.
As mentioned, it may be helpful if you could provide images of a consignment notification and invoice etc.
Good luck.

 

[LGDGlen]

@jdraw i'll have a think about what you've suggested. i'm not sure i've explained myself well enough yet if its not clear what the process/scenario is as tbh i know what is required for the process/scenario and believed i'd described it fully but i'm guessing i have not. i can't provide copies of documents as they would basically be a bunch of grey boxes where i've had to redact business info to the point where they'd be pointless unfortunately.

i really am at the point of HOW not WHAT but again i'll try to clear up my test database of sensitive information and post when i can

 

[jdraw]

Can you show us a copy of a consignment notification? I think the issue is the level of detail/granularity as Markk mentioned. You know details - data and processes - that readers don't.
If you understand the logic involved to take a Consignment and decompose it into Cases of Delivered (Sold) Product to Customer, can you show us the steps involved?
From test data and test scenarios and a current data model, you may find that stump the model will guide you to a solution. (You may find this tutorial helpful generally)
Use very specific scenarios, mock-up/use parts of a given notification and work through the logic to get costs for Product sold to a customer. You know the environment better than others.

If things are working as per your post #13, what exactly is the issue? Can you post some related code?

 

[LGDGlen]

went a different route which solved all the issues above so i have resolved the issues. i am going to mark this solved because it has been and thank all for the inputs and assistance.

if anyone wants to know what i did in the end i can explain but it might be a bit too long to explain