Office Supply Inventory

[swhennen85]

Good afternoon everyone!



I'm new to the forums and somewhat new to Access. I took a college course several years ago on Access 2003 and I've forgotten a lot of that information. Right now, where I work, someone on my team designed a Consumable Inventory system in Excel. We are able to scan someone's badge, place an order in for supplies, and it logs the transaction and the cost associated to that person's ID number. Although, it's not too bad, I think that for what it should be doing, it needs to be made in Access. I also want to tweak it to run certain reports, and measure restocking levels.

I have the ideas on how I want it to work all written down, but I lack the knowledge on getting started with Access. So, I would like to ask for any advice or help on what to do.

• The inventory design I have in mind should open up a Form that looks like a Login Page. It should contain a single text box that if our team can enter or scan an Employee ID number (Oracle number it's called at our work) and it will automatically bring up a Supply Order Form that's linked to that Oracle Number. It should display "Employee First Name, Employee Last name" - "Oracle Number", "Department" in the Form Header. The login page should also have a button labeled, "Admin" where it unlocks the program and allows our team to edit any information, such as adding new employees, adding new inventory, updating Cost of Unit, etc.

• The Supply Order Form I had in mind should be like ordering online. It will contain a list of all the supplies we have in our office available to sign out, and have check boxes so the user can click on one or multiple items to "Add to Cart". Next to the check box, are column headings that should read in order: Item, Item #, Cost Per Unit, QTY In Stock, QTY Ordered, and Total Cost for each item selected. Eg. Item = Pencils, Item # 123456, Cost per Unit = $0.30, QTY in Stock = 50, QTY Ordered (Have a text box to manually enter the number they would like), and the Total Cost is auto updated based on Cost per Unit * QTY Ordered. At the very bottom of the form, it will have the grand total of their projected transaction.

• At the bottom, there should be two buttons: A Cancel Order button, where it will clear the transaction, not record it, go back to the Login Page. The second button will be to "Add to Cart." This will bring up a smaller, popup form that will be used for the User to Review the Transaction Summary before processing the order, just to verify the order is correct. It should have the option to bring up a warning prompt or proceed to a verification form:
  
  • The warning prompt is for Data Validation and should prevent the user from continuing with the order if the QTY Ordered will make the QTY in Stock less than 0. It should say something like "QTY Ordered exceeds QTY in Stock. Please change order amount,' and redirect the cursor to the item that is affected.
  • The verification form will ask, "Is the below information correct?" It should show a summary of all items that were checked on the Order Form, with the following information: Item, Item #, Cost Per Unit, QTY in Stock, QTY Ordered, Total Cost, and QTY after Order is complete. At the end of this, there should be two buttons again. One to Cancel and go back to the Order Form, and one to Proceed with the Transaction. Once proceeded, it should do several things:
  • 
    
    • It should briefly prompt a message saying the Transaction was successful, and if possible, return to the Login Screen.
    • It will update the QTY In Stock - QTY Ordered.
    • It will record the entire transaction done with the following information: Date/Time of Transaction, Employee First Name, Employee Last Name, Oracle Number, Item, Item #, QTY Ordered, and Total Cost.

• I want to be able to run and print several reports.
  
  • A Restock Item Report. This would only display items that fall below a set Restock level for a particular item. This report anyone could print so that our manager can look at it and order the supplies.
  • A Transaction History Report by Item.
  • A Transaction History Report by Employee.

Also, I'd like to be able to do this in Access 2003. We have two networks where I work, and the computer that will be running this only is allowed to use Access 2003, for reason's beyond me. That's really all I can think of at this time. I had tried creating tblEmployees, tblInventory, and tblTransactions to get started, but that's what I'm stuck with. I deeply apologize for such a lengthy post and not having enough knowledge in Access. I'm just looking for a way to make something more automated and make our jobs just a little easier.

Any help would be immensely appreciated.


Thank you!

-Shaun

[June7]

Get data structure designed before thinking about forms and reports. Define data entities and relationships. Set up tables.

Have you looked at the Microsoft Inventory database template? Don't know if there is one available for 2003 but could get ideas from 2010 example.

[swhennen85]

I tried taking a look at the Northwinds ones just to be a little familiar with them. I even did the Goods one for 2010 on my home PC just to take a look. That one seems very close to what I'd like to do, however I'd have to remove a lot of the fields. Anytime I try, I end up screwing up the db from functioning. So that's why I figured it'd probably be easier to just do it from scratch. I know, I fail at Access.

I did see the login screen I could use. It was a list box that you could select the users. I would want to change it a text box, and be able to scan a badge into it to Login. I wouldn't need any supplier or shipper information.

[June7]

Good Luck. When you encounter specific issue that need help with, post a thread.