Designing A Stock Database Managment System (DBMS)

Nov 18, 2007

I am student, in high school, and I have to make a stock database, for ITGS, so as to receive my IB diploma.

I have created forms so as to enter stock information, and customer information, but I have no idea, as to how to connect them, so as to create an order summery and invoice. I started by creating 2 tables, one for the books, and one for the customers. I then made 2 other tables, one being an order table, and the other the linking table between the order and the books (see relation.jpg). I created a form so as to input information, but I can�t seem to get it to work, (see form.jpg)

Ideally, I would like the end-user, to select the customer's name, from a drop down list, and then for the rest of the customer information to update automatically, then, for the end-user to select a date, and finally the end-user to select books from the stock. Once the end-user has finalized the selection, they would ideally press a button, which would automatically print out an order summary/invoice (Maybe a report?)And that would also automatically reduce the quantity of the books in stock by one.

I have no idea as to how to do this and I would really need someone�s help, seeing as I have less than a week to hand this in by (The deadline is Monday 16th November 2007)

Here are the images
form.jpg - http://img30.picoodle.com/img/img30/5/11/18/f_formm_78d6ea7.jpg
relation.jpg -
http://img03.picoodle.com/img/img03/5/11/18/f_relationm_6280f62.jpg

Thanking you in advanced
CJ M

View Replies

Tables :: Designing Fee Receiving System - Due Field To Show Balance

Nov 25, 2012

I did using below:

One table with Student Basic Records with Course name and Total Fee

Tabel name Student_Rec and Fields are below
ID, StudentName, Course Name, RollNo, Total Fee

then another table name Fee_Details for receiving dues in installments.
ID, RollNo, FeeDue, FeePaid, Comments

Now i want the FeeDue Field should show the Balance feedue of each student

I mean it automatically check total fee due from student_Rec table and also check all previous entries for the same rollnumber in Fee_details Table and show the pending amount as FeeDue.

View 4 Replies View Related

Stock Control System

Dec 14, 2004

Hey again,

Im sorry for starting a new thread, Only I need a different topic name.

Thanks for the help Thomas, from the other thread, though my project has been blown completely out of proportion.
I decided to start from scratch again today. Having no help from the teacher is a major factor because he
knows only very basic database.

My initial idea for the project was to be able to set up a stock control system allowing 1 user, my Aunt to be able
to order shoes, manage stock by checking orders against deliverys and updating the stock records. Also some form of
allowing her to enter sales for particular shoes which would update the stock file.

I started with the tables

- tblStock - IDtype, CompanyID, Quantity, Colour, Size, Reorder

-tblOrder - IDorderNo, IDtype, CompanyID, Quantity, Colour, Size

-tblCompanyID - CompanyID, CompanyName, Address, Telephone, Email

I had the relationships set up so IDtype in tblStock connected to IDtype in tblOrder. I had the CompanyID in
tblCompanyID connected to the companyID in tblStock and In tblOrder.

I then added some models of shoes into tblStock. I created a query adding tblStock, tblOrder and tblCompanyID.
I dragged the whole tables into the query but because of the relationships, when viewing the query, there were no fields there. So upon deleting the relation between CompanyID in tblCompany and CompanyID in tblOrder, the data which I entered into tblStock appeared.

I then created a form based on this query with a combo box with its source set to tblStock allowing me to choose the models of shoes. I then put the other fields in as text boxes and made a confirm button. When the form was open, if I selected a shoe from the combo box the company information didnt appear in the fields. Once I had entered quantity, size and colour, I pressed the confirm button and this added that order into tblOrder, but no CompanyID.

This morning I realised the system should be set up including another table with a list of shoe models the user would be able to order because I had them selecting models which were in stock. I hadnt thought about this when designing the database.

I supposed what I am asking for is any general help into setting up this stock control system allowing it to do the functions that I require for my user.

Any help greatly appreciated

Thanks

Tempest

View 2 Replies View Related

General :: Control System To Put Stock That Come Into Specific Location

Jun 18, 2015

I have created a stock control system where i put stock that come in into a specific location i can put the same item over several locations depending of the size of the location or i can can put more than one product in a location .

The problem im having is when i do a order that say remove half of all the stock in it is split over more than one location i cant seem to get it to remove the stock from the location to show space available.

View 5 Replies View Related

Big Problem! Newbie Need Help ASAP Stock Control And Allocation System

Aug 11, 2005

:confused: Guys I need some help!

