Orders/Delivery Notes with partial fulfillment capability (1 Viewer)

[jt196]

Firstly, I'd like to say that this is my first post on the forum - it seems like a great community - so a big hello to all people on here, and a big thank you in advance.

I'm also a newcomer to access, so please treat me with kid gloves and assume that I'm not an expert in the level of detail you give in your advice.

Currently I'm trying to create a database for a manufacturing company and I'm a little stuck with delivery notes and the possibility to partially fulfill them.

I have four tables (among others like customers and products):

Orders

OrderDetail

DeliveryNotes

DNDetail

The Orders is linked to the OrderDetail table as you would expect, delivery notes is linked to the Order table (by the key field), and DNDetail is linked to OrderDetail (by order number) and the DeliveryNotes table (by DN number).

Currently I have a form up and running to enter orders into the database, but the DeliveryNotes form seems just a bit weird.

I'd like to be able to create a form that allows me to select an order, select the items on it that are shipping out, and the quantity that is shipping out - then have that amount be subtracted from and amount due column in the OrderDetail table (OR added to an amount delivered column in the same table OR somehow work in a query so it shows up that the order has been fulfilled somehow....)

It's all a little vague I know, but that's my level of experience I'm afraid. Sorry about that... I know I need to go away and learn more about Access, which I'm doing 3-4 hours a day of, but any help here would really make a difference to what I'm doing...

Thanks in advance!

JT

 

[PNGBill]

I would have considered one table to hold transactions ie details of any documents.

When you raise an Order you have a TblOrder with OrderNum (maybe key) and you identify Cust, but when you enter details for OrderNum such as Sku, QtyOrd plus a few others they are in a subform with data held in TblTrans.

You Have a TblDelDoc that has fields DelNum (maybe key) Driver, check box to confirm delivery done etc but again OrderNum, Sku, Date, Qty, Driver, etc. are input in a subform as data for TblTrans.

A query can search TblTrans and Sum deliveries by OrderNum and Sku.

This would be presented on a form and allow you to (or automatically) flag the OrderNum as complete, for a given sku, where the sum of DelNum = OrderNum qty.

Of course there are many ways to alert/prevent delivery qty exceeding order qty etc but my point is that the actual transaction ie sku qty movement are held in one table only.

Each record will have OrdNum as field and some will also have DelNum in a field.
TblTrans key could be autonum and the query would provide data for a form to track situation of each Order and Sku

Trust this does not confuse anything.

 

[jt196]

OK so a few questions here to clear up what you getting at here.

1. So you're saying to have single TblTrans to contain BOTH the delivery note and order details?
2. Does that mean that the extra fields for deliveries (eg driver, delivery quantity etc) will just end up being blank if it's an order?
3. So how would a query work to flag the order as complete? If the order qt = del qt then the order gets flagged as complete? Is that a VBA thing or is there something more simple in the GUI to do this?
4. How would this look on my order schedule? If I want to put the original number, quantity shipped and quantity remaining, the first two would come from the TblTrans (albeit different entries) and the qt remaining would be the difference?

Like I said, I opened Access for the first time about a week and a half ago, so I'm still not 100% familiar with the database concepts so excuse me if there are any dumb questions.

Thanks in advance!

 

[jt196]

OK Bill - I've managed to get this system up and running. A couple of pointers as to things I had to do (that were a bit beyond my knowledge and have taken me since my last post to figure out).

In response to my questions:

1. Yes!
2. Yes!
3. I've set up a query that totals the 'transaction' items (delivery notes or part of an order) and then deducts the totals from one another. I had a few problems here because when I was entering the information on the delivery note subform it wasn't entering the order number for the item on the transaction subform. I had to link the child and master fields in the relationship of the DN/DN item subform with not only the delivery notes but the order number too. I'm not sure how this is going to effect the entering of items on the system without a delivery note - but that's a little further away.
4. For my order schedule I had to make a new query because the first query taking the total due amounts couldn't be filtered. I created a new query that queried the first query containing the totalling and filtered that one - which seems to work. Now I've got a nice report up and running with our order schedule - containing everything that doesn't have an Quantity Remaining value of 0.

