Calculations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have reviewed this board trying to see if I could find something that
pertains to what I am looking for, and cannot find it. Please forgive me if
has been posted before.
I am in charge of creating a database that will house all of our company
office supplies. We have a Reorder Level that will stay the same always. We
have an On hand Quantity, Reorder amount, and Received in amount.
If the on hand quantify falls below the Reoder level, I need the Reorder
Amount to calculate the amount to be reordered.
Then when the ordered product is received into the system, I want the
reorder amount to go caluclate the differences.
I know this is probably a very simple process, but I just can't get it to
work.

Please help
Tara
 
Simple? No.
We have a Reorder Level that will stay the same always.
Click to expand...
Chisled in stone like the Ten Commandments? Bonus tip: When designing
databases, assume everything will change especially those things that you are
told will never change.

You need three tables.
1. Office_Supplies table that includes a description of the item and it's
Reorder Amount.

2. Office_Supplies_Usage table that tracks how many have been used. Include
a foreign key to the Office_Supply table, a date field, and number used field.

3. Office_Supplies_Ordered table to track incoming shipments. Make sure that
there is a date field, a foreign key to the Office_Supply table, number on
order, and number received. You create this record when you order an item and
update it when the item comes in.

Notice that there is not an On hand Quantity field mentioned above. What you
do is sum up the number received in the Office_Supplies_Ordered; sum up the
number used int Office_Supplies_Usage; compare these to each other and that
will give you the number on hand. If this number doesn't match an inventory,
make another entry in the Office_Supplies_Usage table. Then you can compare
this value again the Office_Supplies table's Reorder Amount.

The reason for the Office_Supplies_Ordered table's number on order field is
to stop you from ordering the same supplies more than once. Say one Monday
you find that you are below the Reorder Amount and order the supplies needed.
That shipment doesn't come in for a couple of weeks. In the mean time you run
the query again and again order supplies that are already on order. If you
are not careful, you could soon have a year's worth of supplies on order!
While it may be better to have too much than too little, it could put a crimp
in both the storage space and quarterly budget.
 
Jerry,
Thanks for the help. I will try out your suggestions. Are there any
formulas that I am supposed to use to do the calculations. And when you
refer to a "foreign Key", is that the same as a Primary key. Do any of the
table need to have a primary?

Also as to the comment I made about the Reoder Level never changing. I get
your point, I guess what I was trying to say is that it will not be changed
due to any calculations. I will change it if I need to up or lower levels.
Thanks for the input. I look forward to hearing back from you.
Tara
 
Hi,

1. IMHO all tables need a primary key even if it's just an autonumber to
keep track records.

2. A foreign key is linked to the primary key in another table. For example
Office Supply number67 can be ordered more than one time. You would have 67
in the foriegn key field of the Office_Supplies_Ordered table.

If the concept of primary and foreign keys in a relational database doesn't
make sense to you, step away from the keyboard. ;-) You really need to
understand this concept before using Access. Maybe a college class, on-line
training, or a book like Database Design for Mere Mortals.

But do you want to know a little secret? Come closer to your monitor. Make
sure that no one is watching. Open Access. Go to New Database. Go to
Templates on my computer. Check out the Invertory Control database wizard
which will build one for you! It might not be exactly what you need but is a
good place to learn.
 
Jerry,
I have designed several databases for my company in the last year. I might
not be as versed in it as I like, but I learn something new everyday. That
is why I post my questions to this group if I have questions. I have found
several helpful things that have automated many tasks in our database, that
were done manually before.
I don't know if your tone is supposed to be sarcastic or helpful, but I am
not an ignorant person when it comes to access. If it peoples questions
annoy you, then don't answer them.
 
Sorry! Sorry! Sorry! I was trying to be humorous especially in the last
paragraph. But I was serious that the Inventory Control database wizard might
build you something very close to what you need.

I also was serious about getting some training and the excellent Database
Design For Mere Mortals book. If you think that there's more databases in
your future, I recommend that book.

Sorry if I offended.
 
I apologize too for taking it the wrong way. Thank you for your suggestion I
will look into the book. However, I found what I needed to do. It was a
simple subtration formula in a query. Go figure! Thanks for your help./
 
Back
Top