Inventory Database - Replacing one record with another

[kathh]

HI All, I'm new here. I'm trying to create a database for consignment inventory. I need to track the current inventory then also record when sold so have fields such as customer purchase order number, PO to supplier, supplier invoice number etc. Fairly standard. What I want to do is to record the replacement inventory stock number so that I can see the one purchase and the one replaced.

[orange]

Is this your first database?
Can you show us your tables and relationships (relationships window as a jpg)?
Can you post a description of the things involved in "consignment inventory" and how these things interrelate? (4-5 lines of simple English)

[kathh]

No, but I haven't used access for a while, so getting re-aquainted.

[orange]

I think I was editing my post when you responded.

Before proceeding with Access development, you may do yourself considerable benefit by reviewing some database concepts.

Here is the starting video from Dr Daniel Soper on Database. There are 8 videos in the series. I recommend you watch 1,2 and 4 as a base for your development.
Also, working through this tutorial would be helpful to you, after the videos.

Good luck.

[kathh]

Please see attached. I haven't really finished the tables but didn't want to go too far until I know how to do the replacement bit.

[orange]

My first reaction is that you do not have all your tables defined. However, it would be helpful if you would provide a description of your "consignment inventory" business --say a day in the life of your business - to help with context.

The recommendation is to get all of the "things" involved defined/described and build a model based on the rules of your business. Create some test data and work some scenarios against your evolving model. Reconcile every issue and adjust as necessary. When your test scenarios are all supported by your model, you now have a blueprint for your database.
You can do the model and testing with pencil and paper...

I see things like Item, Customer, CustomerPurchaseOrder, PO to supplier, supplier invoice number, location in your first post.
From the Access perspective you'll find that not having spaces in field names will save you many syntax issues.

There are several free data models at databaseanswers.org here is one that may help put things into context.
and another.

These models are generic and represent things involved in most situations. But your set up may be different, so these are meant as a starting point that you may adjust (add/remove pieces) as appropriate.

Good luck with your project.