Results 1 to 7 of 7
  1. #1
    sinisterfrog is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    23

    How to Reduce the Chance of Incorrect Data Entry?

    So quick backstory.

    I'm writing a new database for my company. My company routinely does work for university students/professors. We do a net 30 invoice (so payment is never upfront).

    The problem is when we receive order information from our customers (shipping/billing), they often dont know who is paying for our services so they often put themselves or their professor or hell, snoopy as their billing person.



    When data is entered into the database we assume the customer is right (even though there is a significant chance they are wrong) and so we input the incorrect billing information and then sort it out when we attempt to collect~this often adds days/weeks/months to the collections process.

    Over the years on the existing database, we have tried to curate our information~querying by university and making global changes on specific university bill to's. So currently we rely on "experience" to correct incorrect customer information. The problem is we have multiple data entry folk (myself included) and not everyone knows that Cheeseburgler is the bill to for McDonalds university and so the incorrect "bill to" will be entered.


    I've gone down the path of making an "address Table" in which all address information is stored, but that quickly became silly as many of the same fields existed in the "customer table".

    Any suggestions for databasing around this?

    Thank you

    -Matt

    **Update 8:44 5/30/18
    Would it be possible to make an alert of sorts that when specific words are entered into specific fields an alert pops up? ~ When UCSB is entered into a field a little pop up shows up that says the bill to is Donald Trump?
    Last edited by sinisterfrog; 05-30-2018 at 09:46 PM. Reason: Added more information

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You accept an order (verbal?) from a student/professor who says 'bill the university'? And you take their word for it? Really? Maybe require purchase orders from your customers that are signed by some contracting authority? Set up some sort of blanket agreement with university that designates authorized signers? Those students and professors should be making purchase requests through their contracting office. I worked for government and university and could never have just on my own word commit the organizations to a financial obligation.
    Last edited by June7; 05-31-2018 at 05:12 AM.
    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
    sinisterfrog is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    23
    Written orders. Different universities have different payment requirements. Certain colleges set up PO's others do not. Certain colleges have designated accounts payable people, others have research grants that pay out.... Universities are the worst. Each department does accounting on different schedules...I could whine for hours about universities...

    We work with people's research.
    The issue isnt that we dont get paid, but rather, It takes us too long to figure out who is paying for the services.

  4. #4
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    No PO , No GO. Or give us a Credit Card number.
    That's how we deal with all customers and we deal with Universities, Garages, Computer Manufacturers, NHS Trusts, Vehicle Manufacturers , Individuals and everything in between.

    Once you set it up that way and people get used to having to deal with it, they will.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Would it be possible to make an alert of sorts that when specific words are entered into specific fields an alert pops up?
    Not that I disagree with tightening control of the process, but those answers don't really answer your question. Yes, it's possible. I would suggest a table that contains a field for acceptable values and a second field for the names you could possibly get. The 2nd field would contain the PK from tblCustomers rather than repeating the actual value. The 2 fields should be a composite index or composite primary so that a combination can't be repeated. Using the AfterUpdate event of the control that the user is typing in, you'd do a DLookup on a query that gets tblCustomer name value based on the textbox pseudonym and matching the FK value and PK values. This assumes you want to simply alter the value without prompting. If not, use the BeforeUpdate event and cancel if the proposed alteration isn't acceptable.

    The foregoing assumes a combo box with a list of proper values and pseudo values in separate columns isn't suitable because the pseudo values may be limitless. The first solution allows for adding any number of combinations to the junction table. Hope that isn't too confusing. If that's what didn't work for you, then perhaps you were missing something.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    tblCustomers: CustomerID, ...;
    tblAliases: AliasID, CustomerID, Alias; (Create an unique composite index on CustomerID and Alias)
    tblOrders: OrderID, CustomerID, ...

    PK in all tables may be autonumbers.
    On Orders form, to CustomerID is linked a combo box with
    Code:
    RowSource = "SELECT CustomerID, Alias FROM tblAliases ORDER BY Alias"
    User selects the alias, and proper CustomerID is assigned.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    @sinisterfrog

    Your base issue, as I understand things, is a lack of discipline in your "business process". I don't think this issue can be resolved/rectified by means of database alone. As others have suggested, you are "providing some service/product to some client for a fee". Some sort of "contract/agreement" --showing the client, the work order, an estimate of time and cost -- would seem to be needed (and normal business for most). I recommend that more analysis of the business process is required; get some feedback from your colleagues (and bosses) and when you get an agreed upon solution, use that as the basis of new tables/relationships etc. In a viable business Invoicing can not be an assumption or afterthought.

    As an analogy, think of your own bank account. The bank recognizes you(sinisterfrog) as the person who can withdraw funds from your account. They can't assume that Jeremiah Frog, Hop Frog, S Frog, A Frog all represent you and should withdraw(be paid) whatever they may ask from your account. And you won't have an account without signing something in one form or another.

    Good luck.

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

Similar Threads

  1. Incorrect data
    By Traceyann1964 in forum Database Design
    Replies: 1
    Last Post: 01-20-2016, 09:57 AM
  2. Search Box Incorrect Entry
    By libraccess in forum Forms
    Replies: 2
    Last Post: 01-01-2014, 03:52 PM
  3. Replies: 3
    Last Post: 09-29-2012, 10:57 AM
  4. Query Data Incorrect
    By Two Gun in forum Queries
    Replies: 8
    Last Post: 01-31-2012, 06:07 PM
  5. Subform loading incorrect data
    By GoVols02 in forum Forms
    Replies: 3
    Last Post: 02-12-2010, 09:53 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