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?