Looks all good so far - now I've got to figure out how to update combo boxes based on previously selected criteria in a box in the form... Any ideas on that one?

Thanks!

 

[PNGBill]

Glad you are getting used to access. At this level we do things a few steps at a time and then as you gain experience you can reduce the steps with smarter queries and vba but for now your project appears fairly simple so take it slowly and get each step working before you go on.

Unique details of each delvery could be stored on the TblDelNum so you keep the TblTrans with basic details such as OrdNum, Sku, (all records) and DelNum Where a Delivery is made.

Your Order Entry Form may have a master form with unique details of customer, date delivery address, special notes eg ring tel num first etc.
The subform will hold Date, OrdNum, Sku, Qty - all adding records to TblTrans.

Your Delivery Docket Form will be similar with main form allowing you to hold details such as dateraised, datecompleted(this could be your check completed field), driver, vehreg, etc a subform that allows you to add records to TblTrans showing OrdNum, Qty

So TblOrd hold a number of fields and so does TblDel.
Just Details related to Sku and linking to OrdNum & DelNum are held in TblTrans.

You may then have a number of queries that will provide data for your various forms and subforms to keep track of Orders, Pending Delivery Orders, Completed Orders etc.

A delivery Docket may be complete when the sum of all sku's qty ord = ditto for del. Query would sum all records in TblTrans without DelNum for an OrdNum and then Sum with DelNum.

You could show details of delivery by delNum and the list gose on.
The main thing is that every entry in TblTrans must have an OrdNum (how else would you want a record except if an order has been placed), sku & qty.
When records exist with the same OrdNum & Sku (of course you have a TblSku) with DelNum field not nul and the sum of these qty"s = sum qty without DelNum then order is complete - provided Datedelivered is also not nul.

Combo Boxes.
Not sure exactly what you want to achieve but you can also use form control buttons and further subforms.
eg, if a form tells you the order is complete you could have a control button that opens another simple form that allows you to enter a date in the date completed field for that DelNum on TblDel. Just select a place on the form or subform that will get you to TblDel.
As you gain experience you can have the button anywhere on the form and also add events to these controls that will alert/prevent you performing the task if the result is not true eg an order is not complete if DelNum qty sum is not = OrdNum qty sum - do you have a DateCom in TblOrd ?

You can add colour to your form so a textbox will be red if showing.eg if a query returns DelNum's where qty sum agree then the text box will have an entry on your form and be red alerting you to mark this delivery as complete/enter date or panic if you know it is not complete...
and if nothing then the text box shouldn't show and no red writing on your form.

Trust this long winded story assists.

 

[jt196]

Bill - you're a legend! Thanks for the help here. Looks like we're working at opposite ends of the clock - wait a minute, is it 7am Friday or Thursday there??? Ha ha.

Well I think I've got this sussed out here. There is a single table for all of the incoming orders and outgoing items. Each item of both has an order number attached, but only the outgoing items have delivery notes attached. The order items have a field containing 'quantity ordered' on them, and the DN items have a field completed with the 'quantity shipped' on them. This seems to work because if a delivery and an order contain the same order number then the totals can be totted up and put on an order schedule. Oh and by the way, as I said before - orders can go out in parts - so we often need multiple DNs for single orders as they often need to sign a docklet if they're picking up the goods.

Now I've created a query with outstanding orders (ie orders where the balance of items isn't zero), and need to be able to create a form to produce delivery notes using a combo box that refers to this information. Is this possible? Otherwise I'll end up with a massive dropdown list of ALL of the orders that I've made.

