Help needed on inventory database architecture in Access 2003

Unfortunately you are really asking "please can your write this for me", which indicates that you may really be in need of either: the assistance of a db consultant if you really want a home grown solution - and youre talking a chunk of change to develop this - you are asking for much more than a simple inventory database - definitely will cost more than 500 points :)

If you decide to build your own, it would be much harder to support long term, as you see omissions and changes needed.  Also, if you don't have it professinoally designed you will likely make some design errors, and it is much more costly to fix a db in current use which was poorly designed then to get it right at the start.

So instead of going down this path I would recommend you look at QuickBooks which is the industry standard product for small businesses trying to do what you do, and has an inventory management component as well as everything else you outlined.  

The cost of purchasing, learning and implementing with a QuickBooks consultant will be less in the long run then the DIY solution.  It also has an upgrade and development path that your application will not, and there are plenty of people in the workforce who know the product - so this would be much more sustainable for getting book-keepers and employees up and running faster than having to train them in a one of a kind Access db.  And you can be focused on getting a known product to work well with your business instead of learning how to become a db developer.

Northwinds Sample database is a starting point if you are determined to DIY it, but it is not a sustainable long term solution.  Northwinds was developed back in the mid 90s and probably hasn't changed since then.  What about email integration?  online bank account integration? etc.

If you want a hint of what you are getting into for Architecture: this is a 2 minute highly simplified version
Tables and relationships:
Customers
Suppliers
Products
Purchase Orders
Sales Orders
SO Product Join
PO Product Join
Discount types
Term types

but that's scratching the surface.  YOu need to put these in a relationship diagram, and then you may also see other tables and relationships you need.  Then as you point out you will need to build out input forms with appropriate lookups, procedures for running invoices, updating fields based on actions (eg paid).  You'll have to build out ar/ap tracking, get really good at building queries from multiple tables, and reports from queries.

Have I put you off yet?  try QuickBooks!


 or to look again at the off the shelf solutions.  I agree with the previous poster that this is a very tall order, especially if you have limited knowledge of database design.

However if you are