Access 2007: Inventory Database
Hey all, long-time-lurker-first-time-poster.
I'm setting up a database for work, which is not quite an antique store, but for our purposes, let's assume that it is an antique store. We often have items that sit for a long time and cost several thousand dollars. We have a very, very slow moving inventory. The way we have been managing it is on paper, but now we want to move to a computer solution. We have about 500 items and we'd like to be able to rapidly look through pictures of each item and maybe click on one to bring up a page that has more info on each item. We need to be able to view the database from several computers at once, and ideally make changes. Right now we have everything set up as a SharePoint list on our Windows Server 2003 machine.
I'd started by making a database and adding photos as attachments which works ok in theory, but it is very very slow. I then tried to reference each picture as a link, using VBA to swap the pictures using this method: http://support.microsoft.com/kb/285820
But the problem with that is we want to be able to look at many items at once, like in a continuous report, but it loads the same image for every picture frame! It isn't until i click on each record that the photo updates.
Does anyone have any general advice for me on the best way to set it up, what tables I might use or any other set up advice? I have the data basically inputted, I can bash it into any format you think is appropriate, so just let me know!
Well, what is to follow will scream "NEWBIE"! But I guess one has to start somewhere, right? :-P
So here goes...
I have never used Access, and I know only the bare basics, if that much...but now I'm faced with having to create an Inventory Control Database from scratch (and here I'm holding thumbs that someone out there has one I can study and adapt...! :-P)
I work for a Concrete Product manufacturer, and for now I have to create a database where I enter daily production figures, and daily sales figures, and have to end up with a suitable stock report that I have to be able to consult when clients phone in wanting to know how much stock of a particular item we have, and of course, to pass on to our accountant at the end of every month so she can adjust her books accordingly...
Pretty basic stuff, I guess, but I don't know where to start...Can someone give me a few pointers or an example or two of what to do?
By the way, in case it's of any importance, we're talking about a list of about 200 products here, not all of which are necessarily manufactured or sold everyday. So yeah, not a huge project...I hope...! :-)
I am not sure how complicated of a job this is, but we want to be able to import our daily recievals of inventory into our existing inventory database instead of entering it by hand. I try using the wizard, however when I get to "finish" it says there was an error and it was not imported.
I don't know much about access but I was hoping one of you could point me in the right direction.
Getting the following error when I attempt to open a form which includes a sob form:
error: This expression is typed incorrectly, or it is too complex to be evaluated
I created this form in Access 2007 and it automatically created a subform which I need. When I delete the subform...I no longer get the message. I do need the subform.
I did not create this DB, I'm only updating it. It's currently being used by the end user in Access 2003. However it could have easily been created in Access 2000 or 97.
Could someone please lend a hand...I'm stuck.
Thanks for your time in advance,
This is my first time posting, I've tried reading past forums but honestly don't yet know my way around here.
I am building a database which hopefully will be a building block for one to use in my company, but right now it's kind of my tinkering toy. In it I want to have all of my employees information listed for various things, this is the easy part. I want it to have all of my purchase (inventory) stuff in it as well. Here's where I'm getting tripped up. If I issue out 2 XL shirts to an employee I want it to automatically calculate the on hand, and flag at a certain point for reorder.
Would this be best accomplished with a table data cell for qty purchased in each purchase (ie 20 shirts on one order, 40 in another) and querying how many were issued out? Is there a better way to do this?
Hopefully I'm not numbing any brains with stuff that's too easy, I just can't find the answer anywhere. I've looked at Northwind, but I don't know code yet so I don't know what I'm looking for.
Thanks in advance,
I have some basic knowledge of Access and how it works but when it comes to the SQL formulas and some database structures I have not had enough experience. If someone could please guide me in the right direction with this I would appreciate it.
I have need to setup a database of all equipment in a small room so that I can keep inventory on it. Right now I have a database with three tables in it. An Items table that relates barcodes of an item to the actual item name. A Tech table that contains all the techs that would take from or replace something in the inventory of this room. And a Transactions table where it holds what item, who, and if it was taken or stocked.
What I need the most help with setting up right now is how do I use the transactions table to total up all uses and stocks of each unique item so that I can get an idea of what is actually supposed to be in the room at the moment.
Thanks in advance.
I have build a inventory Database, it's include itme name , purchase date, price etc....
Now, I want to search button(s)...
example 1) the button which help me the find out all purchase in the time flame
example 2) , I can filter the supplier and date (base on example 1) to find out the record I m looking for
Is anyone can give me some information where I can find out the solution, thx
Plz help me understand the need for the Inventory transaction table. Can the info thats stored in there not just be pulled from purchase order and sales tables? Is it just a speed issue or just handy having it all in one table. Tis late and my brain has started complaining.
Thx for any insights
I am working on normalizing some tables from my Cancer Services DB, and I have been looking for a good example of something similiar to what I need but my set up is different.
I just care about the inventory control in this thread. Heres the break down.
Its a non for profit organization helping cancer patients. They provide non perishing items for loan (a special bed, a lift chair, walkers, canes, items that will normally be returned) at no cost. The item (a cane or walker or special bed) can be donated to them or they may purchase less expensive items from a supplier.
They provide perishing items (Items such as supplimental drinks, ostemy bags, shampoo etc). Most of these are bought from suppliers.
They provide services as well (which they currently have in with items) such as paying for a taxi, gas vouchers to cover gas they use to drive to and from a doctor, phone calls for clients who dont have a phone) This money would come out of a general account we will label CAP. The money comes from monetary donations, grants, etc. For this question though, assume ALL come from one account CAP.
The goal is to track quantities of items, who has what, who has been given what, when the services were provided or when the item was checked out and came back if its non perishable.
I imagine my tables looking like this;
tblTransaction (TransactionID, LineNumber, ClientID, TransactionTypeID, TransactionDate, Quantity)
tblInventoryType(InventoryID, InventoryType,disposible(yes/no)) -note- The inventory type would be like classes of items. Essentially if its like an item, equipment, comfort item, etc.
tblMasterInventory(InventoryID, ItemName, ItemDescription, SupplierID)
tblSupplierID(SupplierID, Quantity, Price, Date)
tblServices(ServiceID, ServiceName, ServiceDescription)
I get mixed up between how to distingusih between a service and item on the transaction table. As in, how do I seperate okay this is a service so its phone call, or a gas card/voucher. Essentially money to cover the actual service provided by someone else. For the phone call, its not really money, just that the provided it. Essentially they want to track the number of services provided as one of their goals, so they just want to show they provided a phone call.
And how to distinguish that its an item that was donated or bought, if bought how much. Who donated it I dont think is to important at this time. Although they do want to track donations, I am not sure they want it as part of this DB. They essentially will just care about what was donated, when, from whom, in memory of whom kind of thing to print off thank you letters.
But thats sorta off topic unless you think that should be a new donors table added here.
Keep in mind, they dont sell any of this, its all given out free of charge.
I know my table break down is messed up, but Its because i keep changing it back and forth having issues with what goes where and how it links up. Thanks in advance.
hello, i am looking for a music inventory database, anybody any idea where i might get one
I need to create a small inventory database tracking product in stock and "x" amount of raw material to create it.
Is there a way, using MS Access that I can track inventory of the product as well as update how much material was used to create the product?
I am new to access but I know how to create a database and enter the fields I want, eg: product id, model number, product color, in stock, re order, lead time.
My problem is associating another table to deduct/or add the amount of material based on totals.
In a nutshell, I want to track product inventory as well as track raw material in stock to create more.
Thank you for any help!
Im creating a db to keep records of computers that have been assigned to persons in their various departments and also unassigned computers and spare parts.
For the machine specifications table should i put in the following fields as columns
Hard Drive 1
Hard Drive 2
Hard Drive 3
Optical Drive 1
or should i just create a field called Component and another field called type/size e.g. component would be hard drive or opticqal drive and Type/Size would be 60GB or DVD+RW
or should i just create individual tables to store the Hard drive info separate from the optical disk and separate from the memory.
The database is going to get really big which is why i wana structure it properly you r help would be really appreciated.
I am trying to develop an inventory control database and would really appreciate some advice on the way I propose to develop the application
Inventory Control Database.
Outline for inventory control database.
The database will have the following tables:
On hand table
The Supply Table - will store the names of the stock items available
The persons table - will basically hold information on the person receiving the product/supplies
The Supplied Table - will hold information on who is being supplied, what is being supplied, who is the supplier, how much is supplied and when.
The On Hand table - will initially store the opening stock for all the stock items available. When an individual is given some of that stock, the amount of stock given will be subtracted from the on hand total, for that particular stock item.
The Orders Table – Will be the place where we will record all orders received. Once an order is received, this figure will be added to the On Hand Total for that particular item. So ultimately the on hand table should reflect initially the opening stock figure, then the opening stock figure plus any amounts ordered. I anticipate that after the initial opening stock figure is entered onto the system, the on hand table will be automatically populated by the orders table, and the site user will only need to manually update the On hand table in case of emergency, for example if a mistake is made on the order table.
I would really appreciate some help with the table design logic here. This is the first time that I have developed a stock ordering system, and would appreciate any advice on the over all design.
Initially, I want to build this as an access application then convert it to a web application ASP/SQL Server
I'm wrestling with a problem with a hardware and software database, on the software side. I'm trying to figure out where to store the license and media cost for a piece of software. Right now I have the following tables:
1. Software name (MS Word, Office, for example)
2. Software version, which also stores whether a license is required
3. Software license, which includes the license code and whether that license expires
4. Software inventory table, which stores the # of licenses we have available for installation. The purchase order (PO) # and date are currently here.
5. Software PO generating table. This is used when we need to reorder more licenses, or a new piece of software.
I need to figure out where to store the cost of the software, the cost of the license, how many people are covered by the license. I've been trying to decide which of the last three tables should store this information.
There are two issues: we need to keep a history of how much older versions of the software and its licenses cost, as well as be able to enter information to order new versions of the software and/or licenses.
Any input would be appreciated!
I work for a company that is so dated and needs a database to track inventory. Basically I record everything on paper and it is a pain in the rear end. I am not new to access but I am struggling with this database. I need to track my purchases (ie. I buy 5 units of widgets and they have 50 items per unit so that is 250 items total). I am also need to track which departments purchase items from me (ie. 1 unit of widgets = 50.00 so 1 item from the unit = 1.00). Any help or ideas would be extremely welcomed.
I have looked at the microsoft template for this off their website but it only tracks units and I need to have items on it ... is it better to add them or start a new database. Anyhelp would be appreciated.
Hey there, droogs.
It's been a few months since I posted last, asking for help on setting up a database that would handle inventory for the lab where I'm working. I'm proud to say that your advice has really paid off: the DB is doing a bang-up job of handling current inventory. There are just a few things my employers want it to do that it doesn't right now:
1. It needs to track usages on a monthly bases. There is a "Used" transaction type for when something is removed from the inventory for usage, and it needs to add all those for a given month. The idea is that they'd get a general overview of how much they're using over a certain duration, or if they're wondering, "how much of an item would we need to order to have, say, a six month supply?"
2. I don't know if this is possible, but they'd like to be alerted when it's time to reorder. Their idea is that when the inventory for a certain item reaches a critical point (say, a one-month supply or less), they'd somehow be notified or flagged to indicate that a reorder is necessary.
Please help me out, guys. You were awesome last time, and you may save my reputation yet again.
P.S., if this is in the wrong forum, feel free to have one of the moderators move it.
Ok, this is a weird one. I have a large MS Access application that has been around since Access 1.0. Of course, its gone through many, many, many revisions since its beginning. Now, I open the mdb in Access 2007 and behind many of the reports, are calls in the On Current and On Unload events for macros that have not resided in our database for about 10 years. We replaced these in Access 2.0 or 97 I think and through versions 2003, they are not there and everything runs smoothly. But low and behold if I open the database in Access 2007, there they are! And, they are causing errors because of course, the macro no longer exists.
For example, we used to have a macro named xyz and we called it from the OnCurrent event. In Access 97 we removed it by simplying highlighting and pressing delete from the property sheet.
So, I am wondering how the heck it has dredged up calls to this ancient macro after all these years?? Where have they been hiding??
I have also tried converting the database to accdb format, same result, it pulls in the macro calls. The database has been decompiled and compacted throughout its life including from within Access 2007.
I would LOVE to find out the answer to this one...
I have a number of production databases that use VBA to disable the user interface (menus, toolbars etc) when the database starts up. VBA also disables the shift-bypass key so they can't get in that way either. When I move these to Access 2007, what else must I disable via VBA code or whatever so that the user can't get to the database container and defeat my code to keep them strictly in the user interface?
I just want to compact and repair the same database that I open using vba. Thanks.
I am developing a database for a friend and have all the relationships,
tables, forms, queries and reports working well. I used the orders template to
build this and just made a few changes. I have an orders form with an orders details
subform that works beautifully. There is also customers table, employee table,
products and inventory table. The inventory table has product ID, units in stock and reorder level
as the only fields. I need an easy way to have the orders detail form decrease inventory as
items are ordered and also some sort of way to enter restock. Can anyone offer advice that a
beginner can understand in regard to this?
I wrote an update query that he can run that will subtract the qty ordered from the units on stock
for orders with order date >= whatever date he enters, but if he enters a date he's previously updated it will update those records again. I think this is not a good way to go but the only way I could accomplish. I have him using the same order entry form with customer name Restock to add items to inventory by putting an if then else statement in the before mentioned update query. This just adds to inventory if customer name="Restock" else the qty ordered is inventory units in stock -qty ordered.
I'm sure someone can suggest a better way. A friend mentioned dsum function but can't explain how to apply it in this instance. Thanks so much!!!!
I am trying to build a db for Inventory purposes. Its a business with about 10 different areas. Several areas have the same items.
I am trying to create a db where I will enter our starting inventory - lets say 900 wine glasses.
Area 1 has 200 wine glasses, area 2-8 none and area 9 has 200. Area 10 is the storage and has the extra 500.
We count every 3 month, each area separate, combining all numbers in the end.
I need to add a par level to each outlet. Let's say area 1 = 200 and area 9 = 200, storage (10) = 500.
After the first quarter, area 1 has 150 left, area 9 = 100 and storage 400.
1) how do I establish par levels for all the different areas. I want them to show in a subform when I open the inventory item in Form view.
2) how do I enter the opening inventory (#0) and every consecutive inventory take (it should track each one by outlet and totals)
3) (Form) all inventories should be tracked on another tab in the inventory item tab as a graph (par-loss-purchase)
4) (Form) on the front page of the inventory tab, it should show the par level (total), and the LAST inventory amount (total), and the difference between the two amounts (what we should order) and outstanding orders
5) (Form) on the second inventory tab it should give me a spreadsheet type of subform where it shows each outlet (area 1-10), their par levels, their LAST inventory count, purchases, requisitions,...
6) I would like to be able to track requisions, i.e. area 5 has a special event and requisitions 300 wine glasses from storage (area 10). They are then requisitioned back to area 10, but the movement is reported.
Or area 1 requisitions from area 10 to replace broken glasses.
7) I need to track purchases. After inventory we discover that we need to order 250 wine glasses. I would like to enter outstanding purchases (PO number, cost,...) and then received purchases.
8) How do I get all forms to open as large screen shots instead of mid-sized? It looks stupid when it multilayers them.
I am not sure where and how to calculate formulas in Access, anyone that can help me with all this? Especially how do I tell Access in Form view to always show the last inventory take, and the difference to par level. I want the information to be saved in a table, not just a calculation on a form. How do I have to build that part of the db?
If anyone can help, it would be greatly appreciated! I am just starting on the whole thing (so explain like you would for a dummy). I haven't seen any threads that would help.
Thanks in advance - Julia
I am in a second semester in college (Accounting) and I was asked to create database for computing services to track all software such as (Purchases,Inventory,Installation,)etc. In a first semester I took Ofiice 2003 class and Access was part of it.It was very brief overview but I got hooked. Here's what I have so far and my question is.is this going to work?
tblSoftware (I call main table)
PK SoftwareID (such as A1for Acrobat Pro)
PK PurchaseID (Auto No.)
Staff (Purchased By)
PKManufacturer (Company Name)
and rest like phone numbers,contact name,fax number,website) etc.
tblSupplier (pretty much the same as manufacturer)
PKInstallationID (Auto No.)
PKCampusID (Auto No.)
CampusName (There are 7 different campuses)
PKDepartmentID (Auto No.)
DepartmentName (Approximately 25 Departments)
PKOfficeID (Auto No.)
PKStaffID (Auto No.)
Staff (Names of software users) or college employees
Pretty much what I like to capture with this database is,all the purchases,inventory and installations
of all the software on campus.
After this, I have about 10 years worth of accumulated data in Excel to move to new database.
Lot of info on every purchase since then is missing in Excel.Hmmm..don't know what I"ll do with that?
Thanks for all your help. Jurotek.
Got multiusers accessing my Access db.
Typical size of one of the db's is 20MB
With Access 2003 we could have 50 users accessing all day and it would never get to more than 22-23MB
With Access 2007 it is now bloating to 200MB within a few hours!
Nothing's changed on the db - it's still Access 2003 format being opened in Access 2007.
There's images in there but they are linked images.
I can enable auto compact and repair on exit but the users will stay in the db all day, so only at the very end of the day will it get compacted.
But that's not the issue - the question is WHY is this happening with Access 2007.
I'm a novice Access user and been given an old access database (pre-2007, not sure which version) which I've opened in my brand new Access-2007 program, with "Access 2007 - The Missing Manual" by my side.
The trouble is that the old database looks nothing like the pretty pictures in the text book:
* Under the office button there are only 3 options: Close Database, Exit Access and Recent Documents.
* The ribbon has only one tab: Home, most of which is greyed out.
* The thing is stuck in Form View and there is no menu to switch view in the Home ribbon tab.
I can't, for example, see any way to get the Data Sheet view of the database.
Anybody know why this might happen?
Background Info: I developed our main department's Access 2003 split database which is on a server for 15+ Users. I've now been given Access 2007 for development -- Users still have 2003. There are also 2 other smaller databases that are not split (.mdb).
Problem: In the split database, I've saved the Application .mdb as 2003, relinked and made a new .mde. But the Users still cannot open the database. (I did this in a copy until I figure out the nuances with 2007.) The 2 other unsplit databases can be saved as 2003 version and Users can open OK.
I'm grateful for any suggestions on working with Access 2007. I've been trying to tackle the ribbon which is a whole other question.
I have an Access 2000/2003 database which opens up and displays correctly in Access 2007, but when I go to use the File>Print command, or for that matter any of the other pull down menu's nothing happens?
Has anyone else experienced thisproblem, or does anyone know what I am doing wrong?
Any help greatly appreciated.
Does anyone know a simple way to link an invoice to an access database for instant inventory management. Is there a generic template out there?
I am trying the beta version of Access 2007 and realize that when converting Access 97 to Access 2007, the new db in Access 2007 has an extension of .accdb. I rename the db file name to xxxxx.mdb and the VB app fails to recognize it.
Is there any way to convert the xxxx.accdb (Access 2007 beta) to xxxx.mdb so that I can try my app against the Access 2007 db?????
Thank you for your time,
I am trying to copy/replicate an Access 2007 database. My objective is to have a new copy of the database, without any records in it.
Can't find anything in the Access help about this -- what I find in my old Access 97 does not apply.
I am kinda new to 2007 and have at the moment a simple database.
I want three things
A: Use the database on a server.
B: Restrict the use to users etc as i used to be a ble to do in Access 97 i think (do i have to make a MDW if so how ?) 9I tried ACCDE but does not seem to do anything)
C: When users use the datavase not see all the normal access editing stuf..
I have built a database with Access 2007, and intend on sharing the database amongst two users.
I am running Vista, and the two PC's or on a peer to peer network. Both with Vista.
I have split the database, and put separate copies of the front end on each of the PC's.
The backend of the database is on the public drive on my PC.
Access opens and functions correctly on both PC's with 1 User logged in.
However, if both users are logged in, my collegues PC crashes when it tries to open forms. This does not happen on my PC.
Does anyone have any ideas on what I need to change. Thanks in advance.
Just upgraded to Acccess 2007 and have ownership problem extracting queries from a 2002 db.
When trying to import into a new 2007 or export from the 2002, get "you have no permission" message
Have created a new user tried to change owner of the db, but no permission
File security looks correct in that I am owner of the file with full permissions, but Access doesn't recognize me as the owner
Tried logging in as admin, but it isn't the owner either
This is not critical, just very time consuming, this is a test project, but it would be nice not to have to rewrite about 30-40 queries and equivalent forms
Any guidence appreciated
I am not sure where to post this question. I remember finding a forum just for Microsoft Access 2007 questions, but I couldn't find it again... Indifferent
Anyway, I have created a database, and have been testing it with my application. I have some data in this database. I was wondering if there was a way in Microsoft Access 2007 to create an empty copy of the database for release. In other words, I would like to create a copy of the database with all of the tables and columns, but without the data (rows) that I had entered as part of using my testing database...
Does anyone know if I can do this?
If anyone knows where this Access 2007 forum is, or resources that might answer my question, please let me know, as I would greatly appreciate that.