Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Dec 2004
    Location
    new york
    Posts
    28

    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

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    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)

    /
    ~

    Bill

  3. #3
    Join Date
    Dec 2004
    Location
    new york
    Posts
    28

    Still need help

    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

  4. #4
    Join Date
    Feb 2004
    Posts
    533
    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:
    product_name
    supplier
    quantity_Purchased
    date_purchased
    quantity_onhold

    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.
    Code:
    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"
       Exit Sub
    End If
    
    ' 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
    End If
    
    ' 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
    
    End Sub
    There you go, can't get much simpler


    /
    ~

    Bill

  5. #5
    Join Date
    Dec 2004
    Location
    new york
    Posts
    28

    Thanks

    Cheers Bill, Ur a Star, finally, how i get the command updater button? The oranges are going to be tallied and recorder as a single product

  6. #6
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by bobby911
    how i get the command updater button?
    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.

    Check out this web page, Creating Forms in Microsoft Access for a quickie on creating forms.


    /
    ~

    Bill

  7. #7
    Join Date
    Dec 2004
    Location
    new york
    Posts
    28
    i know about the whole form creating and basing stuff, but what function do i give the button in the build command

  8. #8
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by bobby911
    what function do i give the button in the build command
    The way I did the example was in the Forms Module. So if you bring up 'Properties' for the command button, go to the Events Tab, select the 'onclick' item, click '...', select 'code'.

    This will create a new sub procedure in the Forms module. The procedure will have the same name as the button object in your form something_click()

    Then if you copy the sample code from the previous post into the Process and make sure all the refereneced fields matched your form it should work as advertised.


    /
    ~

    Bill

  9. #9
    Join Date
    Dec 2004
    Location
    new york
    Posts
    28
    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?

  10. #10
    Join Date
    Feb 2004
    Posts
    533
    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.

    1. 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.
    2. Use ADO, DAO record set and VBA to update the correct record based on the primary key matching.
    3. 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.


    /
    ~

    Bill

  11. #11
    Join Date
    Dec 2004
    Location
    new york
    Posts
    28
    thanks, u have helped me alot, i have put many more posts out but nobody seems to reply. I will keep in touch, any sites u could recommend?

  12. #12
    Join Date
    Dec 2004
    Location
    new york
    Posts
    28
    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?
    cheers,
    bob

  13. #13
    Join Date
    Feb 2004
    Posts
    533
    I will keep in touch, any sites u could recommend?

    I was just looking at a post here yesterday on exactly this topic, Want to learn access There's some good links here, check it out.

    One of the best sites for answers and code snippits is, The Access Web
    http://www.mvps.org/access

    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.


    \
    ~

    Bill

  14. #14
    Join Date
    Dec 2004
    Location
    new york
    Posts
    28
    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?
    Cheers,
    Bob.
    Last edited by bobby911; 12-31-04 at 12:40.

  15. #15
    Join Date
    Dec 2004
    Location
    new york
    Posts
    28
    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

    Hey bill, u can check out my previous posts for more info, but is there anyway i could enter units sold directly into the sales subform and it would keep subtracting it from the hold quantity?
    Last edited by bobby911; 01-01-05 at 19:03.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •