Results 1 to 9 of 9
  1. #1
    pendlet is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    7

    How to keep track of products purchased and products sold with on products table?


    I am running a small snack fund at work and i want to keep track of the money spent and received and also the products sold and bought. I buy most of my stuff from bulk stores like Sam's Club and Costco but also hit up the sales at the local grocery stores. I have the basic design layout completed but i am at a loss of how to track products that i buy that have more then one item in them. I buy boxes of candy bars in bulk that will have 6 different candy bars or a box of pastries that has various different kinds. When i add them to my list of purchases i need to be able to separate them into there single item form for resale. I want to track each item separately on the resale side for pricing and keeping track of what sells and what doesn't. So my question is do i have to have to have two different products tables one for purchased and one for resale and how would the relate to each other or is there a way to separate the items out automatically. For example I buy a variety box of candy bars that has 10 snickers, 6 milky ways, 6 three musketeers and 8 twixs. In the purchase details it would be listed as candy bar variety pack but for the resale side that does me no good. Here is my current relationships table with just one products table. Any help would be appreciated. My goal is to be able to enter each item as it appears on the sales receipt from the store and have the qty of products for resale update appropriately for each individual item.
    Click image for larger version. 

Name:	Relationship table.PNG 
Views:	37 
Size:	30.3 KB 
ID:	7068
    Not sure if i need more tables some type of coding or what?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You have a Products table that has field for Resale price. Is this pricing for each individual candy bar from the bulk pack?

    You have a Purchases table that links to the Products table so product details are available. Now need a Sales table on the other side so details of product sold can be available to the sales records. Assume all transactions are cash based. How are sales conducted - personally through you, vending machine, honor system deposit in a tin can (that's what we have, everything is one price but it is just a small selection sitting on top of a cabinet)?

    Think I would record a transaction for total purchase (no product detail) then break up the packages and input the count of like items into an inventory transaction table.

    Sounds like a lot of work for a 'small' snack fund.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    pendlet is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    7
    I am doing this more for me to learn access then it is to keep track of the snack fund but i figured it would be useful so why not. Yes resale price is for each candy bar or soda etc.. It is just a counter with snacks and fridge full of soda and water but we collect any where from $300 to $400 a week in sales and i have about $500 roughly in product so its not your typical snack fund. The main reason i want to track individual items is to see what items make the most profit. Our profit margin is slim and i actually sell a few items at a loss hoping that they buy water and soda to go along with that purchase because that's where we make our money. I do what you are talking about know with just manually entering the individual candy bars in to the inventory but i want a way to do it automatically. I've got and idea i am working on now that might work. I will post back with my updated tables.

  4. #4
    pendlet is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    7
    Here is an update. I have done allot to this since the last time I posted but I still have the same problem. When I select a product in my purchase details page I need to have it check to see if there is a check in the yes/no box of the underlying products table named MixedItems. If this box is selected ( = True) I need the MixedItems to be added to my purchase details table. I believe what I need is an, if then else statement in my after update field of my combo box. It would basically be like this.

    If product “Mars Varity Pack” MixedItems = True then add MixedItem1 “ProductID 135” QTY=10, MixedItem2 “ProductID 265” QTY= 6 to my purchase details table else do nothing.
    ProductID 135 is Snickers and productID 265 is Twix so it would basically add two more line items to my purchaseorderdetails table with all the info of that product plus the correct quantity.
    I understand if then else statements but I don’t know how to write them for access, Or how to have it add it to the right record in my table. My products table lists all the products that I can purchase and if it’s a bulk item that includes multiple separate items for resale then it has a check box checked and all the items included are listed in separate fields called MultiItem1, MultiItem2 etc.. I want the code to basicly add all the MultiItems listed to the purchase details table that I am currently work on. Can someone show me how I would do this and how to code it? I don’t want to have to know that a product is a multi-product item or what’s in it, but once I select it from the combobox it just adds the individual items automatically. Here is an updated view of my relationship table.

    Here is an updated relationship table. Click image for larger version. 

Name:	Relationship table2.PNG 
Views:	25 
Size:	45.7 KB 
ID:	7347

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If Then Else syntax is for VBA code (macros do something similar).

    Expressions in ControlSource would use IIf: In-line If.

    Syntax is:
    IIf(<condition statement>, <use this value>, <else use this value>)

    Example:
    IIf(Cost < 100, .15, .10)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    pendlet is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    7
    So if i wanted the combobox to check this each time an item was sleceted should i put the if stament in the controlesource of the combobox? what about the after update field?

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    An expression in ControlSource cannot set value of another control.

    I am not entirely clear on what you want to do. It seems you want to change an aggregate value and that calls for code (I use only VBA).

    If you want something to happen after entry into combobox, use the AfterUpdate event and put If Then Else code in VBA procedure.

    If you want a textbox to calculate based on value in another control can use IIf expression in ControlSource.
    Another example of this would be: =Cost + Cost * IIf(Cost<100, .15, .10)
    ControlSource calculated values will not automatically save to table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    pendlet is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    7
    I think what i need is VBA in the afterupdate event.

    When i am adding items purchased to my purchase details table through a form, I have a combo box that i can pick items from. When i pick an item from that list i want access to check and see if a box has been checked in the field called mixeditem. MixedItem is a yes/no field in my products table. If that field shows true "checked" then I want access to add the aditional items to the purchase details table based on what is in the products table filed MixedItem1. Can you give me an example of some VBA that would check to see if a field is true or false?

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Is the field MixedItem included in the form's RecordSource?
    This what you are looking for?

    If Me.MixedItem = True Then
    'do this
    Else
    'do this
    End If
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. update team against 2nd level products
    By Lata in forum Queries
    Replies: 2
    Last Post: 09-20-2011, 03:35 AM
  2. Multiple products in an order
    By andeekaii in forum Access
    Replies: 3
    Last Post: 05-31-2011, 07:36 AM
  3. products and quantity
    By woody in forum Forms
    Replies: 2
    Last Post: 02-06-2011, 11:58 PM
  4. Orders & Products
    By mastromb in forum Database Design
    Replies: 4
    Last Post: 01-22-2010, 07:59 AM
  5. In need of assistance-products mated to mobile#'s
    By EisBlade in forum Database Design
    Replies: 0
    Last Post: 04-06-2006, 07:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums