Access :: The Dreaded Stock Control Database!

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

Without Any Success To Build A Small Stock Control Database.

Feb 15, 2008

Hi

I am trying without any success to build a small stock control database.

very simply put I am the main site and I have many other sites. each site has a unique code. I have 4 products only. I want to have the total stock that I hold in my site and as I send stock to other sites I want the number to decrease from my site and be added to the other specified site.

any ideas anyone.

View 3 Replies View Related

How Would I Do This? - Stock Control

Apr 18, 2006

Have a fairly simple scenario I hope someone can help me carry out.

I am focussing on two tables: Sales and Stock

Stock contains information on each product
and includes a 'currentStock' field which is decremented with each sale.

Sales records each sale with date and stock id.

I want one action to update both tables, what would be the best method?

Thanks

View 4 Replies View Related

Stock/Inventory Control - Help.

Jul 7, 2006

Hi guys,

Before you say anything, yes i have spent endless hours searching the forums for what im trying to find. The simple fact being that i can't find anything that fits my situation or i simply can't understand it.

Heres the problem -

I've been given the task, by a family member, to design and build a database. The database is for his carpet shop/warehouse. Being of such a business the warehouse always has stock coming in and going out. The manager can't keep track quikcly enough.

He therfore needs -

- A database of products in stock
- A history of old stock and who it was sold to (which customer/invoice)
- An easy, user freindly, way of entering stock as quickly as possible as it comes in
- A form for orders, which, in the top half the customer information is entered and in the bottom half a products/stock search by the stock ID/number to find the products they are buying. Also a subtotal/total box.
- The stock needs to be deducted from the stock/products list when it is bought and goes into a history database/table.
- Reports based on Orders, with only relevant information, for customer invoices.
- A stock search facility where all stock, present and past, can be searched to see what is most popular according to manafacturer, colour, quality etc.

It is not a shop/EPOS application that i am trying to create. Its simply a stock control system which, as a bi-product, produces invoices and reports etc. It goes without saying that, as staff members don;t have extensive computer knowledge, the whole database must very user freindly and simple and clear etc.

Now MY problem -

I can produce simple databases and can set up straight forward queries/reports etc. But the whole stock update thing really confuses me.

- i dont know what the set-up/design of the database should be
- i dont know how to do the stock update thingy
- i dont know how to create a search facility for products being purchased
- i dont know how to move the stock from the stock table into a history table with the customer/invoice information
- i dont know, qithout creating 10 different queries, how to make a stock search facility which the user can determine the criteria for searching
- i dont know how to make the whole thing look simple and user friendly!

What im looing for guys is for anyone to point me in the right direction. i Might sound lazy just asking for people to help a bit but i have spent weeks trying different things out, using examples, reading books, EVERYTHING. It all seems complicated until someone explains it to me. I dont mind doing it all myself but i dont know where or how to start. Can anyone suggest a suitable design/set-up? Can anyone provide a sample/example i could look at that is very similar? Can anyone help in any ways what so ever. I thought i was fairly good with computers but this proves that when it comes to advanced access im just a noob. if anyone can help a guy in need what so ever please do.

you all sounded really helpfull in other posts and i hope you can do the same for me. please remeber im a bit of a novice so be gentle! Thanks in advance for any help that you can provide me with.

All the best, James :)

View 7 Replies View Related

Stock Control Project

Jul 30, 2005

hello,
i have 2 problems,

1 how the stock control will work? I found similar posts saying to calculate the items sold and compare them with items in stock. When there is restocking the items in stock field will be increased?

2 i want to store the total amount paid from a customer in each order. I searched the posts for this case and the answer always says you shouldn't store calculated values but calculate them each time. In my case the prices for a product might change and user wants to know the total amount received from a customer for different purposes i.e discount, promotion e.t.c

View 5 Replies View Related

Stock Control Conundrum

Dec 8, 2005

Hi people,

I have an existing stock control database that has evolved over the years from the "Inventory Control" sample supplied with A97.

We (until now) supplied spares from one manufacturer. The spares items list was provided to us in an excel sheet and easy to append to a Access table.

We now want to supply spares for two new manufactures who also supply spares list in an excel sheet.

The problem:
All 3 manufactures excel sheets are very different in layout format of number etc. and approximately 30,000 lines in total.

The question(s):

> Do I spend ages messing around copy/paste into one "super excel sheet" then append to access or some how have 3 tables (one per manuf.) suppying to one form?

>Can access handle 30.000 records in one table? Maybe a silly Q. I presume very slow on search?

Any suggestions are very welcome.

View 2 Replies View Related

Stock Control Query

Mar 8, 2006

Hi

I am building a stock control database, which uses a barcode reader to input data into the fields.. (stored in a product table)

For a sale the item is scanned.. and the details are displayed. However is there any way which the same form can be used to have multiple text boxes with the same names..