I'd also like the dropdown box in the delivery note that refers to the items on the delivery note to refer to only the items in an order. Is this going to be possible. Tentative research has shown that I'd need to enter some kind of 'AfterUpdate' behaviour in the order select Lookup combo box. This would also be useful to have when entering orders. We manufacture goods mostly with a 'goods A' goes to 'customer A' relationship - or the goods will be received on a regular basis by only a handful of customers. So when the customer is chosen in the order form, it would be incredibly more simple if only the products that the customer receives are then displayed in the Lookup combo box (reducing the dropdown menu to a couple of items rather than a few hundred).

Any hints on these next steps. I've been working on this all day and with your help I've managed to jump forward quite a few paces - so thanks a million so far!

 

[PNGBill]

Located in Port Moresby, Papua New Guinea local time 07:50 hrs.

When you do a query you often get a large result selected but referring to TblTrans, they all have OrdNum and some have DelNum.

In your form, go to your toolbox and select control button. This will step you
through the process and ask if you want all records shown or just records that match a field name. Here you may select records that match by OrdNum, DelNum, Date, Sku, or even CustNum if included in the query.
This works for subforms as well.
if you have a subform to show partial deliveries then the query will show all partial deliveries and the basic subform will do this but when you use your toolbox to create this subform in your main form or create a control button that opens another form then you have the option to only see the matching records (when you create the button).

Say you have a form selected from a menu that is headed up "Manage Deliveries"

You could have buttons all over this that list Customers (may open a form to do this) and then identifies an individual customer and then that may have buttons to show view all orders, all deliveries etc. You can go on like this all day long but the "system" allows this to be done quite easily.

Later, you will be able to have less buttons that do more but for now the standard access toolbox allows you a lot of options.

If you manufacture products for individual customers then have CustNum on your TblSku so you can easily view all products by Cust and of course again, go on all day with your forms to show deliveries and orders.

As you may have guessed, I am yet to master VBA so have tried to make everything simple

 

[jt196]

Got it with the combo box/query selection! That makes things a hell of a lot easier now. Thanks for that! I'm getting there...

Perhaps I didn't explain the autoupdating fields properly. Let me give you an example:

There are 3 companies:

Company A
Company B
Company C

And 5 Products:

Product 1
Product 2
Product 3
Product 4
Product 5

Company A only orders 1 & 2, Company B - Product 3, and Company C Product 4 & 5.

So for example, on choosing Company A in the order section I would like the product combo box to only display Products 1 & 2. If I select Company B, it should only display Product 3 - etc etc.

Any ideas?

JT

 

[PNGBill]

Your combo box would link to a select query that would only show orders or products etc for the selected company.
The filter can be done in the query ie, it asks for a company id to be entered or if your form has already gone to the company then the toolbox action will allow you to restrict the records to CompanyName on both sides so you only get the selection you want.

I don't use combo boxes much so a bit rusty on this issue but on the main form you have already selected Company A and all details of this company are showing in your OrderHeader ie, telnum, Contact, delivery address etc and you now have subform that is waiting for you to add the products now being ordered.
Instead of a subform you could have the main form and a button to say new Order ( and many other buttons to track activity with this customer ) and this button would then add a new order form that is only allowing the products available to company A because it is working on an Append query that uses TblSku and when you made the command button you restricted the records to match CoyName. The query will have additional new fields to rec data required to update the TblTrans when you decide it is ok.

You may have to suse this out a little but it will work. Another option is to create a temp table to hold all of your new order data and when you are happy you click on a commend button and this appends temptable to TblTrans.
A macro can help here so the button to Confirm The Order actually starts a macro that may do a number of things including Appending TempTable to tblTrans and deleting TempTable as a last action to avoid an order being entered twice.

Experiment with macros, they are so easy because unlike excel macros they are really just a list of actions to take one step at a time and mostly consist of queries that manipulate and action data after the prev and before the next step.

