Need to Create an Order system

[Toffy]

Hey there i am very new to access and am having trouble wraping my head around the logic and method needed in order to create a data base that records weekly orders.

What types of tables do i need?
How can i input more than one product per order?
How do o group the information in a organized way?

Ideally i would like to Input the clients name the week of the order and then all the products etc that are ordered.

Then be able to see in an orders table each client that made an order and a subdatasheet (the small plus in the corner) that once clicked i see all the products that are in that order?

Any chance someone could give up their time in explaining in layman's terms the logic behind creating a database to do this? and What tables would be needed.

:banghead::banghead::banghead::banghead::banghead: Its just one of those nights

 

[CJ_London]

You need to read up on database design

In principle what you are trying to achieve is to store data only once. So in the case of an order you need two tables, one for the order header information and one for each line of the order - and perhaps another one for products

In addition you will need a table for customers and if they can have multiple addresses (delivery/invoice/etc) then another table for addresses

Each of these tables will contain a uniqueID -known as a PK (Primary Key) - usually an autonumber field - the number means nothing in itself (and should not be used as meaningful data such as a customer account number or order number), it just uniquely identifies each record in the table.

In each 'sub' table such address, in addition to the PK you also have an FK (Family Key) which is the link back to the unique customer record.

Similarly the order line table perhaps has an FK linking to a product table and will definitely have a FK linking back to the Order Header and this table in turn will have an FK linking back to the customer.

Some tips.

• Avoid using reserved words (Name and Date are popular examples) see this link http://support.microsoft.com/kb/286335
• Avoid spaces and unusual characters (such as #) in your table and field names - settle on a convention such as myTable, orderID or MyTable, OrderID
• DO NOT use the lookup facility in table design - seems a neat function at first, but I can guarantee you will waste hours of time later when you don't know what you are looking at.
• When asking for advice on this forum, post your code and what it is supposed to do, don't try to describe it and then say 'but it doesn't work' - there will be a manifestation of why - perhaps it produces too many records, or none at all, or you get an error message

 

[Mihail]

Some additional advices from a newbie (at least if you compare me with CJ):

• If it happen to know how to deal with Excel worksheets is time to forget as much as you can from this knowledge. The tables are NOT worksheets.
• The user (even you will be a user after you design the DB) will never need to hit the small plus in the corner in order to see the table's children. There are other tools for this task.
• You will never need to copy data from one place to another.
• Never need to store a calculated results somewhere.
• Can't insert formulas in tables. This doesn't mean that you can't use complicated formulas but, again, there are specific tools for this tasks.

Good luck !

 

[Pat Hartman]

When you open Access and create a new database, one of your options is to start with a template. Depending on your version of Access, there will be one or more options of order entry applications to choose from. Create as many of these template starter applications as you want and examine each of them to see which will give you the best start.

 

[Toffy]

Thanks
So far i have updated my relationships because they were not done correctly in the first place, i managed to create some combo boxes but not baced on 2 different fields still having trouble with that...

I also dont think im out of the woods yet because in my order details table it requires inputting the exact order number product number and client number instead of the name...

How would i get the user to chose the product and the product id would generate accordingly? i ask specifically for the product because i have 3 fields to justify each product thats ProductName ProdSize and ProdUnit (model).

If the user were to input these 3 variables through combo boxes how do i get access to generate the product id in the order details table?

 

[Mihail]

Post the relationships window (a picture).
Ensure that the windows for tables are large enough in order to show us all the fields.
Until you will rich 10 posts you must ZIP anything in order to upload to the forum.

 

[gemma-the-husky]

one other thing, added to the observations of Mihail.

do not try and design an excel type system that uses the preceding spreadsheet row to calculate something. you have to try and avoid this if at all possible, and it certainly should be possible.

 

[Toffy]

Hi guys i realized that i was a bit over my head and took a few steps back using the sample north wind data base as a stepping stone to correct my tables and relationships.

Attached is what i have so far in relationships but now i need to add a few things to my data base that work for my company and not sure how to go about it.

One of the things i want to do is as follows:
In my orders form once the user selects a client i want the correct products to show up in the order details drop down menu.
i'l explain

We have more than one market for the purpose of this example 2 (Fishing and Terra)
If the client is from the Fishing Industry i want the order details to only show me the products in that table list and vise versa.

I have separate product tables for each market and have made a market field in the client table. As well as a market table to add more as we expand. How do i get that field to tell my orders form to only show the correct markets products, but at the same time have all orders only show up on one table for invoicing purposes?

Thanks

 

[jdraw]

Work through this tutorial.

see the links here for more info

Good luck

 

[Toffy]

Watched the videos and it was good for a bit of background information but i still dont see how to make this relationship work.

Depending on the client i need the order details table only show certain products...
Would i combine all products in one table instead of 2 separate ones and then make a field in the product table for "category" listing under what category it falls under?

Still doing so how do i get the combo box to only show the correct category of products based on client?

Secondary question i think falls under the same type of problem i aswell would like to be able to have varrying prices with every client, each client has special prices depending on items etc. How would i go about this for every client to have potentially his own unique price??? (without manually inputting a discount for the products off of a base list price table) at the moment having everything in excel it is vary easy to do but would like to consolidate everything in my database.

:banghead:

 

[Pat Hartman]

If one product only comes from one supplier, you can put the SupplierSourceID in the product table. However, this is very restrictive and inflexible. A better solution is to create a junction table that combines SupplierID an ProductID. This allows any supplier to provide any product. You would use this table to populate the combo. Use a query that selects the Products for a supplier.
Select .. From tblProducts Where SupplierID = Forms!frmOrder!cboSupplierID
Order By ...
Then in the AfterUpdate event of the Supplier combo, requery the product combl.
Me.cboProductID.Requery