Database dummy - Design help?

[nicole.skeeters]

Hi yall! I'm trying to build a database at work for inventory purposes. I've been working on it for a few weeks now and have gained little ground, so I'm reaching out. My knowledge of access is relatively basic - I understand tables and relationships and queries and forms and creating reports. I'm just stumped on why I cant get the data to write to where I want it to be.

These are my goals:
*To keep a current, live look at inventory by entering in new supply requests and subsequently entering in received stock
*To be able to show, over time, how much each division/department/supervisor is requesting in a specified time frame (ie quarterly)

To achieve this, I realize that I need the following:
*A form that enters supply requests - a single transaction id for a single supervisor at a time that records multiple supplies coming out of inventory and updates the current stock accordingly. This form would write the transaction to my "tbl_Transactions" while also updating the field "current stock" in "tbl_inventory."
*A form that enters stock received to update current stock data
*A report to show amounts ordered ($) by each division/department
*A report to show inventory reorder requirements

These are my basics:
tbl_Supervisors:
pk-Supervisor ID
Division
Last name
First name
Email
Job Title
Phone
Department
tbl_Transactions:
pk-Transaction ID
fk-Item ID
fk-Supervisor ID
tbl_Inventory:
pk-ID
Item
Description
Item Cost
Category
Current Stock
Discontinued
Comments

I have a one-to-many relationship between [Supervisors]![Supervisor ID] and [transactions]![transaction ID], and [Inventory]![ID] and [transactions]![Item ID].

Just fyi, I had originally tried making MSOffice's Inventory template work for me, but it just doesnt. Can anyone help me, suggest a different template closer to what I need, or suggest readings? I appreciate your help in advance, and apologize for the very long post.

Thx!
Nicole

 

[plog]

Not too bad, here's what I see wrong:

tbl_Transactions,
1. You need a date field (i.e. TransactionDate)

2. You need a quantity field (i.e. TransactionQuantity)

3. You need a field to denote wether inventory is coming or going (TransactionType). This might not be necessary if you only care about whether its coming or going and not about specific actions (manager returns stock vs. buying new inventory, or manager taking out stock vs. removing because stock is damaged.)


tbl_Inventory

1. If Current Stock holds the quantity on hand, remove it. This should be calculated, not stored. You would run a query on tbl_Transactions to determine this.

2. Item Cost might need to go into a new table if costs can change and you need to know how much something cost in April of last year vs. May of this year.

 

[nicole.skeeters]

Alrighty, yes, I should've added in a date and quantity field in tbl_transactions - thanks for pointing that out. And you're right, in order to calculate changes I would have to choose if the item is coming or going.

Can you elaborate on how to do the calculation for current stock if it shouldnt be stored? More so, if I have no transactions (which I dont, since I'm the first to think to build this), I would think the qry would just show no data instead of what we have on hand currently. So how do I show what I have in stock now without having any transactions?

Since, yes, I'm sure pricing can change at the manufacturer's whim, I will move it to it's own table, thanks again. I'm sure I'll have more questions as I continue on, but this gives me a few things to work on now!

Thx
Nicole

 

[nicole.skeeters]

Alrighty, yes, I should've added in a date and quantity field in tbl_transactions - thanks for pointing that out. And you're right, in order to calculate changes I would have to choose if the item is coming or going.

Can you elaborate on how to do the calculation for current stock if it shouldnt be stored? More so, if I have no transactions (which I dont, since I'm the first to think to build this), I would think the qry would just show no data instead of what we have on hand currently. So how do I show what I have in stock now without having any transactions?

Since, yes, I'm sure pricing can change at the manufacturer's whim, I will move it to it's own table, thanks again. I'm sure I'll have more questions as I continue on, but this gives me a few things to work on now!

Thx
Nicole

 

[plog]

So how do I show what I have in stock now without having any transactions?

You put transactions in there for them. This is what I meant by a TransactionType field. You would have a transaction for each item that says something like "Initial Stock" and that would let you initialize how much you had for each item.

I would actually create a new table for TransactionTypes that would look like this:

TransactionTypes
TransTypeID, TransTypeDesc, TransTypeValue
1, "Initial Stock", 1
2, "Purchased", 1
3, "Allocated", -1
4, "Returned To Inventory", 1
5, "Damaged", -1

TransTypeId would become a foreign key in tbl_Transactions and allow you to know exactly what happened. The TransTypeValue field lets you know if its a credit (1) or debit (-1) to inventory. You would multiple the quantity field in tbl_Transactions by TransTypeValue to get the total number of items to add or subtract.

 

[nicole.skeeters]

Well this is going to be an awkward question because I'm sure I'm supposed to know this...but, where do you put the 1/-1?

 

[plog]

That was a table description. The top was the name of the table (TransactionTypes), the next row listed all the fields of that table--TransTypeValue is the one that holds the 1/-1 and below that were example records.

So you put the 1/-1 in the TransactionTypes table in the TransTypeValue field.

 

[nicole.skeeters]

I feel really silly for asking that now, because you went into much more detail at the bottom of that post, I guess I just overlooked it. Sorry! Thanks for answering.

 

[eirman]

There is a freely available inventory db available for download on this site.
I can't give the link as my post count is <10
Go to page 2 of "Sample Databases" and look for "Inventory Control".

Have a look at it ... it may give you a few ideas. If you hunt through the rest of the sample databases you may find something simpler to start with.

 

[nicole.skeeters]

Eirman, thanks - I'll check it out.

plog: Alright man, I've gotten to the point where I'm trying to do your suggest query for quantity purposes. I'm not quite sure that I've done it correctly, however. I have added into tbl_transactions all of my inventory, using the transactiontypeID for "initial stock" as you'd indicated (1). I added in the quantity, then went to build a qry on the tbl_transactions. For current stock, I did "Current Stock: [transactiontypes].[transaction type value]*[transactions].[quantity]."

I did a test entry to test the calculation...but it calculated that I have -2 in stock because 3 (initial stock) * -1 = -2. So its almost like it needs to be, "if its a type 3 (allocation, -1) then + quantity instead of *"

Have I confused you yet? I may be making mountains out of molehills here by just misunderstanding what you were saying. I can zip up what I've got so far and shoot it to you if you want to see.

Thx
Nicole