Table Organization For Inventory Database
Sep 1, 2005
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
ADVERTISEMENT
Aug 31, 2007
I have at least 200 excel spreadsheets that get updated every day with closing prices of commodities. I want to run a breakout query against every spreadsheet to see if today's close is a new 20 day breakout. What is the best way to organize the access table or tables? Put all the data in one table or have separate tables for each contract and run one query against the group? I need to be able to update the tables everyday so that today's close info is included. I have a working query that works with a single dynamically linked spreadsheet to table, but I don't want to set this up for all 200 spreadsheets. Can this process be duplicated? I have tried to modify the query to run against two separate tables, but was unsuccessful. Since there are so many potential tables, if that is the best way to do it, how can you say select all tables and then run the query?
Here is the query I have written:
SELECT WZ07.Symbol, WZ07.AlphaDeliveryMonth, DateSerial(Val(Left([ddate],4)),Val(Mid([ddate],5,2)),Val(Right([ddate],2))) AS transdate, WZ07.DClose
FROM WZ07
WHERE (((DateSerial(Val(Left([ddate],4)),Val(Mid([ddate],5,2)),Val(Right([ddate],2)))) Between Date()-28 And Date()-1) AND ((WZ07.DClose)=(select max([WZ07].DClose) from [WZ07])));
The WZ07 is the December Wheat contract for 2007. This is what the excel spreadsheets are named. I had to do a dateserial command to turn a number into a date(couldn't make access recognize that 20070831 was August 31,2007) All the column headings on all the spreadsheets are the same: Symbol, AlphaDeliveryMonth, DDate(transdate),Close
I know that this is both about tables and queries so I wasn't sure where to post this. Thanks for your help.
View 9 Replies
View Related
Apr 5, 2007
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 4 Replies
View Related
Aug 4, 2005
hello, i am looking for a music inventory database, anybody any idea where i might get one
cheers
phil
View 1 Replies
View Related
Sep 28, 2006
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 3 Replies
View Related
Jun 27, 2005
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 9 Replies
View Related
Nov 1, 2005
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 6 Replies
View Related
Feb 8, 2015
I need to create an inventory database that will track customer owned inventory as it is received stored serviced and sent back to my customer. I am in the oil and gas industry and store large amounts of customer owned pipe. many joints will have the same part number but each joint is one of a kind in that it has a joint,heat, lot, and batch number. here is an example,
Job Name: Shell ex
Part Number: 129001
Quantity: 1000
Here is an example of joint info I need to record
Joint Number:193
Heat Number: f21283
Lot: 202
Batch:12j22
Reference: byy2112
Tally:33.2
Each one of the thousand joints will have a combination of this information making it one of a kind.
My system needs to allow my users to receive jobs, record where we store the pipe in a rack location, pull the specific joints out of inventory as the customer calls for them. We will receive 1000 at a time but the customer calls for portions of that job over long periods of time..
View 10 Replies
View Related
Jul 19, 2013
Create a database to inventory pdf files (and content ) .
I work for a building department and we are working on scanning old building permits from the 40's and 50's
Also i would like to inventory blueprints .
View 4 Replies
View Related
Oct 24, 2004
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 1 Replies
View Related
Sep 9, 2013
I am trying to create a simple store keeping In and Out inventory database using Access, I thought I had made it but looks like I am missing something here.
The store works on SRV (Store Receiving Voucher) and SIV (Store Issue Voucher). Products will be added based on SRV and will be issued out based on SIV. So far I have created the tables as you can see in the figure. One thing I am not understanding is where to keep the record of the Current Quantity of each product, lets say an Item has been added or issue out, it should be added or deducted accordingly from that specific products overall quantity. Right now I have a sample field within products table as you can see with the name QtyOnHand but that doesn't seem to be logical.
View 7 Replies
View Related
Feb 7, 2015
Im building an inventory database for my company and have the tables, relationships, forms and query's built but im struggling to figure out a way to make all the quantities move around when I have a purchase order or sales order that either adds to removes from inventory.
My database is fairly simple as all I need to do is track what is coming and going and what is the current levels of inventory. I have for instance a product table that has all the information on what products I have and what their part numbers, item number (primary key), description and so forth, separate customer tables and supplier tables, as well as an inventory table setup similar to the northwinds database. I used queries to combine my purchase order and purchase order detail tables and to combine the sales order and sales order detail tables to make sales orders and purchase orders through their respected forms and all is good in that sense but it doesn't move the inventory numbers just lists what is in each purchase Order/Sales order.
View 3 Replies
View Related
Apr 17, 2013
I want to make inventory fifo(first in first out) db. Example:
February
1- beginning balance : 800 unit @ 6$ per unit
4- received 200 unit @ 7$ per unit
10- received 200 unit @ 8$ per unit
11- issued 800 unit
12- received 400 unit @ 8$ per unit
20- issued 500 unit
25- returned 100 unit to storeroom to be recorded as latest issued prices
28- received 600 unit @ 9$ per unit
The report must be like pic attached
I dont know how to make transaction to do this....
My database in attachment
View 3 Replies
View Related
Feb 1, 2005
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 3 Replies
View Related
Jul 20, 2015
I want to track inventory. All our items are tubes that are stored in boxes in a 10x10 grid. So I have a single access table with columns for tube ID, box ID, and position in the box (numbered 1-100). That works fine in terms of 'where can I find tube x'. However it's also useful to be able to look at the physical box & check that there are tubes where should be tubes, and empty spaces where there should be empty spaces. So for each box in the database, I want to print out a 10x10 grid, with the appropriate tube ID's in the appropriate place. Then I can easily check the layout in the database against the layout in the actual box.
View 14 Replies
View Related
Sep 19, 2005
My primary key for my database a WorkOrder Number. The issue is that sometimes problems span a selection of WorkOrders. For example, if we get a lot of bad parts, 3 different WorkOrders might be affected, and each may have different resolution to the effect.
To speed up data entry, I've had a request to make a button that copies the all the information from a current record into a new one.
If much of this information repeats, it this a poor way to handle this data?
Currently it is pretty straight forward with ostly one->many relationships, and this would make things pretty complicated with a bunch of many<->many relationships.
Just want to know ya'll opinions.
View 2 Replies
View Related
Nov 11, 2005
Hi, all.
Im trying to show multi level organization with a query and im not sure how to do it. I have a table that is named Originators, that includes all of the persons personal info and this table gererates a OriginatorID. Next I have a table called OrgTree that has the fields as follows:
OriginatorID---Auto lookup to Originators---OriginatorID
LastName--Text
FirstName--Text
StartDate--Date/Time
UpLineOrigID--Autolookup to Originators-OriginatorID
UpLineLast---Text
UpLineFirst--- Text
This works so far, but I would like to see all the people brought in by originator (a) and all the people brougth in by those people and so on. I guess this would be a tree, but im not sure if thats my problem.... Can this be worked out with a query or do I need to do something else. God I hope it can be done with a query.
Thanks for any help.
Scott
View 2 Replies
View Related
Aug 7, 2015
I have a Inventory DB and i want to integrate scanning. So far i have a table called StockInfo, this table holds information about bar codes (device type, make, model). I have a form called Scan_Barcode, on this form I want to be able to scan a bar code into a text box (text0) and use a afterupdate and requery function to display the results onto a form called EnterInventory.
Not only just the results but I want certain columns from the query to display in certain text boxes on this form. I have a query called FindProduct. that filters the criteria by whats entered on the Scan_Barcode text box (text0). So when i scan a certain bar code i can get the query to display certain product information for the bar code. I just cant get the results onto the HarwareStock form.
View 8 Replies
View Related
Feb 27, 2008
I'm a newbie. I'm setting up a database to monitor inventory then allow users to make reservations from the items in inventory. I'm a little confused.
I have the follow tables setup.
Category
Charger,BB Data Only, BB Phone, Broadband Wireless Card, Cellular Phone, Laptop, Laptop Accessory
BB Data Only
Device, Model#,Serial#,IMEI,PIN#,Deactivated
BB Phone
Device,Model#, Phone,#IMEI, PIN#, Deactivated
Chargers
Device, MTHMID, Type of Charger
Laptop
Device, MTHMID, Manufacturer, Model#, Serial#
I also have tables for manufactures and service provider, can i add a lookup to the service provider table to the bbphone table
basically i want to create a form that will change the information requested based on the type of device selected.
I'm confused about how to setup my main table, and the relationships between the tables.. please help
View 3 Replies
View Related
Oct 27, 2005
After a lot of searching, printing and highlighting I want to double check that I have the right idea.
I have a simple inventory control database. From reading the forum, it seems the best approach is to drop the detail lines table of the purchase order (incoming) and the detail lines table of the sale invoices (outgoing) and make one table for all transactions.
The Transactions Table would also keep records of inventory adjustments from inventory counts, etc. Each transaction would be given a type so I know in which direction the stock count moves in (adding or subtracting) when I sum the totals for each item.
Otherwise, I need union queries to ensure all tables are considered in my calculations.
My database is simple (thankfully). Let me know that I am on the right track I like the solution but like any new student, a little reassurance is appreciated.
View 3 Replies
View Related
Feb 12, 2007
Hi, I was asked to help create a very simple Access DB for a small restaurant to help them keep track of inventory.
The only things that the owner wants to keep track of are:
1.Item Catalog
2.Date and Quantity of Item IN or OUT
3.Current # of Items
My problem here is that there are 5 Item categories, each with a relatively lost list of Items under each one.
1.Drinks
2.Ice Cream
3.Kitchen Supplies
4.Grocery
5.Etc.
Will it be advisable for me to create an Item Catalog TABLE for each of the category instead of piling all the Items into just one Item Catalog TABLE?
The Designs I’m currently considering are:
A.)
TABLE SET 1 : Catalog of Items
Fields (ID, Name)
•TABLE Drinks
•TABLE Ice Cream
•TABLE Kitchen Supplies
•TABLE Grocery
•TABLE ETC
---------------------------------------------------
TABLE SET 2 : Movement IN/OUT of Items
Fields (Movement ID, Name, Date, IN-Amount, OUT-Amount)
•TABLE Drinks
•TABLE Ice Cream
•TABLE Kitchen Supplies
•TABLE Grocery
•TABLE ETC
B.)
TABLE 1: Catalog of Items
Fields (ID, Category, Name)
TABLE 2: Movement IN/OUT of Items
Fields (Movement ID, Name, Category, Date, IN-Amount, OUT-Amount)
I’m really more inclined to choice A because I just re-learned Access last month and will be creating my first serious database just now. I don’t want to choose a more complex table structure (choice B) that might cause problems with the DB later (as it will really be used to keep track of real-life inventory and might screw up their operations if it goes haywire). Is Design A viable? Or flawed?
Another question I have is with the structure of the Movement – IN/OUT table. Is this workable? My thought is just to let the user enter something like:
Drink [date] [IN] [OUT]
Coke [date] [2] [0]
Coke [date] [0] [1]
In the database, then compute current Quantity of the Item using a query (Sum[IN] – Sum[OUT]) . Will this work? I’m a bit apprehensive with this because I’m thinking I should have another TABLE that will store the actual inventory Quantity instead of the Quantity just being computed through query. But doing another TABLE for Quantity does complicate stuff a lot since it will have to determine which Quantity value gets added to or subtracted from based on many criteria. Will it be OK just to use the query to compute Quantity left?
Thank you so much for any help on this!
View 14 Replies
View Related
Nov 29, 2004
I am in Access 2003 and I need to create a chart of the inventory levels of a product. I have the daily production and I know that the amount distributed out of the warehouse will be the amount produced that day up to 200 items. However, I can't figure out how to keep a running balance of 'ending inventory' if the amount is over 200, so that it would be the next day's beginning inventory.
I am trying to figure this based on the formula:
beginning inventory + new production - distribution = ending inventory day 1
day 1 ending inventory (beginning inventory) + new production - distribution = ending inventory day 2
etc
I wrote a query to put the new production and the daily balance to retain in inventory (the lesser of daily production or 200). I need my query to be run so that I can put it into a chart, so I need a column to be 'ending inventory' by day so that I can have a line chart going across time.
Thanks for any help. I'm stuck.
View 2 Replies
View Related
Jul 26, 2012
I use Access on a daily basis as an end user at work and am building my very first database for my woodworking business I started this year.
So what I am wanting to do is import my Excel inventory list to a table. Along with the categories I have in Excel, I have created inventory numbers for my products. Since I'm not exactly a businessman and accustomed to inventory systems, my inventory numbers just start with "AA" and go on up (I chose this as opposed to assigning numbers so customers aren't confused when they see a price tag).
Anyways, what I am doing is creating a Sales table and accompanying form for entering sales of each product. What I would like to happen is every time I make enter a record into the Sales form, I would like for the item to be deleted automatically from the inventory table.
View 2 Replies
View Related
May 5, 2007
Access Guru's:
I am a beginner at best with Access. (I'm using Access 2003) I have a small in home business as a second job making silk flower arrangements. I am now to the point that I need to track my inventory, quantity used, COGS, etc. Most of my arrangements are in gift shops on consignment.
In lieu of buying Quickbooks Manufacturing ($$$), I want to start tracking everything with Access so I can at least get some nice reports at tax time.
The problem I am having is I cannot translate the tutorials that Microsoft supplies to this application, they only cover setting up basic tables. (ie: in a retail/inventory application - do I set up 1 table, 1 table & subtable, 2 tables????)
I set up a table so far with 4 basic inventory fields:
Item description (record includes: flower, vase, foam, etc) / Color / Qty on Hand / Cost. Then (on the same table) finished goods fields of: Qty Used / Arrangement # / Qty Left / Sold for. While trying to figure out how I am going to track how much each arrangement cost, I'm not sure this set up will work.
I probably went into too much detail for my question, but I wanted to give some kind of background. Is there a CD/Book/Tutorial that specifically guides one through setting up a retail/inventory table?
Sorry so wordy.
Catherine
View 1 Replies
View Related
Feb 18, 2014
I'm trying to add items received to my inventory table. If the item is already in the table, I just want to update the number and cost etc. If the item is not in the inventory table I want to add it. My problem is determining which item in the source table is already in the target table so I can either update of add. how to find an item number in target table by looping through the source table?
Here's the code I've written which doesn't work.
Dim I As Integer
Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset
Set rs = Nothing
Set rs2 = Nothing
Set db = CurrentDb
[code]....
View 5 Replies
View Related
Aug 3, 2013
I have one table named Entry. This table have account number,work organization, price, date and etc.
I use a combo boxes in form which is connected to query and I can easily choose one account number,one work organization and date to see an report. There is also a possibility to left some of the fields blank, it will bring report if is null.
Now I need to make a form that have a possibility to bring up a report for multiple selections of account number and work organization?
View 4 Replies
View Related
