Results 1 to 4 of 4
  1. #1
    bcarter17 is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jun 2021
    Location
    Richmond MO
    Posts
    35

    Updating Price

    Could use some help with this one. I have an application for our Rock Quarry that creates and print tickets of loads going out. If they are not on account, we have the ticket pull in the board price of the type of rock and then multiply times the tons on the load.



    If they are on account, we leave the price field blank on the ticket and show zero dollar amount. This is due to some customers getting special pricing based on volume (on some types, not necessarily on others), etc. So right now, our office manager can pull an Excel export from the application for the previous days load tickets, but all "Account" customers will show a charge of zero. She manually goes in and inserts the special price into the spreadsheet 1 ticket at a time (sometimes 80 or 90 tickets a day).

    I am looking into maintaining a table of all our account customers as a record, with fields for all types of rock we sell as fields in the record (Currently about 30 different types). If a customer is getting a special price different than the board price, she could go in and enter the special price for that item or items. If there is no special price, the field would be left blank. (See example)

    My question is, how to update the Sales table "UnitPrice" field that shows $0.00, with a special price from the "SpecialPrice" table. Example, if the customer was "Bob Carter Construction", and they purchased a rock type of "1" Clean", and in the "SpecialPrice" table, Bob Carter Construction in the 1" Clean field shows a special price of $12.00, I want to update that record to $12.00. But if Bob Carter Construction also purchases 2" Clean rock, and they have a special price of $11.00, then update that sales ticket record to $11.00.

    Click image for larger version. 

Name:	Bob1.JPG 
Views:	17 
Size:	54.4 KB 
ID:	46804Click image for larger version. 

Name:	Bob2.JPG 
Views:	17 
Size:	65.0 KB 
ID:	46805Click image for larger version. 

Name:	Bob3.jpg 
Views:	18 
Size:	197.3 KB 
ID:	46806Click image for larger version. 

Name:	Bob4.jpg 
Views:	18 
Size:	95.3 KB 
ID:	46807

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    SpecialPrice table is not normalized data structure so can't just link tables in query.

    Run UPDATE action SQL using DLookup(). Something like:
    Code:
    UPDATE Sales SET UnitPrice = DLookup("[" & [Product] & "]", "SpecialPrice", "Customer='" & [SoldTo] & "'") WHERE UnitPrice = 0
    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
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    You should have a 'Products' table with a row for each product you sell, this table at a minimum could only need 3 or so fields. The special price table should probably be a junction table linking a customer table and products table together in a many-to-many relationship.

    See below for a few more comments.

    Click image for larger version. 

Name:	Untitled.png 
Views:	14 
Size:	175.8 KB 
ID:	46808

    [edit]
    Quick and dirty and very incomplete example of what I mean:
    Click image for larger version. 

Name:	Untitled.png 
Views:	13 
Size:	16.1 KB 
ID:	46809

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

Similar Threads

  1. Query to pull price from master price
    By jonathanoeijoeng in forum Queries
    Replies: 3
    Last Post: 03-21-2019, 02:18 PM
  2. Order Entry Form - Case Price vs. Unit Price
    By Kaloyanides in forum Access
    Replies: 4
    Last Post: 05-18-2017, 06:31 AM
  3. Replies: 17
    Last Post: 12-14-2015, 10:23 PM
  4. Item without price, or duplicate price
    By Auto in forum Reports
    Replies: 5
    Last Post: 07-29-2013, 09:46 PM
  5. Replies: 1
    Last Post: 11-08-2011, 07:44 PM

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