Records

[Griphon]

Hello ,



I would like to create a stores receiving and dispatching database. I would like to know how would one go about setting up a table structure to allow the following:

I would ideally have 4 tables. 1.Stock Items 2.PO Numbers 3.Recieving 4.Dispatch

What I would like to accomplish is to receive/dispatch stock on existing stock codes attached to a PO number.

Therefore I would like to try and create something where I can enter a PO number and list multiple stock items to that number. When displaying the record of the PO number thus would return the items attached.

Any advice would be appreciated.

Thanks in advance.

[June7]

Usual approach is to have a table related to PO_Numbers that has records of the items associated with that purchase: PurchaseDetails

Not sure what Receiving and Dispatch are for.

[Griphon]

Good Day, Thanks for the response,

1. I receive a list of PO_Numbers that are generated from an online database and imported into a table.
2. When receiving the stock the PO_Number is used and stock items from an available list, and the opposite when dispatching changing the stock levels of the stock items.

Thanks in advance.

[June7]

You could have one table for the in/out transactions with another field for the transaction type. In either case, can use a form/subform arrangement for entering the transactions. Main form bound to PO_Numbers and subform(s) bound to transaction table(s). Find existing PO on the main form or enter new PO then enter the related transaction records in subform(s).

[Griphon]

Hello June7,

Please see attached at what I am trying to achieve.

Thanks.

[June7]

Form and subform have same data source. That's not how should be. Reread my previous post and review http://office.microsoft.com/en-us/ac...010098674.aspx

Also, if the combobox is intended to be just input for filter criteria, it must be UNBOUND.

[Griphon]

Hello June7,

I have made attempts to access the list of poa numbers from the drop down and select the product from the Sub, but the data seems to be saving incorrectly.

I did read through the link supplied I just cannot seem to come right.

Thanks

[June7]

If you want to provide latest version, will analyse.

[Griphon]

Database1.accdb

Hello See attached, thanks for the response

[June7]

The unbound combobox to select PO will accomplish nothing.

I don't see that you have attempted what I suggested about the form/subform design. Both forms still have the same data source.

Linking the forms on the same field from the same table will accomplish nothing.

Your data structure is still not correct.

Appears you need a better understanding of relational database principles and the use of form/subform.

Consider a conventional business db model: A purchase order is a transaction to buy product. A sales order is a transaction to sell product. Those are three entities for the db. Customers and Suppliers are two more entities. Customers are related to Sales and Suppliers are related Purchases. Products are related to Purchases, Sales, Suppliers.

tblSuppliers
SupplierID
SupplierName
Address

tblCustomers
CustomerID
CustomerName
Address

tblProducts
ProductID
ProductName
SupplierID

tblPurchases
PONum
PODate
SupplierID

tblPurchaseDetails
PONum
ProductID
Quantity

tblSales
SalesNo
SalesDate
CustomerID

tblSalesDetails
SalesNo
ProductID
Quantity

In your case, you receive a list of items associated with a purchase order and dispatch items by some other instrument. Do you care to document who from/to? A very simple model:

tblStock
StockID
StockNum
StockDesc

tblMovement
MoveID
MoveType (Rec, Dis)
DocID (PONum, DispNum)
MoveDate
StockID
Quantity

If there can be multiple stock items for each instrument, split tblMovement in two and have a Details table. Use form/subform arrangement.

[Griphon]

Hello June7,

I shall give this a try and give you some feedback, I really do thank you for all your assistance.

Griphon

[Griphon]

Hello,

I have attached an update, I would just like to know once I have filtered my Item, how do I retain the DocNo and add the selected item to the record?

ThanksDatabase2.zip

[orange]

Griphon,

Start with an overview of WHAT your business is. Identify the Entities and attributes of each. Using you business description determine the relationships among your tables. Create a data model of what you are trying to build.

Work through this tutorial to get an appreciation of the steps involved in database design.

Good luck with your project.

[June7]

What DocNo are you referring to? MoveID should be the autonumber primary key.

A record in tblMovement should have either supplier or customer, not both. For proper normalization, there would be only one field for person/company ID in tblMovement.

Using tabs for Receiving and Dispatch doesn't really help. Still need to pick the MoveType from a combobox.

[Griphon]

What DocNo are you referring to? MoveID should be the autonumber primary key.

A record in tblMovement should have either supplier or customer, not both. For proper normalization, there would be only one field for person/company ID in tblMovement.

Using tabs for Receiving and Dispatch doesn't really help. Still need to pick the MoveType from a combobox.

Hello June7,

The DocNo is a list of Purchase order numbers that I receive from a third party app, I would use supplier on incoming stock and customer on outgoing stock. The reason for the tabs is that I can have a text box with a default value on each of the pages to auto insert the move type.

Thanks in advance again