So in essence I need to be able to view more than one sold item on one form.. is it possible to have rows of different transactions??

Hope that makes sense.. any assistance would be great!!

Sunshine!!

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

Automated Field For Stock Control, Need Help, Please

Jun 22, 2005

Hello, I have been learning how to use Ms Access in order to design my own Database, I have done quiet a bit but i found myself kind of stuck witth the following issue. My question is pretty simple (I think�) Please, can someone help me? :o

I need a field that update itself automatically for each record.
For example, if I have 2 initial fields called [Stock balance] and [Quantity delivered]; I need to find a way to have the [Stock balance] updating itself just by entering a value for [Quantity delivered] where [Stock balance (actual record)] = [Stock balance (previous record)] � [Quantity delivered]

I could create a third field Called [Quantity left]
with [Quantity left (actual record)] = [Stock balance (previous record)]
and [Stock balance] = [Quantity left]-[Quantity delivered]

but it doesn�t seem right to create a third field that is equal to an other field just with one record difference.

Anyone with an advice?

View 4 Replies View Related

Stock/Inventory Control - Help :confused:

Jul 7, 2006

Hi guys,

Before you say anything, yes i have spent endless hours searching the forums for what im trying to find. The simple fact being that i can't find anything that fits my situation or i simply can't understand it.

Heres the problem -

I've been given the task, by a family member, to design and build a database. The database is for his carpet shop/warehouse. Being of such a business the warehouse always has stock coming in and going out. The manager can't keep track quikcly enough.

He therfore needs -

- A database of products in stock
- A history of old stock and who it was sold to (which customer/invoice)
- An easy, user freindly, way of entering stock as quickly as possible as it comes in
- A form for orders, which, in the top half the customer information is entered and in the bottom half a products/stock search by the stock ID/number to find the products they are buying. Also a subtotal/total box.
- The stock needs to be deducted from the stock/products list when it is bought and goes into a history database/table.
- Reports based on Orders, with only relevant information, for customer invoices.
- A stock search facility where all stock, present and past, can be searched to see what is most popular according to manafacturer, colour, quality etc.

It is not a shop/EPOS application that i am trying to create. Its simply a stock control system which, as a bi-product, produces invoices and reports etc. It goes without saying that, as staff members don;t have extensive computer knowledge, the whole database must very user freindly and simple and clear etc.

Now MY problem -

I can produce simple databases and can set up straight forward queries/reports etc. But the whole stock update thing really confuses me.

- i dont know what the set-up/design of the database should be
- i dont know how to do the stock update thingy
- i dont know how to create a search facility for products being purchased
- i dont know how to move the stock from the stock table into a history table with the customer/invoice information
- i dont know, qithout creating 10 different queries, how to make a stock search facility which the user can determine the criteria for searching
- i dont know how to make the whole thing look simple and user friendly!

What im looing for guys is for anyone to point me in the right direction. i Might sound lazy just asking for people to help a bit but i have spent weeks trying different things out, using examples, reading books, EVERYTHING. It all seems complicated until someone explains it to me. I dont mind doing it all myself but i dont know where or how to start. Can anyone suggest a suitable design/set-up? Can anyone provide a sample/example i could look at that is very similar? Can anyone help in any ways what so ever. I thought i was fairly good with computers but this proves that when it comes to advanced access im just a noob. if anyone can help a guy in need what so ever please do.

you all sounded really helpfull in other posts and i hope you can do the same for me. please remeber im a bit of a novice so be gentle! Thanks in advance for any help that you can provide me with.

All the best, James :)

View 5 Replies View Related

Stock Control - Table Structure

Sep 4, 2007

First of all, please let me apologise as I am something of a novice when it comes to Access and the answer to this query will no doubt be 'obvious' to most of you!:o

I am trying to create a Stock Control database to track stock bought, sold and held. The product is mainly shoes. I am not sure of the best way to structure the tables. The fields required are:
Item Code - this is a unique reference
Item Description - this could be duplicated e.g. there could be two item codes relating to 'Summer sandals'
Wholesale Price - this could be duplicated e.g. there could be two item codes at the same price
Colour - Each item code will have a limited colour range
Size - Each item code will have a limited colour range

Basically I want to create a 'Stock Purchased' form to enter the stock bought on any one purchase date. I wanted to do this in such a way that if the item code was selected from a combo box, the item description and wholesale price would be automatically populated, but the colour and size would be selected from a combo box (limited to the particular choices for the item code).

So, initially my question is: Do I need to create a record for every possible combination of shoe style, colour and size (this could result in hundreds of possible combinations), or is it possible to have a single list of colours and sizes and somehow link these to the item code?

I've been looking for an example of a Stock Control database but haven't seen one. No doubt I'm going about designing this database in completely the wrong way so any suggestions really would be appreciated.

Thanks for your help.

View 4 Replies View Related

Stock Control Update Query

Nov 21, 2007

