Results 1 to 9 of 9
  1. #1
    PinkDuster's Avatar
    PinkDuster is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2017
    Location
    Cleveland area
    Posts
    65

    Need to include ad hoc products, service in sales and inventory database


    I'm working on a sales and inventory database for a small business, and have tblProducts, tblCustomers, tblOrders, and tblOrderDetails connected and functional with production of sales reports and invoice forms. My boss would like to incorporate the ability to create orders for ad hoc products or services, and "on-the-fly" good customer discounts into this Access 2013 database. Not sure if I need additional tables, or to eliminate the referential integrity on the existing tables. Any advice appreciated!

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You could have a NotInList event for the product and add it to the products table, allowing them to enter the product description and updating the products table. Referential integrity is a good thing and it is best to keep in intact. You can add the discount to the Orders table, with maybe a rating on the Customers for good/bad customers.

  3. #3
    PinkDuster's Avatar
    PinkDuster is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2017
    Location
    Cleveland area
    Posts
    65
    Thank you for the suggestion! That would be ok to do with the addition of nonstandard products or services, but not for adhoc (market) pricing. There isn’t a standard percentage discount to apply. I was hoping to find a way to automatically populate the order details, overwrite any products or prices, and store that in the orders table without changing the products table.

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I don't understand what you are saying! Overwrite products already entered?

    Let's separate the two issues - valid product info and price info. For products, you won't have to change the table, just add new records. For pricing, where does the pricing come from currently? And why can't you add a discount to the Orders table?

  5. #5
    PinkDuster's Avatar
    PinkDuster is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2017
    Location
    Cleveland area
    Posts
    65
    Yes, I will just add new records to products table for the ad hoc products. Current tblProducts contains pkID, P/N, Description, Qty on hand, and Retail Price. I would need the ability to modify a price on an order at any time for a given customer but not impact the price in tblProducts. Could be a one-time discount, trying to move a particular product, etc. I hope this is making sense.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Most of us store the price an item is sold at in the order details table. That would also be the spot for your discount. In other words, when they select a product, this field would populate with the retail price from the products table. The user could override it if desired.

    That also protects you from price changes. If you rely on the price in the products table, historical data wouldn't compute correctly.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    When you add a new product you will need to add the description and price as well. Such as setting a flag to denote "this is a new product" and run an update query in the AfterUpdate event of the form. Or else check the products table and if the description is blank then update it to what the user has entered.

    Is the discount tied to the product or to the order? That would determine where you stored the discount value, on the order header or the order details.

  8. #8
    PinkDuster's Avatar
    PinkDuster is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2017
    Location
    Cleveland area
    Posts
    65
    Thank you for the advice, I do not have my order details table set up that way. I will rework the table to populate from the products table, but store as "PriceSold" in tblOrderDetails.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 6
    Last Post: 07-29-2016, 11:01 AM
  2. Sales and Inventory DB
    By Dig in forum Access
    Replies: 8
    Last Post: 11-17-2014, 12:49 PM
  3. Products Inventory Dilema,Add To Inventory
    By burrina in forum Forms
    Replies: 3
    Last Post: 12-02-2012, 12:10 PM
  4. Replies: 8
    Last Post: 04-24-2012, 01:05 PM
  5. Replies: 1
    Last Post: 08-11-2011, 01:52 PM

Tags for this Thread

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