Order Entry / Stock table

[Hyperviper]

Hi All.
I have 20,000 items in a stock table listed by
ID
Part Number
Description
Current Stock
Re-order level
Location.

I have created a form based on a table which will list the following:
Customer ID
Order ID
Part Number
Description
Quantity Required
Price each
Sub Total.

At present i have a Customer address Form linked to the order Date Form with a Order details Subform. I then also have a seperate Stock Table, listing all the parts, which i imported from Excel.
Basically i need the details that are entered in the order details subform to be related in some way to the stock table - so the items there can be updated as the orders are entered. Could anyone help me with this as right now the Stock table sits on it's own - not linked to anything, does it need to be?
Is it just a case of creating a query - if so what do i need to do as i seem to be missing something...
If you need more information please let me know.
Thanks Folks

 

[Emohawk]

The table containing the order details will have to have a field called Part Num that relates to the Stock Table-Part Num field.

Post the details of you current Tables (i.e. fields) if you need a bit more help.

 

[Hyperviper]

Thanks for the tip.
I currently have the following tables:

Customer Table;
Customer ID
Name
Address
Postcode
e.t.c.

Order details table containing
Customer ID
Order ID
order date
Delivery date
notes
plus 5 spares

Order details Table has the following :
Order ID
Stock ID
Part Number
Description
Quantity required
Sales Price each
Sub total
Plus 5 spares



Stock Table with the following:
Stock ID
Part Number
Description
Stock Level
Re-order level
Location
Supplier
Plus 5 spares


Relationship wise they are attached
Customer ID(CUSTOMER TABLE) to Customer ID( ORDER TABLE)
Order ID(ORDER TABLE) to Order ID( ORDER DETAILS TABLE)
Stock ID (ORDER DETAILS TABLE) to Stock ID (STOCK TABLE)

Order Details Table has two links as i think that's correct.. since a product can be in more than one order?
Any suggestions on what i do now?

Thanks for your help..

 

[Alexandre]

TblCustomers (you probably have it)
- Customer ID (autonumber PK)
- FirstName
- LastName
etc.

TblParts
- PartID (I suppose this is your autonumber PK)
- PartNumber (I suppose is an internal serial number? Avoid spaces in field names)
- Description
- CurrentStock (Humf. It is generally not a good idea to store calculaton results. Ideally you would strore the initial stock, then from the orders table you would get the ordered quantities and calculate the difference at any moment)
- ReOrder level
- Price
- Location


TblOrders
- OrderID (autonumber PK)
- CustomerID (foreign key)
- OrderDate
etc.

TblOrdersDetails (This makes the link between orders and ordered parts)
- OrderID
- PartID
- PartQuantity

Relationships:
TblCustomers 1:____:Many TblOrders 1:____Many: TblOrdersDetails: Many____1: TblParts

Note that the above strucure still have important limitations: if you want to change the price (according to the evolution of prices) of a part or stock number (according to refurnishments) you loose any historical track of the amounts of previous orders as well as of stocks evolution.

A better structure would involve a separate table for parts (with no indication of prices nor of the amount in stock), with a one to many relationships to a PartPrices tables where you would store prices per period of time (DateBegin DateEnd Price) and a one to many relationship to a Stock table.

 

[Fizzio]

Your order details table does not need both Stock ID AND part number (you can derive the part number from the StockID)
Rename your 2 order tables to be a bit more descriptive ie tblOrders and tblOrderDetails
Your relationships ar ok

Customer ID(CUSTOMER TABLE) to Customer ID( ORDER TABLE) 1 - Many
Order ID(ORDER TABLE) to Order ID( ORDER DETAILS TABLE) 1 - many
Stock ID (ORDER DETAILS TABLE) to Stock ID (STOCK TABLE) many - 1

I would consider removing the spaces from tablenames and fieldnames (it makes them easier to reference) and I would also consider removing the StockLevel as this can be calculated from all of your orders and (in general) it is better practice not to store items that you can calculate (unless prices change etc)

You now need to decide what and how you want to display and enter the data. You are almose certainly need a Form/subform setup. To give you some good start points, look at the Northwind example that came with Access.

(posted at the same time as Alexandre but you get the Gist - Hurry up fingers )

 

[carboxylic]

Hi all,

Ive been searching all over this forum for something that sounds like mine.

Im doing a stock control system in Access with VBA.

Right, Im having trouble with ERD's and tables.

The Entities I have, like the threadstarter are simmilar so:

MANUFACTURER - contains details on all suppliers of the stock
STOCK - contains details on all items of stock (goods).
CUSTOMER - contains details on all customers buying the goods.

Now the the thing is, apparently theres a many to many relationship(s) between MANUFACTURER and STOCK and the same with CUSTOMER and STOCK.

Is this because many customers can order many items of stock? And Many manufacturers can sell many items of stock?

Or am I going the wrong way around it?

 

[carboxylic]

Hmm it looks like I have confused Stock Control with Sales Processing?

Is that right?

 

[carboxylic]

Anyone?...........

 

[neileg]

You'd be better of starting a new thread instead of resurecting this one.

Hi all,

Ive been searching all over this forum for something that sounds like mine.

Im doing a stock control system in Access with VBA.

Right, Im having trouble with ERD's and tables.

The Entities I have, like the threadstarter are simmilar so:

MANUFACTURER - contains details on all suppliers of the stock
STOCK - contains details on all items of stock (goods).
CUSTOMER - contains details on all customers buying the goods.

Now the the thing is, apparently theres a many to many relationship(s) between MANUFACTURER and STOCK and the same with CUSTOMER and STOCK.

Is this because many customers can order many items of stock? And Many manufacturers can sell many items of stock?

Or am I going the wrong way around it?

Access only models the real world and relationships are real world attributes. So if any of the items of stock can be supplied by more than one manufacturer and any manufacturer can supply more than one item of stock, then this is a many to many relationship.

Clearly, it is highly likely that customers can buy more than one item and the item can be bought by more than one customer, so this is many to many, too.

 

[carboxylic]

Thanx Neil.

Btw, you see that Sample Datbase available from MS office, thats a stock control isnt it?

I can see how new stock is orderded through the "PurchaseOrder" form. And this effects the inventory transactions (which is the master file yes?).

But what happens if the customer orders something? How is that implemented into this system?

I can see "units sold" in the master file, but how does that get updated?

If I were to make more tables, very simmilar to the "Purchases" genre, would that be taking it too far? Because then it would become a sales order system, yes?

 

[carboxylic]

Thats what I mean btw....

 

[carboxylic]

A speedy response from anyone is greatly appreciated......

 

[neileg]

Thanx Neil.

Btw, you see that Sample Datbase available from MS office, thats a stock control isnt it?

I can see how new stock is orderded through the "PurchaseOrder" form. And this effects the inventory transactions (which is the master file yes?).

But what happens if the customer orders something? How is that implemented into this system?

I can see "units sold" in the master file, but how does that get updated?

If I were to make more tables, very simmilar to the "Purchases" genre, would that be taking it too far? Because then it would become a sales order system, yes?

It's your system, you can implement whatever you want. A comprehensive purchase/sales/inventroy system is quite ambitious for someone starting out in db programming, however.

 

[carboxylic]

Cheers for your advice. I have settled for a simpler task for my project lol.