So, one macro could present you with a form to enter a new order and another could confirm the order, update TblTrans, and present you with a Delivery docket all made out and ready to edit ie it assumes you are making one full delivery and you just delete a line and or amend the qty to reflect what will happen on this delivery docket. The form may show the qty on hand of each sku.
When you then confirm the delivery docket another macro may give you an option to raise another delivery docket, delay this process. Some system insist that all orders have a delivery docket and you just edit the qty and raise a new docket for the balance until there is no balance left.

Just all done by queries, forms and macros.

(TblSku should have ComID and then you can have as many sku's as you like for as companies as like but only CompanyA will show in your form).

You have a textbox for qty

 

[jt196]

Phew! Thanks a lot for all that help, Bill. Even though you're on the other side of the earth, I feel like some good mentoring has gone on here and your advice has helped me endlessly to get there. So thanks for this and if I'm ever in PNG I'll buy you a beer.

I'm pretty much at the end of the first stage, which is getting to the point where the factory floor have a switchboard that presents them with three buttons:

1. Order Schedule (so they know what's going on and what to make/when etc)

2. New order (when an order comes in - it goes on the system)

3. New Delivery Note

I'm keeping it simple at the moment so they don't have too much brain strain, 2 out of 3 haven't used a computer before.

I've just spend the entire day sorting out the combo box subform autoupdate on select in main form. It took a while and plenty of headache but I've managed to get it working. As usual, it was simpler than I expected, but knowing exactly took quite some time (giving me a better understanding of Access and queries etc).

Say you're looking to make the items on your subform match the order that you selected on the mainform. Firstly, you'll need to select the combobox in the subform. The query for the datasource for the product will need to include one of the fields from the order combo box on the main form. In the query edit section of the combo box, in the field that you're matching to the mainform combo box (in this case the OrderID is what matches the order that you select in the first combo box, and the products, that you'll be given in the second in the subform) you'll need to enter:

[Forms]![A]!

Where A is your main (master) form name and B is your field that you're using to match. Mine came out like this:

[Forms]![F_DelNot]![OrderID]

I had a little difficulty here because this gave me duplicates if any products had been delivered from this order already - so I used a query as the datasource that summarised the products (in and out) on the orderDetails form.

Finally, this needed to be saved and importantly - when the first combo box is selected, this information needs to be passed down to the second combo box. After a lot of googling and finding a lot but not a lot that worked, I found the right VBA script. This little nugget needs to be placed in the VBA code of the AfterUpdate box of the first combo box:

Me.[SF_Ord]![ProductID].Requery.

This seemed to do the job. Well, I'm pretty chuffed with that one. It took a lot of work, but I'm finally well on the way.

 

[PNGBill]

Good to hear. I have been offline all day sorting out a new asset register for our business.
I have not used combo boxes much, maybe should but most activities are covered by command buttons that either start a macro (order list of queries, forms, reports etc), Open a new form, produce a report etc.
Keep using VBA if you can because this will make life easier - I need to work on this myself.
Regards,
Bill

 

[jt196]

Aaah VBA - yeah I'm not even close to understanding that yet, it was more like a bunch of cutting, pasting and trial and error - but I'm probably going to get started on the chapter this afternoon...

As for combo boxes, I felt that they were a necessity as we have a finite register of customers, suppliers and products and I'm basically going to be dropping this little database down on the guys downstairs and they're going to have to start using it asap without much computer experience. So in my mind having a combo box where they could choose from a finite group of our customers or products was more or less a must.

Now when they want to place an order, they choose the customer, and the list of products is limited to the products that customer buys (and more or less its all just 1 product=1 customer - so it's easily manageable.

The products that have a 'RegBuyer' field in the Products table that is a lookup with an option to select multiple values. Products that have more than 1 customer are selected accordingly with multiple values.

Anyway, it seems to be working now - and I feel like I'm over that initial hill to get this thing off the ground. Later on I'm going to be customising the database to manage stock levels, quoting, material suppliers and timesheets...

But as they say in Spanish - poco a poco - bit by bit.

J