Practical Sales Database for Cigar Shop?

[AccessJunkie33]

So I'm creating a database for a friend of mine who has just re-opened his cigar shop.

Here are the tables:

Sale
Purchase
Inventory
Vendor
Customer

Association Tables:
Sale Detail
Purchase Detail

As I was messing with this on Access, I wanted to have the db automatically update inventory levels based on purchase and sale events without running an update query at the end of the day. I couldn't make it happen.

My friend isn't very good with technology so I'm attempting to make this as simple as possible for him to functionally use. The forms I've created for him are simple enough but I'm worried about the update function because he might mistakenly run once, twice, maybe many times without knowing.

Is there away to automatically update resource tables based on new entries in event tables?

I'd be grateful if some gurus chimed in.

Thank you!

P.S. After reading some of your feedback, I'd like to know why the inventory table shouldn't be updated automatically based on the sale, purchase, return, and shrinkage events and why a select query is better?

 

[MarkK]

Don't unpdate tables with this data. How many you have is how many you bought minus how many you sold.

Have = Bought - Sold

Don't store this number anywhere. Calculate it when you need it.

 

[Lightwave]

Just to expand on Lagbolts comment

How it would work is as follows

On purchase a stock record is created - on sale the position field flag is changed to Sale. If there is a return the position field is changed to Return one field with the range of values that you could possibly have - Sale, Return, Faulty for an individual stock item etc...

To get the latest position of your stock its anything that is either returned or not faulty or Sale.

A simple select query.

 

[AccessJunkie33]

The issue I see is that when my friend checks his inventory through the inventory form, that number will be different from the query.

Also, if the inventory count is just Have = Bought - Sold, does that mean we don't need a qty attribute in the inventory table and should keep the qty in the purchase table??

Sorry, this is a foreign concept to me. I thought databases should automatically update the inventory table when an event that changes its stock occurs.

Thank you for replying btw!

 

[DCrake]

There is always a big issue with stock totals. Ok you start off buying say 10 boxes of cigars

5 are on the shelf
1 is in the display cabinet
4 is in the back room

You sell 4 so you get the remaining 4 from the back room, so do you create an internal stock transfer or do you ignore the fact. Also wha happens if somone steals a box do you account for it.

 

[AccessJunkie33]

I would just ignore the fact. I just want to know why the inventory table shouldn't/can't be automatically updated instead of using a query.

 

[DCrake]

It's a bit like one hand clapping.... unless the brain tells both hands to work together then one hand does not know what the other is doing.

Nobody is saying catagorically that you should not store calculations in tables, in some circumstances there is a need to do so. However in this instance the norm is not to do so.

Think of you POS

You pick an item from the inventory
You select a quantity
You add it to your basket
You checkout
Until you finally check out an item can be removed from the basket, so the only real time to do it is a checkout.

Then you have to trawl though the items in the basket and run an update query on each item to amend the quantity in stock figure to reset the quantity and also add the items to the inventory table.

By simply relying on the inventory table to calculate the items sold you deduct this from the current stock level in the inventory table to give you the number of items in stock.

I you get a return this is just a contra entry in the transaction table which increases the stock balance.

 

[AccessJunkie33]

Ah! Sounds like periodic accounting to me. : ]

Does that mean I should periodically update the inventory levels when there's a purchase event and let the select query deal with showing current inventory levels after a couple sales?

 

[Rabbie]

I would just ignore the fact. I just want to know why the inventory table shouldn't/can't be automatically updated instead of using a query.

This link will give you an insight into when calculated fields can be used. Remember that you need to know the rules to know when to break or bend them

 

[AccessJunkie33]

YOU GUYS ARE SO FRIGGIN HELPFUL. Thanks again!