Results 1 to 3 of 3
  1. #1
    St.Alphonzo is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Dec 2015
    Posts
    9

    job costing, multiple tiers of validation

    HI! I have a Access 2016 database that tracks billable job costs for a general contracting company.



    Right now, I have two primary tables containing cost data: JobCosts (sub-contract, material and rental invoices) and EmployeeHours (in house labor, 5 employees). JobCosts is pretty simple: a vendor issues us an invoice, and it is entered through a form that records DATE, JOB (4 digit project ID#, validated against "Jobs" table), COSTCODE (sub/material/rental, validated against "CostCode" table), VENDOR (validated against "Vendors" table), INVOICE (short text/number - tracking ID from source document), COMPONENT (job component number validated against "Components" table - a list of 154 different elements of home building) and of course COST.

    "EmployeeHours" is similar, except it records hours worked by Employees, assigning them to a component, validated against the same table.

    So basically there are three levels of complexity with regard to the different tiers of data entry:
    the source document-- a vendor invoice or employee time card -- is:
    1) allocated 100% to a single component number on a single job.
    2) broken out to multiple components on the same job
    3) broken out to multiple components on different jobs (most common with time-cards, but sometimes a sub-contractor invoice will include work performed on more than one job)

    HERE'S THE PROBLEM: big jobs have budgets with a specific set of components. While entering a component number that isn't on the master list is not possible, it IS possible and quite common to assign a component number that isn't being used on that job... which causes confusion when we go to create an invoice . I need to be able to restrict input to the "COMPONENT" field to numbers identified in the budget for that job. So essentially, I need the value entered into the "JOB" field to specify validation rules for other fields in the same record before it is entered... is this possible?

    OR could I use a single form to output data to multiple tables? So if I used a table validation rule to restrict the component field, that means each job would have its own table... would I have to manually switch between tables or forms in order to record entries for different jobs?

  2. #2
    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
    that means each job would have its own table.
    No. Each Job is a record in the Jobtable, and the JobTable is related to other tables.

    There are a number of short youtube videos by BA_Experts. These can help you with analysis and data gathering.

    Here are some other links and info to help you with design. (post #7)

    Here is a list of reserved words in Access that you should avoid.

  3. #3
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    To answer your question: Yes it is possible. Whether or not it can be explained sufficiently via a forum is unclear as the implementation depends on the structure of the data as well as the User Interface Experience you intend to provide.

    For instance you could do a compare, followed by a pop up message - this would allow flexibility while still reminding users of the rules.

    You could dynamically define the available components per job somehow in the list/combo box being used (how that is done depends on the data structure).

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

Similar Threads

  1. Replies: 2
    Last Post: 03-18-2015, 07:34 PM
  2. Multiple Validation Rule
    By gatsby in forum Access
    Replies: 20
    Last Post: 01-15-2013, 05:24 AM
  3. Multiple Validation Rules
    By Theremin_Ohio in forum Access
    Replies: 1
    Last Post: 12-16-2011, 01:21 PM
  4. Labor Costing: I'm So Dead...
    By JohnHoo in forum Access
    Replies: 3
    Last Post: 11-22-2011, 02:14 AM
  5. Weighted-average inventory costing.
    By evander in forum Database Design
    Replies: 9
    Last Post: 01-03-2011, 08:32 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