basically, im making a stock control database

i've made an update query but its not exactly what i wanted
is there a way to make the criteria equal a particular form field because i dont want the user to type in the stock ref in a parameter query.

also, how do i make it so no orders can be made on stock which has stock levels of 0 and have a message saying it is out of stock
at the moment, i put >0 in the criteria underneath no in stock field in the update query

i hope i made it clear because im not sure how to explain it

thanks for any help
the jayman

View 4 Replies View Related

Order Entry / Stock Control Template Db

Jan 5, 2006

I have to provide a Quote/Order/Invoice and stock control system very quickly for one of our satellite offices, and wondered if anyone knew of any "boilerplate" databases that have done all the grunt work, so that I can just edit reports and forms etc to get them up and running. We are happy to pay a (modest) fee for this service, but would expect to get full access to the design/code for later development and customisation if required.

I've tried Googling but get page after page of rubbish !! Any pointers would be gratefully recieved.

Thanks and Regards

Richard Keith

View 3 Replies View Related

Storing Live Data? (Stock Control Help)

Feb 18, 2008

Hi guys,I'm new to Access except using it years back but much has changed from the 2000 version to the 2007 version - it's slowly coming back to me though, as it's mostly just an interface change. Although I'm highly experienced in SQL (mySQL) and database design / structure from being a Web Programmer.I'm wanting to store a stock amount for products. Using 3 tables, for products, orders and sales. Here's a real basic layout:productsprodID | productName | productStockordersorderID | supplier | prodID | quantityBoughtsalessaleID | customer | prodID | quantitySoldThe products table is just a list of the products. Sales is where I log what I sell to customers and the orders are what I buy from my suppliers.What I'm wishing to do is use the products table to store the stock of that item so as soon as I make a sale, that quantity for that prodID is subtracted instantly from the productStock and when I make an order it's instantly added.Is this possible, or is there a good way to do this?All help appreciated! :)Thanks,Nick.

View 2 Replies View Related

Running A Update Stock Control Query From A Button

May 20, 2005

Hi

I am a relatively new user to access and wondered if you could help me with a stock control problem.

I have created an update query (qryUpdateQuantityinStock) with the below fields:

QuantityinStock
tblProduct
Update: [QuantityinStock]-Forms!frmCustomerOrder![frmProductOrderLine Subform].Form!Quantity

ProductNumber
tblPrdocut
Criteria: [Forms]![frmCustomerOrder]![frmProductOrderLine Subform].[Form]![ProductNumber]

When the user enters the quantity they require into the orders form I would like to deduct it from the quantityinstock field in the products table.

The above query does work if you add the following code to the beforeupdate event of the orders subform:
If Me.NewRecord Then
DoCmd.SetWarnings False
DoCmd.openquery "qryupdateQuantityinStock"
DoCmd. Setwarnings True
End If

Although I wanted the query to run from a button (confirm order) on the order form so that the user decides when to deduct the stock.

I cannot put a button onto the orders subfrom where the query should run from and if I place the code on a button on the main orders form it only deducts one order (one product) and not all products on the order.

Can you help me find a way to run the query from a button?

I have read through the threads and found lots of information on stock control, although have not yet found anything similar to this problem. I understand that this is not the correct way to do stock control, although my database is for academic purposes and only requires a simple stock control system.

Any help would be greatly appreciated (sorry for the long post!). :)

Many thanks

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

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

The Ever Dreaded Many To Many

Sep 21, 2007

I can't quite get my head wrapped around this, so any help will be appreciated. Here's my set up.

Every table has an AutoNumber Unique ID to make things simple.
This is about insurance information for a company that owns a lot of property.
There are other tables in my database that I am not mentioning because they do not affect my many to many drama. So if it looks incomplete, it (hopefully) isn't and I just didn't mention it.

Tables are:

tblBuilding
BuildingID
BuildingName

tblPolicy
PolicyID
EffectiveDate
ExpirationDate

tblPolicyCoverage
PolicyCoverageID
CoverageType
PolicyID

tblCoverageInfo
CoverageInfoID
PolicyCoverageID

True Statements about this information:

A "Policy" has many "Policy Coverages"
A "Policy Coverage" has many "Coverage Info"
A "Building" has many "Policy"
A "Policy" has many "Building"

So my Building/Policy relationship is the many to many relationship. So I want to create a middle table which will have a New ID, and the Building ID and the Policy ID. I think.

Here is my confusion. Do I create a middle table to connect the building to the policy? Or do I connect the building to the policy coverages? or do I connect the building to the Coverage Info? Because it's true that a building has many policies, many coverages and many coverage info. I'm thinking I put it at the policy level but I'm not 100% on that.

I fear I won't get this answer without hours of trial and error and I don't have hours being this is just a side thing for one client because they own like 200 properties. If you need more info, let me know. Any suggestions, much appreciated. Thank you.

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

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