Hello Folks,
I can see reasons for two tables; one for Purchase Order Details records and the other for Sales Details records. In this condition a Union Query would create current inventory records.
A case could be made for having only one “Inventory” table that is made up from both PO details and Sales Details.
Which would you do?
I will leave my opinions for later.

I decided to have seperate tables, and use a Union Query to find the current inventory.

Actually probably not, please read further.

I would have a single table with all transaction types - you may also need other movement types such as

stock adjustments
stock balances
stock transfers, maybe

etc etc

as well as movements inwards and outwards.

its far far easier to manage with all these in a single table. If you need another stock movement type, its trivial if you only have one table, but it definitely isn't if you have a separate table for each movement type.

In addition, a union query is non updateable, so this in itself is likely to give you problems.

Thanks Bob, I knew there were good reasons to have only one table.

So in this case, the parent table (Sales, Purchase Orders, Transfers, Inventory Adjustment, Etc.) should have all the fields necessary to define any transaction type and the various forms should contain only the bound controls pertaining to that type of transaction.

the MS sample inventory database is pretty good, and well worth investigating.

I have used it, and it has a single table for all movements - however, i changed it, to change the sign of the movements - as sales and purchases were both recorded as positive values in the provided solution.

i prefer purchases to be positive, and sales negative, so you can just sum movements over a period.

Hey Dave,
Yeah I took a look.

I like the way they created different fields to hold in and out data. and their forms are neat (tidy). Overall they don't don't have much normalization in the tables.

Thanks for the heads up. It will add their distinctiveness to our.

my 2p worth - I come across this quite a lot especially in report writing.

A different view/design of a table via a report can vastly improve the users understanding of that information. They think they are seenig separate information but its just a filter on a bigger table.

Eg. Lets say you have a table of all sales a report of that would probably be of interest to the manager but a bit dull for an individual salesman. If you can make up a report that shows his sales and only his sales he's much more likely to be able to monitor his own performance.

Similar with stock - sales and purchases are all stock just one in one the other out. All together looks a bit of a mess. But with two separate forms and suddenly you have Sales vs Purchases. Or with a total you have inventory.

I've made a few CRMs and the notes tables often has a lot of what I call "marker" fields
From
To
Date
Date Updated
Author
Category
Subject
Project
Product

Things like project or product can be great as you can make them into drop downs and filter out products you are particularly interested in.

Differing ways of ordering often make a big difference as well.

I sometimes wonder whether the guys inventing databases completely comprehended how they would be used when they first created them. I suspect they just thought it would be useful to store information and were a bit sketchy on the exact advantages.

Which is probably good - there might still be easier ways of doing things out there that we haven't thought about. Although I don't think the fundamentals of basic accounting are likely to be the area for improvement.

Considering they've been around for the best part of 7 centuries.

Hey Dave,
Yeah I took a look.

I like the way they created different fields to hold in and out data. and their forms are neat (tidy). Overall they don't don't have much normalization in the tables.

Thanks for the heads up. It will add their distinctiveness to our.

what do you mean - they don't have much normalization? They seemed OK to me - as I said I changed the app to store movements out as negative, rather than positive - so I could just sum movements without a union query.

Where did you think there were normalization issues?

what do you mean - they don't have much normalization? They seemed OK to me - as I said I changed the app to store movements out as negative, rather than positive - so I could just sum movements without a union query.

Where did you think there were normalization issues?

Yep overly splitting things is as much of a normalization issue as not splitting them enough.!

Yep overly splitting things is as much of a normalization issue as not splitting them enough.!

sorry - i really odn't understand this.

if a table is not normalised, then it needs to be split until it is normalised.



the MS inventory example is actually quite light, doesn't have many tables, and is pretty good in my opinion. As I say, when I used it. I changed it in some ways, so all movements were stored with the appropriate sign - but thats all.

sorry - i really odn't understand this.

if a table is not normalised, then it needs to be split until it is normalised.



the MS inventory example is actually quite light, doesn't have many tables, and is pretty good in my opinion. As I say, when I used it. I changed it in some ways, so all movements were stored with the appropriate sign - but thats all.

Dave I was just referring to the habit of people in particular when dealing with stock of wanting to record movements in separate tables marked purchases and sales rather than one table. In such a situation splitting the table isn't normalization and actually leads to difficulties further down the line.