Purchasing - Receiving setup help needed

[M0E-lnx]

Hello forums.
Using access 2010 here.

Trying to setup a database that would allow me to create purchase orders, and then receive the items within that purchase order
I think I got the purchasing side of it setup properly, but i'm having trouble with the receiving side of things.

For the purchasing, I'm working with 2 tables.

tbl_PO_Records
* ID
PoDate
PoVendor
PoJob

tbl_PO_Transactions
* ID
TransPORef (related to ID from tbl_PO_Records)
TransQtyOrdered
TransItemNumber
TransItemDesc
TransItemCost

So now, what I need to do is to pull up a transaction list (from that table) when I select any one PO from tbl_PO_Records. Once I get the list, I need to be able to receive any number of items in it. I need to be able to have multiple receiving transactions per line, because sometimes vendors will invoce for partial deliveries, so the backorders are invoiced on a separate invoice.

Any help is appreciated, Feel free to ask any details I may have left off

 

[jdraw]

There are existing data models at http://www.databaseanswers.org that may help
eg http://www.databaseanswers.org/data_models/customers_and_orders/index.htm

or http://www.databaseanswers.org/data_models/purchase_orders/index.htm

 

[AccessBlaster]

Also as a side note you way want to name your primary keys fields something other than ID. PoRecordsID or TransactionsID keeps you code cleaner and easier for others to figure out, and could help in debugging.

 

[gemma-the-husky]

I assume you are getting into inventory/stock in general.

not much point dealing with inventory in, and not inventory out. Mightily difficult. Inventory is one of the hardest areas to deal with. Quantities can be managed in normal circumstances, but dealing with prices is very very complicated. There are very many special cases to consider.

 

[M0E-lnx]

Thanks for the replies so far guys.
I've checked out those data models and they really dont help me much.

My situation is not about inventory, but rather quantities ordered vs quantities invoiced.

Like I said, I got the purchasing side of things setup the way it should work... There is a parts catalog with numbers and pricing in general, but because of price variances, I do require orders to be acknowledged by all my vendors, so the orders get edited for pricing corrrections before the invoice arrives.

My problem lies in being able to create a form where I can type in a PO ID number and have a subform display everything I ordered on that PO ID and allow me to receive some or all of it. By "receiving" I mean I need to have some kind of a record that I've already been invoiced for this order, and all items are received in it, so I can close this order and expect no more invoices on it.

 

[jdraw]

Can you show us some tables and sample data that shows the issue involved?

I am also confused a bit by

so the orders get edited for pricing corrections before the invoice arrives.

Why are there pricing corrections required as part of the business process? Do you not know current prices at time of Order????

 

[M0E-lnx]

The pricing needs to be adjusted because some of the pricing (metals) change on a daily basis... so keeping the prices updated is a task of its own... but most of the stuff's prices are stable

Here is what I have so far, with the purchasing side simple enough, but fits my needs as it is.

http://vluser.dyndns.org/Database1.accdb

Maybe somebody can suggest how to implement an invoicing/receiving side to that.