Help! Very Simple Inventory Needed

[jflorey]

I'd like to create and inventory database with just one Transaction Entry Form. This will be for my organization's promo closet for keeping up with items and their stock level.
The Form should have the following:

Item (This will be a drop down of items from the Item table)
Employee (This will be a drop down of employees' first name from an employee table)
Transaction Type (This will be a drop down of Addition or Removal)
Quanitity (Number of items added or removed)
Date (The date items were added or removed)
Event (A blank field for entering where the itmes were taken to)
Comments (For additional info)

I need to be able to find stock level at any time for any items.

I'm completely lost when it comes to Access...
Please Help!

[June7]

Suggest you get an introductory book on Access and start learning. Need to learn relational database principles, Access functionality, programming concepts, macro and/or VBA language.

Download the Northwind sample database or the Inventory template.

Also review
http://office.microsoft.com/en-ca/te...001047741.aspx
http://allenbrowne.com/AppInventory.html

[jflorey]

We have a promotional Item closet at work and they want a database to keep up with what is going out and coming in:

Essentially I just need a Transaction Form where staff can enter when our promo Items are removed and added (brought back or stocked). This form should include:

• Date
• Staff (Which will be a drop down of names from the staff table)
• Event (a new entry every time)
• Item (Which will be a drop down of items from the item table)
• Transaction Type (Add or Remove Item)
• Quantity (how many were added or removed)
• Notes (long text entry option)

The only reports I need should be stock counts of how many of each Item are in stock, each Item by category are in stock and that should be it.

How many tables/queries do I need to accomplish this?

[Dal Jeanis]

Generally, you will need one table per "entity" you are tracking.

In this case, I see the following entities:

1) Staff members
2) Items
3) Categories
4) Transactions
5) Events

Transactions is probably two tables, one with a single record for each transaction, and one with a single record for each type of item moved in/out in that transaction.


Pop over to MVP Roger Carlson's access library and check out his tutorial on database design. It will save you dozens of hours in the long run. http://www.rogersaccesslibrary.com/

[jflorey]

Are you saying each item needs its own transaction table?

[June7]

No, Dal is not suggesting that. Transactions table would be like

TransactionID (primary key)
ItemID (foreign key)
StaffID (foreign key)
EventID (foreign key)
TransDate
TransType (In/Out)
Quantity

[Dal Jeanis]

What I meant is that, to a human, a "transaction" can mean "On Friday Joe brought back three keychains and six posters". To avoid complexity and redundancy, that's not a single table, though.

You'd have one transaction MASTER table where a single record (say transaction number 27) represents "Joe brought stuff back on Friday".

TranMasterID (primary key)
StaffID (foreign key)
EventID (foreign key)
TransDate
TransType (In/Out)

Then you'd have a second transaction DETAIL table where one record represents (for transaction 27, add 3 keychains to inventory) and a different record represents (for transaction 27, add 6 posters to inventory).

TranDetailID (primary key)
TranMasterID (foreign key)
ItemID (foreign key)
TransType (In/Out)
Quantity

Or, if you prefer, you can have everything be a detail record, as June laid it out, and store the redundant info on each detail record.

Redundancy isn't a horrible thing. You'll notice that I decided above to have in/out be redundant. The field could be deleted from the master, but I'd think it carries information that I'd like to have there. Having a third possible value - "Mixed" - might be useful for when Gertrude comes by, drops off something and picks up something to go back to the same event. That third value wouldn't be useful at the detail level.

[June7]

Dal's table structure is more normalized, although would not expect TransType in both tables. It is a balancing act between normalization and ease of data entry/edit.