Help Please On Table Design For Simple Inventory DB
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:
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?
Hello All, At work we have a large and messy Contacts list so I decided to set one up using a database. At present I have 3 tables:- Companies (custID,companyname,address,etc) People (nameID,firstname,middlename,lastname,custID) Phones (phoneID,phonetype,areacode,number,?????)
My problem is this, Some of the phone nos belong to the individuals and some belong to the company. If a person is replaced at a company I need to reasign the company phone nos to the new person whilst retaining the individuals and their personal phone nos. If a company is deleted I need to delete only the company phone nos. and if a person moves within the company I want the company nos to reasign to the new replacement but keep the personal nos of the individual. Now I see its going to be more complex than I thought.
Can anyone help me with the table layout and links. (nb this is only a simple database relating a person to a company without using departments etc.) Its main use is to provide phone nos names and addresses quickly. Many Thanks Peter
Hello. I'm trying to design a simple database for a bicycle recycling charity. I hit a brick wall when realising that I had never before attempted to update a record. :eek: Until now I've just used Access to add new members to a bus user's organisation.
If the shop takes delivery of 20 tyres, I need to add 20 to the current stock.
Is it possible to achieve a simple Form that adds to or subtracts a quantity from current stock, or do I have to learn some access programming?
I've looked through some textbooks and can't find a simple update solution.
I'm not sure what the best way to set this up is, and I'm hoping someone can point me in the right direction.
I have a long list of different parts that I have acquired and taken inventory of. I am selling kits that contain different amounts of certain parts. For example, Kit 1 contains 2 of part A, 3 of part B, and 14 or part L. I will only be selling complete kits and not the individual parts.
Here's what I'm thinking. I need a table for the individual parts that lists the quantities of each. A table for the kits and a subtable for the kits that lists the quantities of each part. A transaction table.
Then I just need a form that has a combo box with the kits listed, date of transaction and quantity shipped, and a button that runs a query to subtract from the quantities of the individual parts based on the kits table and subtable.
Can someone please help? I'd really appreciate any tips.
I wish to request if it is possible to design an inventory where multiple shops or branches can be accessed from the same place.
or
multiple shops can use the same program with a centralized database, such that every user can only access his or her report from anywhere they login from.
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.
New to Access, and having a heck of a time learning it...or rather learning how to correctly design databases.
All my expertise is with Excel. I'm creating a project where I use Excel to parse a non comma delimited text file, then feed certain figures into an Access database. This is all through VBA.
It wasn't until yesterday that I realized I had a problem. There are two text files with data that makes up one complete record. With what I already have built, and with what I have tought myself (ADO w/ VBA wise) I tought the easiest solution would be to create two tables that will hold the data from each respective text file. This is what I'm working with:
A store has a department with 5 areas of measurement that is collected daily. One complete record would be like this:
Date | Store | Dept | Sales | Cust Count | Item Count | Avg Price | Mix
...and there are (right now) 3 stores and 15 departments that are watched in this project. What I came up with for a table design was this. Fields with an "!" prefix reflects primary keys.
Table1 (using data from txt file 1): !Date | !Store | !Dept | Sales | Mix Table2 (using data from txt file 2): !Date | !Store | !Dept | Cust Count | Item Count | Avg Price
In each table, I have to have a compound primary key to make up what is a unique record. I just learned I could use a compound index and an autonumber as my primary key. Either way, I'm using the 3 primary keys in each table with a 1 to 1 relationship. This seems to work if I make a query.
Now, will the way I did it hold up? Is there a better, more correct way to do it?
Sorry about this but I have two forms in a split database, one for data entry, one for data editing. The underlying record source is a query pulling data from multiple tables which worked fine until we had to move the backend of the application. Now whenever anyone opens the data entry form (add mode only) there are no fields on the report for them to complete; they are confronted with a blank screen. The data editing one works fine, but this one cannot be put onto general access because only four people out of about 25 are allowed to edit the data.
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.
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.
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.
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.
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.
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?
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
I have a database that I use to put in orders for our shop and keep track of our part informations as well as paint and packing materials. Everything works good on this but I am trying to create a table for adjusting quantities on hand for packing material based on the part quantities and for adjusting paint in stock based on information given to me after the job is run.
There is already a relationship between parts and packing material as well as parts and paint. When I put an order in I would like to have it adjust out that many packing materials that are related to that part. Once a job is run I need to be able to adjust out the amount of paint used.
For the paint side of this I want it to track the paint used by order, we are trying to get a grasp on how much paint we are using for parts so it is important for me to know how much and when.
I am thinking I need a table that connects paint to orders and has quantities in it, then create a query and do the calculations from there... I do not know how to accomplish that but it sounds like it could be right...
I have a list box that allows multiple selections [Inventory]. I also have a combo box that has multiple selections [Shows].
Right now, user selects from list box and from a combo box and clicks a button. On button click, the items from the list box are associated with the PK from the combo and stored in a junction table. This allows me to quickly associate many inventory items to one show.
I realized that there I currently have no way to prevent duplicate Inventory+show records in the junction table besides having a composite key. This would be fine except no records get inserted into the junction table if there's a duplicate entry.
Ideally, I think that the user should select from the combo box [Shows]. This should narrow down what shows up in the list box [Inventory] in a way that Inventory items already associated with the show are not displayed.
If I have 10 Inventory items and Inventory items 1-5 are already associated with Show 1; after I select the combo box, the list box only displays Inventory items 6-10.
Here's the associated code
Option Compare Database Option Explicit Private Sub cmdAddRecords_Click() Dim strSQL As String Dim db As DAO.Database Dim rs As DAO.Recordset Dim ctl As Control
I am designing a database for my organisation. I have done most of it but am stuck on this. Ideally I would like to have a check box (in a table) that when checked a load of data is carried across from that record to a record in another table.
Given that I can't and don;t want to use VB is there anyway that this can be done easily?
This only shows the flow if the main category is "Floors", sub category "Joist", and then the choices under subcategory "Joist". There will be others for the other categories, subcategories, and choices.
Can someone help me or steer me in the right direction in setting up the database table/tables to accomplish this. Oh and BTW, there will be prices attached to each "Choice" under the subcategories.
I hope that this makes sense... any help would be much appreciated.
if i had a table with products and i want to keep a record of what has been chaged or added to this product, whilst keeping the old records how could i set it up in a databse?
the excel spread sheet looks like this
product commments
1 chnged this and that 20/08 1 modifyed component 21/08
It's a simple question really. I'm currently learning databases and am doing a project, just to show that i understand everthing i've learnt so far. I have to produce a small database.
There's nothing difficult about the project, and its a very basic database, but i was just hoping to ask a small question.
I'm producing a DVD database, to hold details on a collection. My main table holds the majority of the fields, as follows;
The problem came with boxsets. Originally i was just going to have boxsets as one entry, but some contain a few dvd's, each with different certification, etc, and i wouldn't be able to record running length, director, etc. So i added a ysnBoxset field (just to record whether a dvd was part of a boxset or not) and then made a new table with
lngDVD_ID strBoxsetName
So now i can record which are part of a boxset, and the name of the boxset they belong to. But.. then i got to the problem of Purchase Price. Obviously i cannot record an individual purchase price for those which belong to a boxset, and i want to be able to record the price of the boxset.
So my question is... is it acceptable to add a curBoxsetPrice field to tblBoxsets, which would allow me to store the price... and then just leave curPurchasePrice as £0.00 for those dvd's in a boxset??? Or is there a better way of doing it? (I know there is, but i'm a novice :( )
Thanks for any help you can give, sorry for the lengthy explanation! :rolleyes:
Within my db I have a table, tblProjects which currently has 36 fields. Each project will have a fixed set of 12 tasks, each having a begin and end date as well as a yes/no "completed" field. This would add another 36 fields to tblProjects.
Instead, I have created a new table tblTasks which has these 36 fields along with a PK(TaskID) and FK(ProjectID) and is related to tblProjects. I believe tblProjects is already normalized properly with relationships to tblCompanies and tblContacts as well.
I'm not sure if this was neccessary, or should I have just added the task fields to tblProjects. What, if anything, are the advantages of one way over the other? I'll be upsizing to SQL Server, does that make a difference?
Bit of a long one but I require some advise if people can supply it.
I've started creating a DB that is now getting out of hand due to me being inexperienced. The things I want to do now are getting way to complicated. From reading various posts I know that this is all being caused by my database design and while the DB is still young I'm go back to square one. I want your advice on this if you can to ensure 3 weeks from know I'm again not chasing up weird bits of VB code to make it all work.
I basically have a lot of information to hold that interconnects with each other. I'm making up a role profile DB (so we can assign define access rights to a computer system) I have lots of info to collate but the basic stuff is.
Staff Records (Name, business area & ID) Applications (What computer applications do staff require) Shared Drives (What access to shared drives and folders do they need) Shared Mailboxes (what access to shared mailboxes) Roles (users will be assigned roles to suit their requirements)
Basically what I have at the moment is below.
Roles Role ID Role Name and about 15 other fields that dont really matter for this
Applications Application ID Application
Shared Drives Shared Drive ID Shared Drive
Shared Mailbox Mailbox ID Mailbox
Each role will only have a select number of applications, mailboxes and drives from the overall list. so role 1 may give access to 2 of the 10 applications, 1 out of 5 shared drives and 3 out of 4 mailboxes. Role 2 on the other hand may need 9 out of the 10 applications, 1 out of the 5 shared drives and 1 out of 4 of the shared mailboxes.
I just don't understand how I can create a form from these tables that would display all the different roles with their respective apps, mailboxes and drives they would need.
any advice you can give to help whould be appreciated.
I am designing a db for our Law office. I want to have tables for different types of Civil Cases-ie Divorces, Deeds, Contracts, Personal Injury, Auto Accident, Slip and Fall, Product Liability, etc. The divorce, Deeds, tables are easy but I am having difficulty deciding on the personal injury tables. Heres my dilemna. Can I get away with one or two Personal Injury tables even though there will be blank data fields. For example, I will need a field for the name of the product in a Producrs Liability field. This field will be blank in all other Personal Injury Cases. I know the rule is you don't do that but my alternative is 15-20 tables for every concievable Personal Injury Case- Auto Accidents, Slip and Falls, Fraud, Prouduct Liability, Accounting, Attorney, Dental and Medical Malpractice, etc. This is in addition to other civil case tables, criminal case tables and federal case tables. Anyone have a suggestion?
I am not sure if which I should be using as the main entity for my tables. I am creating a database for hourly information on individual employees. Each employee will have the same types of hours to keep track of (weekly, bi-weekly, monthly etc) I naturally thought to treat each employee as the databases own entity and created fields for each type of hours but hit some trouble when trying to pull queries to compare each employees hourly information against each other to see who is being efficient or not. Each employee has the same exact fields but with just different numbers. We track each type of hour by week (Field One: Week 36, Field Two: Aug 28-Sep 03)This is the first time I am creating a database from scratch. All advice greatly appreciated and TIA :confused: