I need help with my Access database - stock control & sales?

Question

I've built myself a database to control my very small business. On one of the tables, I have all my purchases. On another, I have all my sales.
When I go into sales, I select an item from my 'purchases' table via drop down that I've sold. HOWEVER, what I can't figure out is how to set things up so that once I've sold an item, how do I stop it appearing at a later date on my pick list on the sales sheet i.e. I've already sold it so I don't want to be able to 'sell' it again.

I guess that I've got to find some way that, once it's picked, a marker gets added to the purchased table that says 'no longer available', and the sales sheet disregards all items with such a marker.

But I can't work it out. Without going in an manually updating the Purchases table every time. There must be a smarter way.

Answers in relatively plain english please - I'm self taught (recipe for disaster!) and Access makes my head hurt!

Answer 1

You just add a logical field the one where it says YES/NO.

This will add a little checkbox and you can set the default to No.

When you run a Query and you want to omit all records that have been sold, under the Yes/No column in the Criteria row put the word No.

Answer 2

You need to run a query to count the quantities down on the purchased items echa time you sell a quantity. Ideally you could show this in the drop list. that way if you order more than one of something it can be sold to several customers and keep your stock list in order. Also a single integer 1 for obsolete 0 for available, allows you to turn off any item for view through the query in the drop list if it is no longer available. Access is not the best database from a scalability or multi-table use point of view.

Answer 3

I do not know the exact coding, but you have to do two things:
1. When you make a sale, code the sale bill so that the quantity sold is deducted from Inventory database.

2. For sale entry, make a conditional dorp down list, to show only those items which have quantity > 0.

Hope that helps.

Answer 4

well what you can do is, just number the items what you have got in your stock to sale for, suppose you have milk number it as 8 bottles, as soon as you sale it off, just fire a query which decreases the number to 7 and so on. Now if you are using a front end like VB or something, write a simple code,
if(num==0)
{

lable1.enable=false;
}

or something like, so that when the quantity is ZERO(0) it doesnt let you select it..

hope this helps, its just a concept, now you gotta put the logic. or send over the Access file, i will help you in that :)

Answer 5

What the name of the database program. if you used an old one you can make changes to the program. however try using Microsoft acces for your database you can add and change your program to fit your needs. I hope that this helps. I need more information about the database program to make a good strong answer.

Answer 6

Sounds like you're mucking with the tables directly, which is BAD, BAD, BAD! You should only interface with your tables with a FORM.

In the form, once you "sell", qty is deducted from "available" (your "inventory" table) and added to your temp table just for printing. (And presumably, into some sort of a log table in case someone needs to figure out what really happened) Before you "sell", you check qty available first. All that logic can be done from a form.