I am new to this database stuff and my work want me to produce a database which will not only keep stock on printers, scanners, monitors and base units we have in stock but want an allocation system as well.

Basically, a member of staff from a department will make a request for some items. These could be a monitor, 2 base units and a scanner for their location. I need a system which tells the user that the items or qty they want is in stock or not. I know I need to use an unbound box for this but dont know how.

Then I need a form which a IT techie can allocate a job too themseleves and take the item to them once this has been done they sign off the job.

I have a rough idea on what I want in each table but I am really losing time and i need some help or if anyone can do a quick one for me with forms etc I will be ever so grateful!

The tables look as follows:

Dept - DeptID, Name of Dept
Staff - StaffID, Name, Postion at college
Request - RequestID, Date, StaffID
Stock - StockID, Item (monitor), make (CTX), Model (17" TFT), QTY, Status (dead, working, faulty)
Request deatils - ? - Basically this is where the staff member will make their request for what they want.
Allocation - This part needs to be given to a member of staff and they needs allocating out
Techie - TechieID, Name, Postion
Location - LocationID, Location (where the item is going to)

If anyone can help me please post otherwise please please send me an email to mini_beest@yahoo.co.uk

Thank you and hope you can help

View 2 Replies View Related

Designing A Database

Aug 19, 2005

Could someone please give me some help with designing a database in access.
I know excel really well, and took a class on access, and have tried a few times to get started with access but always to no avail.

I have a company that installs real estate signs

1. we have about 200-300 agents (which represent about 10-15 offices)
2. 3 things can happen to 1 sign -
-1. installed the first time (charge)
-2. have a sold sign, for sale sign, or flyer box put on(charge)
-3. Finally removed(free, no charge)
-4. the date would have to be tracked on all of these occurences
3. We have about 10 products or things that can be put or hung on a sign.

I can set up the offices and the agents, and link them just fine. I can set up the products and do the work detail. The problem I have and cannot figure out is trying to bill or invoice this. Some agents are billed individually and some agents don't get billed, there office gets billed as a whole. Could someone please offer some insight on how maybe to go about this from the beginning.

Thanks for any help
Josh

View 3 Replies View Related

Designing A GUI For A Database

Jul 17, 2007

Hi

I've been asked by someone at work to design a "no brainer" gui for an access database but I am a complete newbie with regards to this - sure I can do simple queries to filter the information I what in design view but this is something else.

How can I design a gui that can be clicked on or is executed when the mdb icon is clicked that will allow people to access from a drop down box the area they need information about and then have to option to either have the information exported to excell or in a summary report to be printed off. Should I use the form wizard or the report wizard - though none of them seem to do what I want.

I've set up the database from the excell spreadsheets they gave me containing activity figures per operational region. The regions are listed in the first column and I wanted a drop down box to list them.

We have 6,000 entries on the database. These cover 8 regions with some regions containing maybe 500+ entries. As mentioned the regions are listed in column one of the database.

When I select the regions column in a drop down box instead of getting just one entry per region, I get every single entry for example 500 for region 1, 200 for region 2 etc. In other words it is reading the rows not grouping them according to region which is what I want

How do I group them so Access only includes one entry per region on the drop down box and what is the best way to design the gui to give the results I am after?

Thanks
Andrew

View 3 Replies View Related

Help Designing Database

Oct 1, 2004

Good day,
I am in the process of creating a database in Access.

I need some advice on designing the table structure.

I have 10,000 products.
There are 40 product types.
Some fields are the same for all products. (I.E. description, price).
Some fields are unique to the product type.

I am thinking this:

1. Have a table called products with all 10,000 products including description, price, producttypeID and productID.

2. Have a table with producttypeID and ProducttypeDescription.

3. Have 40 tables with ProductID and fields that are unique to that category of product.

Is this the right way to go about it?

Thanks in advance.

View 3 Replies View Related

Help Creating And Designing An ACCESS Database!!!

Sep 1, 2005

Hi guys, i need help with a sample database, and wondered if anyone could tell me the entities and how to calculate the prices etc. If anyone could attempt starting the database off for me it would be appreciated too!!

Here is the spec!!!

You are required to produce and document a design that meets the requirements of the McDuffs Burgers scenario:

The corporate office of McDuffs Burgers has asked you to design a database to help track its restaurants and managers. The database is to help the management show the total annual sales of each restaurant and the performance of each manager, as measured by the totals annual sales of all restaurants for that particular manager. Each restaurant is supervised by a single manager, but a manager is also responsible for several restaurants. The company stores typical personnel data (name, salary, and so on.) for each manager as well as basic data for each restaurant such as the telephone and address of each restaurant, its size in square metres, and total annual sales for the last fiscal year. The company would also like objective ways to measure the performance of a manager such as the total revenue for which they are responsible, the average annual revenue per restaurant, the average annual revenue per square foot, etc.

The database should also track the orders that are placed by individual restaurants to the corporate office for various food supplies. Each order is associated with a specific restaurant, and of course, a single restaurant will place multiple orders during the course of a year. The company uses a standard set of product numbers, product descriptions, and associated prices that applies to all restaurants. Each order can specify several products, and a single product may appear in several orders. The database should be capable of computing the total cost for each order.

Deliverables
1. Entity definition for each entity.
2. Entity Relationship Diagram, which must show entities, relationships and membership.
3. Relationship definition for each relationship.
4. Relations (This must include for each relation the primary and foreign keys).
5. Data Dictionary.

Furthermore:

You are required to implement the design (produced in the first part of the assessment - McDuffs Burgers - Database Design), by designing and creating queries, forms, reports and any supporting code. Revisions may be made to the design in the implementation process.

You should note the management of McDuffs Burgers has little experience of database systems and wish to be advised on the information the system can produce.

They require example reports demonstrating the capabilities of the system to:
- Aid the day to day operation of the business.
- Provide appropriate management information.

You must also implement an appropriate user interface to the database easy to use.

View 5 Replies View Related

Newbie: Designing A Database In Access

Nov 1, 2005

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

Designing Database Table And Relationships?

Feb 14, 2012

I want to design a database for an educational instution environment. My problem is knowing what tables to create and how to link them. I know for example that an instructor can teach more than 1 course and a course can be taught by more than 1 Instructor. This makes it a many to many relationship which is not remommended. I know that there has to be another table to bridge these 2 tables. I want to know what is a suitable name for this 3rd table and what example fields it should contain besides the the Keys form table 1 and 2?

Now here is the over all problem:some instructors teaches more than 1 subjectsome subjects are taught by more than 1 Instructorsome Classes are taught by more than 1 instructorsome instructors teach more than1 classSome classes do more than 1 subjectSome subjects are done by more than 1 ClassSome classes are done at more than 1 locationThis is what is needed:

To be able to enter student grades and attendances by classThe marks sheet should be entered in a subform displaying the full student names list for a particular class at a time.

2. To be able to display the marks and attendance of students as a class list.

View 1 Replies View Related

Designing A Proposal/Quote Reporting Database

Feb 2, 2006

As a basic user of Access I believe I have identified a use for the program for the purposes of a reporting task which I am charged with at the moment. We undertake the weekly tracking of proposals, which involves in brief, the action required for the bigger proposals, the teams involved in writing the proposals and follow up information as well as registration of wins and losses etc.
At the moment we use Excel but I am finding it extremely labour intensive with entries having to be removed and captured by only one person on a weekly basis and when you are talking about 40 to 50 new proposals registered nationally in a week and about 100 updates on existing jobs it becomes a perpetual case of chasing your tail.

Am I right in thinking that if we were to design a simple, effective database with a form design that all our research directors could use with ease, and we could merely run reports off each week that this would not produce a far quicker reaction time when it comes to pooling international resources but also far more effective reporting system with which to produce our management summaries?

I am aware that this will probably require quite a bit of consultation on design but before I go shouting the merits of Access to any of my superiors I wanted to make sure I could be confident of Access's capabilities first?

Can anyone advise? point me in the right direction?

Many thanks!

Lisa H.:confused:

View 2 Replies View Related

Designing Form To Make Intro Of DataBase?

Dec 16, 2005

I want to make my Intro of Database. When I open it. Just like you open NorthWind(Sample) database.

How to Remove the Title WIndow of Form.With all sides just like in NorthWind.
also removed the Record Navigation (|< < 1 > >| >*). I saw every option in Form Properties but didnt find.
Please Let me help in this Regard.

My second Question If I only want to remove Record Navigation only.But Title window displays.
May be from first question I can also get my answer.

Thankful in this regard.

View 5 Replies View Related

General :: Designing Database Using Existing Data

Oct 25, 2014

I have to design 2 databases base on some data that was given by my instructor but I feel like I'm missing something on both specially the 2nd database. I have attached my work so far as well as the instructions and the existing data..

View 2 Replies View Related

Queries :: Designing A Query To Normalize Existing Database

Dec 6, 2013

I am attempting to normalize an existing database. I've created the table structures necessary and now I'm designing a query that will update the new field in my primary table: "LabelBaseProduct" with the primary key from my new table: "tblBaseProduct" where the old field from my primary table: "tblLabels.BaseProduct" equals the description field from my new table: "tblBaseProduct.BaseProductDesc".

A visual of my tables:

tblLabels (Main table)
- LabelID
- BaseProduct (old field with text data)
- LabelBaseProduct (new field, needs to be updated with PK from tblBaseProduct)

tblBaseProduct (new table)
- BaseProductID (PK and FK to tblLabels)
- BaseProductDesc (Field that should be matched to tblLabels.BaseProduct)

I tried to design a query using design view of the query design and this is what I have:

Code:

UPDATE tblLabels, tblBaseProduct
SET tblLabels.LabelBaseProduct = [baseProductID]
WHERE (((tblLabels.BaseProduct)=[tblBaseProduct].[BaseProductDesc]));

When I attemted to run the query it told me that it was going to updated over a million records. I only have just short of 2k records in my database.

View 4 Replies View Related

Transport Managment Db

May 24, 2007

Hi People, great forum and loads of info to keep me going.

just a quick question

i have created a vehicle managment db, with a list of all our vehicles and there information such as tax due date, MOT Due date and copys of log books and MOT Certs,

what im wondering is, can i create an alert so it will alert me either by email or by a pop up box or maybe a printed document telling me a few weeks before the MOT or tax is due, i'm only really basic with ACCESS and im using the new 2007 version, the forms etc work fine and i have no problems with trying things out

maybe one nice person could point me in the right direction

thanks

Stu

View 2 Replies View Related

Help With Stock Database!

Sep 6, 2007

Hi,

I am creating a database which keeps control of stock. Each item is scanned in with a barcode scanner and given a unique stock number. The Manufacturer, System, Subsystem, Location and Classification is then added.

When a customer phones, and orders a product i.e. a camera, a customer order number can be added to the item in stock. The problem with this is that if the customer is ordering a number of items, and other items are not in stock yet, this item needs to be seen as unavailable. Due to large amounts of stock, i do not want to allocate the customer a specific item with stock number, but just allocate one of the many items of that sort. I do not want to allocate a specific item as when you go to collect the item from the stock room, every item of this type would have to be checked to find the specific stock number, which would be timely and difficult.

I tried using a make table query, which copies the information from the stock table, except the stock number, and so with this list, i will have a list of every item. I can then produce a table which shows me how many of each type of item is available. The problem is, this needs to update every time more stock is added, but i also need to be able to allocate a number of the items, and this becomes unavailable stock. At the moment, each time the query is run, the information is overwritten.

Please can someone help me if they can think of a better idea of how to do this! Im not that hot on programming, and so a in depth description of what to do would be great!

Emily

View 3 Replies View Related

Making Stock Database HELP!

Nov 2, 2005

I am looking to design a simple stock database for work. This is just an idea at the moment, unless I can get my head round this problem.

I have throught that I can have 3 tables

NEW STOCK(this is where you enter all new and existing stock is)

USED STOCK(this is where all the stock that has been sold used for whatever reason is entered)

ACTUAL STOCK(this is where the items in NEW STOCK have the items sold in USED STOCK deducted from them to give you the ACTUAL STOCK)

PLEASE HELP ME, am I completely on the wrong line or can I do this?
Would be good if I didnt have to use SQL, VBA etc...

View 2 Replies View Related

Barcoding Stock Control Database

Apr 28, 2005

Hi,

I am a beginner with making databases, so am looking for a bit of advice so I can know where to start.
What I'm trying to do is make a stock control database for our drugs dispensary as there are constantly drugs going in and out and they need to be able to keep track of where drugs have gone and what quantity levels they have.

I also have a 2D barcode scanner which scans and reads the drugs barcodes in the following format:

Product Code [Enter]
Product Descripition [Enter]
Batch Number [Enter]
Expiry Date [Enter]

So I would have thought the best thing would be to have a form with these fields in so the user could scan a barcode in filling in these fields. and that would then input the drugs into stock (Obviously there would be a quantity field too)

But where Im getting stuck is how could I then produce a form much like the one above with a search button. So the user could blip in the barcode filling in the relavant fields and then search for that product based on the products batch number?

Any Help would be MUCH appreciated.

Many Thanks

View 4 Replies View Related

Help Building A Stock Level Database

Jun 25, 2006

hey, is there anyone who wil be of help to me by creating me a quick and simple database. It needs to keep track how many spare parts I have. Therefore it needs to show how many of each spare part i have, and be able to update the stock levels with a submit request page that when the submit button is pressed the stock levels automatically update. the information required for each spare part is article (what the product is from), article number ( the unique article number of the product), colour (the colour which its from), spare part (what it actually is) and quantity.

Any more questions please ask

thanks

View 1 Replies View Related

The Dreaded Stock Control Database!

Dec 14, 2006

I have started making a stock control database, i have spent about a week on it, And i am struggling! There are obviously lots of different ways to actually build them efficiently. My problem is that i want to add stock and take stock out, changing the stock quantity and obviously keeping track of stock. I only want to ammend the stock quantity, in the stock table if the item is not a new item. The stock is added via a log sheet which could also have a new item. My head is beginning to hurt, but with a bit of help from you guys i might be able to crack it.

The Tables I have are:

tblStock

tblCategories

tblSuppliers

tblInventory transaction

tblStock_in

tblStock_Out

tblEmployees

Is this sufficient? Can i add stock and take stock out with one transaction table? Or would i need to add another table? I believe the code would be through a query but again am a little unsure as never done anything like this!

Please help!!

View 8 Replies View Related

Help Needed On A Stock Control Database

Mar 26, 2007

Hi

I am quite new to access and i can grasp things quite easily.

Basically i am make a stock control data base for a food company. I am making it so theres an order table and a stock table. Basically i want to know how to change the Quantity avalible in the stock table fom the orders table.

I dont know if i have explained that very well. But basically i have related the product name in the stock table to the product name in the order table. So i can add a number so that that the quantity of the product will change on the stock table.

If there are any easier ways of doing this rather than having 2 tables it would be useful

Ive tried all types of things and i cant get it to work. Any help would be very much appriceated.

Thank You

View 2 Replies View Related

Need Sample Database For Stock Transactions

Jan 28, 2008

Hi,

I'm in over my head with something I've been asked to do at work as my access skills are pretty basic.

I need a sample database containing PRODUCTS and SUPPLIERS, that lets you book IN stock received from the supplier (and adds to the stock level) and lets you book OUT stock issued to staff (and reduces the stock level)

I'll have a go at reorder levels, form design, stuff like that myself, but I'm having trouble with these very basic requirements.

Does anyone know of a sample database that I can download to learn some of this from?

View 2 Replies View Related

Product/Stock Access Database

Jun 28, 2005

I need a basic Product database that allows you to have a drop down menu to select a product from a table. In turn this puts the unit price in for you, you then put the quantity in and it calculates the total.

I also have a stock control. When one is bought it takes the quantity down. Very simple I know but I can�t get it to work�.

If there are any good basic database templates i could use let me know

I have attached a sample zip file

D

View 2 Replies View Related

EPOS/Stock Database Questions

Nov 30, 2007

Dear Reader

I am a realtivly new user and I do not know alot of things about Access

I have set up a database, and within this database, i have a table with my product details (the products are books, details include: ISBN, Title, Stock Level, and Price), I also have a table with customer's information (Name, Contact details, etc). I have created an order form, from which a user selects the customers name from a drop down list, and then he selects his name, and finally the date. Following this the user then works within a subform.

This subform has the following details, Order ID, Book ID, and Quantity Ordered. After this I want there to be a subtotal. I have no idea as to how to set this up, but I am assuming that I would need a query or something, to look up the price of the books, and then to multiply it by the quantity that the customer wants. Following this ideally I would like a small box on the main form saying the total value of the order.

Would their be anyway, to add the book ISBN number into the subform, where the user could either type in the ISBN number or select from a combo box the book title, but either way, both fields are updated, what ever method is used.

From this subform, I would like to be able to print out an order summary/invoice. This would contain all the books ordered by the customer in the current transaction, as well as the customer�s information, and the total of the order.

And my final question is probably the hardest one. Is their anyway (possibly by pressing a button) to automatically minimize the stock value on the products table, by the quantity chosen by the user in the order form? And if the quantity wanted is greater than the amount in stock, for a message to appear on the Order Summary/Invoice stating "On Order"

Using the above method, would it then be possible through the creation of a query to show what customers have ordered in the past, in a tab on the customer's details form.

Thanking you in advanced
CJM

View 11 Replies View Related

General - EPOS/Stock Database Questions

Nov 30, 2007

View 2 Replies View Related