Newbie Needs Help With Tracking Inventory In Orders Database
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!!!!
View Complete Forum Thread with Replies
Related Forum Messages:
Orders Database
I know this is probably very simple but I do not know how to do it. I have: Table 1 - customers Table 2 - Orders At the moment, when a customer orders 2 items, I have to create 2 seperate order numbers. I would like to have both items under one order number. Can anyone help with an easy solution. Thank you
View Replies !
Inventory Database Help
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.
View Replies !
Manufacturing Inventory Database
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!
View Replies !
PC Inventory And Allocation Database
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. problem: 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 Drive 2 Drive 3 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.
View Replies !
Inventory Control Database Help
Hi 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: Supply Table Person Table Supplied Table On hand table Orders 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 Thanks Marcus
View Replies !
Tracking Changes To Database
Is there a way to track changes made by different users to access tables? For instance if several users update or change data in various records is there a way to track when and what was changed (before and after data)?
View Replies !
Tracking Database Help
I consider myself an intermediate ACCESS user and can get around it with little trouble with the help of the internet and the help file. I am trying to design a database that is fairly simple in design, it is for tracking the status of documents at our work center. Basically we have personnel reports that are passed around the different people and different dates. I have a main form that has the info on the person along with dates that it is due. Then a sub form that has the date sent and where it went to. My problem lies in that I want to keep track of the whole history of the document but when i print out a report or export the data to excel to put up in powerpoint I need to have just the most recent date sent only item. I have 2 tables one with user info and another with the routing info with its own primary key along with a identifier key to link the primary and sub form. Of course it is easy to create a report with the data to show all the different routing steps but not just the one with the most recent date. Thanks in advance for the help.
View Replies !
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! Thanks!
View Replies !
Table Organization For Inventory Database
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!
View Replies !
Question-Database Tracking
I would like a field on my main switchboard that displays the last time the database was modified. Simple question, and I can not find an answer anywhere. Thanks!
View Replies !
Issue Tracking Database
Hi team. I've had a bit of a search around the forums and haven't found this anywhere. Does anyone have a sample issue tracking database for an IT department? I don't have too much Access experience so am looking for a little bit of a headstart on this. It seems relatively basic though. The fields I think I will be needing are the following: Log No Status Priority Type Project Raised by Person Raised by Department Description of Issue Status 1 Active 2 On-Going 3 Hold 4 Finished 5 Cancelled Priority 1 High 2 Medium 3 Low Type 1 Bug 2 Enhancement 3 Other Project 1 Lotus Notes 2 File Maker 3 Tumbleweed 4 Windows 5 Microsoft Word 6 Microsoft Excel 7 Microsoft Outlook 8 Adobe Acrobat Raised by Department 1 Research & Development 2 Customer Service 3 Finance 4 Data Solutions 5 Communication Services 6 Production 7 Stores 8 Operations 9 Other
View Replies !
Employee Tracking Database
Would like to hear from anyone that has designed a database that can be used to track employee compensation on a year to year basis. I am current designing a database to do just that but I'm having a hard time deciding on how to setup the tables. What they use this for is yearly reviews so I need it to be able to pull data from prior years and the current year. I've designed one but don't think its going to work for me. Just interested to see if and how someone has done the same thing. Thanks, Chester
View Replies !
Simple Tracking Database
Hello, I am trying to figure out how to setup my tables. The tracking system i am trying to create will have these information: the Tables will contain these information - Package number - Received date - Sent date - How long have we had the package - Status (Sent or Pending) I will also need to figure out who received and sent the package - Employee name Please advise if I setup the tables correctly. I will also need to query these info: - all packages received 3 days ago - how many packages an employee received and sent - How many sent or pending Thank you for your help.
View Replies !
Specific Item Tracking Database
Hello Everyone, I've been looking at all the fine db samples and advice provided in this forum and they are giving me a huge boost in my new job as a DB Admin. :D Since this forum has a very active group and you guys know and done it all, ;) I thought I would ask if anyone out there has done or could give me an idea on how to buid the following db: I need to create a db that will keep track of a specific item from the moment it comes in 'til it goes back out. This could be weeks in the process. What happens is that this is a program where people donate old worned out wheel chairs and they get totally overhauled. As the chairs come in they need to be tracked from start to finish to see where they came from (Somewhere in the USA), what process it took to overhaul it (ItemID and Current location [Storage, Repair BIN, Repair Table, Completed Tabe, Outbound Table, En Route to Destination, etc] and status [For parts, fixable, paint room, repair room, completed]) and where they were donated to, a city in some foreign country (Africa, Asia, Mexico, South America, Etc.). If someone has something I can use would be great, but if not any idea on how to do it best, would be greatly appreciated. Thanks! René
View Replies !
Looking For Free/shareware PDA And Cell Phone Tracking Database
Hi, I have a list of 500 PDA users, spread over 7 offices. I want to keep an eye on what happens with the devices and their SIM-cards. As they are often swapped between offices and SIM-cards get lost. I am pretty sure that I don't have to re-invent the wheel. Do you have any idea of a free/shareware tracking tool for PDA's and cell phones ? Thanks in advance !
View Replies !
Importing Daily Excel Spreadsheets Into Item Inventory Access Database
Hello, 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.
View Replies !
Newbie... Need Help With Database!
I have an old database that would come in handy as a membership roster, but if someone drops out, how do I delete them from the database? I tried deleting the fields, but the blank form is still there. Also, I would sure like to add a search feature so I could go directly to an individual without scrolling through all the names. Can anyone shed some light on this? Thanks I've included a link to it below: http://blindactions.com/temp/ALR.zip
View Replies !
Newbie Trying To Build 1st Database
I have attacked quite a few guides ( Dummies & Absolute Beginners ) on how to build a database of my gardening clients, but have run into a problem that they can't seem to resolve. Is there anyone out there who can help me with "referential integrity" and trying to link relationships between the various tables I have created.
View Replies !
Newbie Needs Help For Access Web Database
Hi all, Access is not my strong point but I have got it to do most of what I need apart from the fact that when I create an update page in asap and then try and update it i get an error if any of the fields in the update page don't have any data in them. As there are several fields and over 500 records in the database is there a way in Access where it can automatically insert a space in all the empty fields which would then stop the asp page from crashing. Or does someone have a better suggestion of solving this problem. All help appreciated Regards Paul
View Replies !
Newbie: Designing A Database In Access
Hello, Hoping some one could assist me? I am busy designing a database which I am going to put in access. I have designed the tables and am now not sure how to link them. I was given an excell spreadsheet with headings and am now trying to make this into a database. Example : I was given Co-ordinator Name - Which I now split up into: Co_First_Name Co-Last_Name Co_Initials Co_Area_Code Co_Tel_Number Co_Mobile Co_Email Now there other fields like this , for example Regional Manager Name - which I have split up as above into a seperate table. These fields all relate back to one Code - Called District There are also a fields which needs to show the a fee required the fee paid and fee outstanding/balance . The rest of the information required I can create check boxes for , I hope! Anyway my question is how do a relate all these different tables together? This is my mental block. And the check box tha information - is it stored a another table? I hope I am explaining my self correctly and I hope some one can make sense of my question
View Replies !
Searching A Database; Newbie Question
Hi there, I am designing a search tool for a database table which has attributes such as part number, manufacturer's part number, description, etc. Each item in the database has all these attributes associated with it. When I search, I am trying to allow a popup to occur when I want to access a modified report that has my search results computed. I created a query and a report, added some SQL code to it, etc. I can basically click the report and have my inquiry window come up asking for the part number, but I'm not sure if it's associated with my database because when I put in the #, the report comes up blank. I right-clicked my Report and look under "Objects that I depend on" and my Queries tree is full of dependencies that point me to my database table. There is, however, still no report with any substance that I can pull up no matter what part # I put in. Does anyone know what's going on? I know this is really easy and it's driving me crazy cause I've never used Access before. I don't necessarily have time for tutorials and any lesson enrollment since it's for work, and my profession has absolutely nothing to do with this. Ha. Thanks for any help you may be able to offer. Richard
View Replies !
|
TRACKER
