Unanswered: Need Help from the db Wizzez, im an amateur!
ok guys let me crack it down, i have a small business of fresh produce trade that me and my two brothers have jus set up, we have a low budget, so i volunteered to be the computer programmer here! anyway i hafto come up with a stock system which basically means, i need to collect what comes in in the mornin, whats sold, and then tally it all at the end of the day. The deal is that, i got four main categories, date, supplier, product and quantity, when i do the sales, i need to knock the total off the quantity matching the same supplier product and date. The main thing is that, i need to use the same quantities for each product in the morning at a new date as the ones at the end of the previous day. Help, do u guys have any good tips or sites to help me
PPPPPPPPPPPLLLLLLLLLLLLLLLLEEEEEEEEEEEEAAAAAAAAAAA ASSSSSSSSSSSEEEEE Im in a pickle
The deal is that, i got four main categories, date, supplier, product and quantity, when i do the sales, i need to knock the total off the quantity matching the same supplier product and date. The main thing is that, i need to use the same quantities for each product in the morning at a new date as the ones at the end of the previous day. Help, do u guys have any good tips or sites to help me
You should have a tables for:
Supplier, Products, Product Category, and Inventory
Here's some tables to consider. Inventory Table: will be the main table for order transactions, It will have the date of order, price paid, vendor code, shelf life expreation... info for each item purchased.
Supplier: Details of each vendor, VendorID, Name, Phone, Contact, Address, notes...
Products: Lists Each product and Details of product
Product/Vendor: linking table from Products to Vendor used to show Multi-vendor list for product sources.
Product Min/Max: Table by product code of min/max inventory level.
Category: Product Categories, essentially this will be values used in a drop-down list of your form, additionally it will have a related field on the products list to drive the product by category listing. For list values that are not too lengthy I typically create a lookup list table with listcategroy, listitem, itemdescription, and order fields. then query this table in the listbox object at the form level.
You may check out www.databaseanswers.org for sample data models. Check out categories 45. Inventory Control, and 85. Stock Control. (I'm having some trouble accessing these now, it may work later)
thanx bill, but still, its a fresh produce trade, the theory is quite simple, all i need to include is Date, Supplier, Product, Units on Hold, Units sold (this will be different transactions but it must change the units on hold field for each of the products by a simple calculation HOW!) the units on hold at the end of each day will be the same as the start of the next morning, to save time and re entering of data, is there anyway to copy the data from the previous date???? HHHHHHHHHHHHHHHELLLLLP arent there any templates i could work with
all i need to include is Date, Supplier, Product, Units on Hold, Units sold (this will be different transactions but it must change the units on hold field for each of the products by a simple calculation HOW!)
Simple, though I think you would still want a supplier Table. So as you record POs you can select a Supplier from a list and avoid typos in your data. What happens if you get say 500 oranges from supplier A. and 250 oranges from supplier B. Are supplier B. oranges going to be recorded as a separate product?
I guess for the simplist DB have a table called products, with fields for:
Create a form to display this information, and add new records. On the form have an unbound field for 'quantity_sold' and a command button to update the hold value in the table.
Example of code to subtract sold number from current hold quantity.
Private Sub cmdUpdateHold_Click()
' set a variable with the current qnty from the qntyhold field on the form
intCurHold = Me.qntyhold
' check the unbound SoldUnits text field for a numeric value, this code would be activated from a click event of a button on your form.
If Not IsNumeric(Me.txtSoldUnits) Then
MsgBox "You must enter a Number to proceed"
' set a variable with the sold units entered by the user
intSold = Int(Trim(Me.txtSoldUnits))
' Check if sold units Exceed hold units
If intSold > intCurHold Then
MsgBox "Sold Units Entry Exceeds the current units on hold. Hold quantity will be set to '0'"
intSold = intCurHold
' subtract the sold unit number from the current hold units
Me.qntyhold = intCurHold - intSold
' reset sold units field to Null
Me.txtSoldUnits = Null
' Apply the changes to your database table
Me.Dirty = False
first you'll create the table with the various fields needed. Then you'll base a form on this table. The datasource of the form will refer to your table or a query based on the table. (you can use the 'auto form' wizard to create a form directly from the table. With the form in Design mode you should have a Floating toolbar named Toolbox (If not go to the tools-customize menu or right click on the tool bar area and select Toolbox from the list to display it) find object on this toolbar, click it then click on the form design grid where you want the button placed. Once the button object is on the form you'll want to rename it to something more discriptive (I named it 'cmdUpdateHold') then go to the events tab and assign an action to the onclick event.
cheers bill, another thing, when i enter product name and date and product category into the sales form, how can i find the corresponding record in the received goods table and edit the quantity on hold for that record?
Several ways of doing this, typically you are going to have related fields between your tables. For example you might have a Product table to list products with a unique ProdID for each product. In your sales table you will have a field for 'Product ID' where you would enter the ProdID related to the Product in the Product table. This would be a Primary Key of ProdID in the Products table and a Foriegn Key in the Sales Table. With the realted fields you can identify the record(s) to update and use built in database structures to query and update data.
There are many ways to actually update the fields in related table. Here's a few.
Have Products on a main form and Sales Items on a bound Subform. The main form will always be in sync with the subform on the same form. You just update the quantity field showing on the main form.
Use ADO, DAO record set and VBA to update the correct record based on the primary key matching.
Create an Update Query with a Parameter based on the active sales form, then run that to update the hold quantity.
This is a basic concept of Access and Relational Databases. You may want to read some Access books and search some on-line resources on these topics to gain a better understanding.
hi, i have created an updater query, everything was working fine, but 3 problems. the first is that when i run it, i only need to run it once, but when i add a new sales record to the form, it does it all over again therefore giving values like -12 in the quantity on hold field. The second prob is that when i relate the lot ID field in the query, it says type mismatch, one of the tables has this field in bold. lastly is there anyway to stop the prompt that tells you how many records you are about to update?
I have the Microsoft Access Developers Handbook; Getz, Letwin, Reddick I'd say the this is one of if not the best publications for advanced access programming. It is not necessarily the best for an introductory book. Any decent access book will start out with a chapter on RDMS concepts, Data Normalization, and general database basics. You will not likely learn these concepts by trial and error of hacking away or getting them piece mail from any forum. If you can learn and understand the basics you will be miles ahead with any database project. Make a trip to your local bookstore and leaf through a few access books, then pick one up so you can keep it and markup points of interest.
hey, thanks for the recommendations, they are helpful, i am currently using a book called access 2003 in easy steps by mark lewin. it is for beginners, it helped me alot when it came to updating queries though, i will get hold of the book that you are recommending, im still having that little prob with the update query as i have stated in my previous post, any suggestions??? I only want to update each record once, is there any limit available?