Dealing with multiple quantities of products in access.

[mccadam]

Hi all,
I am currently trying to build a database for the company I work for. I need the database to track our stock from suppliers and distribution records to our offices and private clients.
The difficulty is that each stock can have a different cost and said stock could either go to one of our offices, or to a private client at which would be sold for more then cost price. To begin with I only added the cost to a product when it was distributed. However when I need to do a stock check on 'what's left on the shelf', there are no cost attributed to the stock so I can't get a figure of how much money is sat on 'the shelf'.
My Database currently consists of 6 tables - Product/Stock/Distribution/Supplier/Private Client/Offices.

Any help would be hugely appreciated as the more I look at it the more confused I get...! If you need to me explain more about my database and/or explain my issue more clearly then please just ask me.
Thank you in advance
mccadam

[jzwp11]

Welcome to the forum!

To get a better understanding of your table structure, could you provide a list of fields in each of the 6 tables you mentioned? If it is easier, you could zip and post a copy of your database with any sensitive data removed.

[mccadam]

Please see screen shot below of table structure and relationships.

[jzwp11]

OK, first of all, it is recommended to not have spaces or special characters (the underscore _ is OK anything else like #,?,/,\etc. should not be used) in your table or field names. You also do not want to use reserved words as table or field names. Here is a link for reserved words in Access.

With respect to your structure, I would put all organizations/people in 1 table and then distinguish them as a branch, a client or a supplier

tblOrganizations
-pkOrgID primary key, autonumber
-txtPrimaryName (name of the company or last name of an individual or branch Name)
-txtSecondaryName
-txtAddress
-txtCity
etc.

I see you have batch and serial number in 2 tables. Are they are same actual values in the two tables or are they different?

Do you modify the products you buy or just sell/distribute them?

Also, I do not see a cost/price field in the stock table, should there be one?

Is each product unique or can someone buy multiples of each one?