Order management system

[garywood84]

I have little experience using Microsoft Access, but am attempting to create an order management system.

I have designed a system to store customer details and allow the entry of orders. I now want to develop this so that the system can handle delivery notes.

There are currently two tables for order information, since each order can consist of multiple items. So, general information, like the order date, order number and salesperson are stored in an orders table. A second table, Order Detail lists the items and item specific information along with the order number of which they are a part.

A whole order (i.e. all items assigned to one order number) may be delivered at once, or in smaller consignments. However, every consignment must have a delivery note with a unique number. So, there may be several delivery note numbers associated with each order, relating to different shipments.

I therefore need to create a system which allows the user to select which items from an order are being dispatched and then get Access to do the following:

• Create a new record in the delivery notes table. The delivery note number will therefore be created (it's an autonumber). The user should be prompted to enter the delivery date and this should be stored in the date column of the delivery notes table, alongside the delivery note number.
• Enter the delivery note number in the "Delivery Note Number" field of the Order Details table, for every item that the user selected as dispatching.

Once this is in place, I will then be able to pull all the required details together with a query and then print the delivery note.

Bearing in mind that I'm very new to Access and so don't have any knowledge of scripts/macros etc, can anyone tell me how I can achieve this?

Many thanks in advance,

Gary

 

[elbweb]

ok.. now this is what i see you want:

an Orders table that holds the main informations.
linked details tables that hold more then one detail for each order.
a table of notes, one for each cosignment.

therefore one other tables should be created to link all of this, in my understanding:

tblCosignments that links the notes to order details, so you know which details are a part ofthat cosignment

i attached a sample db with just tables and reltionships, if this is right, i could try to go a little further and try to show about the forms, etc.

 

[garywood84]

elbweb,

Thank you very much for this. I really appreciate you taking the time to put this together.

I think what you have done is what I'm trying to do, though I'm a little confused since the tables don't contain my fields and I'm not sure what your descriptions mean where "info" features in more than one table.

This is what will be stored in each of the orders tables:

tblOrders:
Order ID
Job No
Customer PO No
Date Required
Ordered by
Salesman
Value
Comments
Inspectors Comments
Complete?

tblOrderDetail:
Order ID
Item ID
Item Description
Material
Unit Price
Quantity
Drawing No
Drawing Issue
Delivery Note No.

Every order will feature only once within tblOrder. However, if order 1 is for six different items, there will be six corresponding records in tblOrderDetail. Say only two of these are ready for dispatch. I need to print a delivery note which only includes those two items, recording the delivery note number somewhere so I can look back at which items were in which delivery number.

As I say, I think what you've done is on the right lines for this, but I hope the above clarifies and will enable you to guide me further.

Just in case it helps, what I was thinking of having as tblDeliveryNotes is:
Delivery Note No.
Delivery Date
Invoice No.

Many thanks again for your help,

Gary

 

[elbweb]

OK first,i recreated the database with the fields you wanted, with a couple of changes (i.e. the 'Value' field shouldn't be used because the word value is a reserved word , used by the program, so i renamed it ValueAmount). Also i put in the corresponding datatypes, i believe would be used, there were a couple i wasn't sure about, so sorry if i messed that up.

Here is an example of how the database would be used, it might be hard to code to use this way, but this is what i think should be done:

we are going to use your example:

Every order will feature only once within tblOrder. However, if order 1 is for six different items, there will be six corresponding records in tblOrderDetail. Say only two of these are ready for dispatch. I need to print a delivery note which only includes those two items, recording the delivery note number somewhere so I can look back at which items were in which delivery number.

tblOrder would have one entry, for the main order information. (OrderID of 1)
tblOrderDetails would have six entries for the six parts of the order (each has an OrderID of 1, and Item ID go from 1 to 6).
now is the tricky part:
tblDeliveryNotes would have one entry, for this one delivery (OrderID is 1 to relate back to the first record, and NoteID is 1 for this new Record)
tblCosignments would have two records for the two items that were ready to dispatch, which are part of this new note. (Both would have a NoteID of 1 relating to this last record, and then the first would have a OrderDetailID of 2 and the next would have one of 3 for the two different parts of Order 1 that were included in this record)/

now that is awfully confusing so i have set up some examples that im including in the database, here is what they are (pardon my lack of creativity )
most of the data is just fluff so its easier to understand.

First Order Information:
OrderID:1
JobNo:333
DateRequired:7/29/2006
Salesman:James
ValueAmount:$23.00
Complete:Yes

Second Order Infromation:
OrderID:2
JobNo:334
DateRequired:7/29/2006
Salesman:Alyssa
ValueAmount:$340.00

Now the first order had 5 items sent out in 3 shipments:
Items:
Toothpaste tubes, bowling balls, turkey sandwhiches, a big red bus, and an old muffler.
The three shipments were:
1 - Toothpaste and the old muffler
2 - the turkey sandwhiches
3 - the bowling balls and the big red bus

And the second order had 4 items in 2 shipments:
Items:
some old paper, a crystal ball, a new pack of orbitz gum, and a set of screwdrivers.
The two shipments were:
1 - the paper and orbitz gum
2 - the crystal ball and the screwdrivers


now all of this info is in the DB so you can look at the samples, hopefully it will make sense.
i have a feeling it is going to be a lot harder for you to create a user interface then it will be to understand the database.

im here for more questions, you might this of a better way to do this, but the intermediate table is the only way to have the notes and the corresponding details to be related

 

[elbweb]

woopsy i forgot to attach my db

 

[garywood84]

elbweb,

Many thanks for this. Reading your explanation as well as looking at your example made clear the relationship required between the order data and the delivery notes.

I have slightly modified the idea of the consignments table to include that information as additional fields in tblOrderDetails and have started to develop the database further.

Thank you again for getting me on the right track. I appreciate you having taken the time to put the examples together and I'm sure I'll be back here soon when I find the next problem I can't solve in developing the system!

Cheers,

Gary

 

[elbweb]

and when you come back, I'm sure we will be right here to help you through it. But remember if ya don't come back with problems, i'd like to see the final project just to see how it all evolved

 

[garywood84]

elbweb,

Thanks for this. I'm currently developing two databases: one for my use at work and the system for which you've been advising me, for a relative to use in their company.

My work one has taken priority since the weekend but I'm hoping to get back into the Order Management System over next weekend, so I'm sure I'll be back here with more questions then!

Cheers,

Gary

 

[elbweb]

hope you have fun with work

and feel free to pm me if i'm not around and no one is answering your questions, that seems to be the only way to get my attention ...

 

[garywood84]

Cheers elbweb.

Watch this space... (and your PMs)!!

Gary

 

[garywood84]

I've finally got around to looking at this again. I have a setup like the one elbweb proposes above. To recap, there's a table which holds orders, linked to an order_details table by ID number. This is because each order can consist of multiple lines (so each line is in order_details, linked to a single entry where the order number is allocated in the orders table).

I need my system to print delivery notes. Deliveries may consist of:

1) All items from an order
2) Only some lines from an order
3) Some/all lines from multiple orders.

I need a way in my database to be able to identify which items should are ready for delivery, and print the delivery note for them. This will involve creating a record in the delivery notes table with the date and delivery note number in it, then entering the order_detail_ID for each item into a delivery_details table with the delivery note number alongside it. Once that's done, a delivery note report can be run to create and print the note with all the required items on it.

I hope this makes sense so far - it's difficult to explain, so please let me know if you're not following me!

I'm thinking that there should be a form for each customer which displays (as a subform?) all the items they have on order which are not yet delivered. There then needs to be some way for users to select those ready for delivery and run a script which does the above record creation.

Does anyone have any ideas what the best way of achieving this will be? I'm sure other people who've made order entry systems must have encountered this issue, so would appreciate any advice.

Thanks,

Gary

 

[garywood84]

I've not been working on this for a while, but have just returned to it to try and finish it off. Can anyone answer my questions in the above post, or point me in the right direction?

Thanks,

Gary