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:

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 Replies


ADVERTISEMENT

Table Design For Simple CONTACTS Database

Aug 20, 2005

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

View 4 Replies View Related

Simple Inventory Update From Form

Nov 28, 2006

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.

Thanks for any suggestions. :o

View 6 Replies View Related

Simple Inventory Of Ind. Parts But Selling Kits -- Please Help!

Nov 3, 2004

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.

Thanks.

Richie

View 5 Replies View Related

Forms :: Design Multiple Branch Inventory?

Aug 14, 2015

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.

View 1 Replies View Related

General :: Simple Store Keeping Inventory Database

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

Too Simple To Be True? ...is This Design Proper?

Apr 30, 2008

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?

Thank you for the time & help!

View 4 Replies View Related

Help With Form Design (sorry, Time Pressure Has Led To A Relatively Simple Question)

Oct 6, 2004

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.

Can anyone shed any light onto this problem.

Sorry to take up time.

mjc65

View 1 Replies View Related

Inventory Table

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

Transaction Table For Inventory Control

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

Help To Create Inventory Table For A Chart

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

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 1 Replies View Related

Inventory - Auto Deleting From Table

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

Beginner-Tutorial For Retail/inventory Table(s) Set Up

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

Modules & VBA :: Add Items Received To Inventory Table?

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

Tables :: Transaction Table Setup To Adjust Inventory

Apr 22, 2013

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...

View 1 Replies View Related

Modules & VBA :: Inventory - Show List Of Items That Aren't Already In A Table

Jun 19, 2015

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

[Code] ....

View 13 Replies View Related

Design Table So That When A Box Is Checked Data Is Transferred To Another Table

Mar 23, 2012

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?

View 8 Replies View Related

Help With Table Design

Nov 28, 2005

I am having problems coming up with a Table design and need help.

I am trying to design a table that will have 8 categories, 47 subcategories, and then choices within those categories.

Much like the following (this is only one category example, there will be many):

(Main category) Floors -> (Sub categories) Joist, Carpet, Vinyl Flooring, Wood Laminate -> (Choices for Joist Sub Category) Structure Wood 2X8 Joist, Plywood 2X8 Joist, Structure Wood 2X10 Joist, Plywood 2X10 Joist

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.

View 1 Replies View Related

Design Table

Aug 30, 2006

I have 5 tables with the same data (row and columns). How do I make it into a relational table?

View 3 Replies View Related

Table Design

Jan 29, 2008

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

2 newest model 3/08

View 1 Replies View Related

Table Design Help

Oct 23, 2004

Hi all, i wonder if anyone can help?

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;

lngDVD_ID
strDVDTitle
strYearReleased
lngDiscAmount
dtmFeatureLength
lngCertificationID
ysnBoxset
ysnExtraFeatures
strDirector
lngMovieStudioID
lngCategoryID
curPurchasePrice

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:

View 3 Replies View Related

Table Design ??

Mar 14, 2005

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?

I appreciate any advice,

Sup

View 7 Replies View Related

Table Design

Jun 2, 2005

Hi,

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.

Again sorry for the long post.

View 1 Replies View Related

Table Design

Jun 5, 2005

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?

View 6 Replies View Related

Table Design Help

Aug 30, 2005

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:

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved