?
Solved

Force a user to input data for all of the categories of Labor

Posted on 2014-10-03
6
Medium Priority
?
158 Views
Last Modified: 2014-11-18
I have a table which has a list of the work centers for the company numbered 1 - 10

I am putting together a quote database in Access.  Part of the quote is to estimate the labor.  I was going to have the user enter a record for each of the Workcenters by selecting the workcenter from a dropdown, but instead I would like to force there to be a record in the labor table for each of the work centers automatically so that the user only has to enter the hours and the rate for each.

I have screenshots of the following:
1. The Work Center Table
2. Example of the view that I would like to create
3. View of the query that I am working with.
Query-Example.png
Work-Center-Table.png
Labor-Example-Screen.png
0
Comment
Question by:btgtech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 664 total points
ID: 40360834
tblQuotedLabor has total(calculated) fields that should be avoided.
Looks like you have a 1:1 relation between tables.
You may approach it as a form and subform.

Upload a sample database, and explain the process you want the data be entered.
This will show the relation and what approach to adopt.
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1336 total points
ID: 40361191
As hnasr has mentioned, you should not store calculated values in a table. It's difficult to keep it updated, and there's just no valid reason to do it, since you can easily get the total on the fly by adding those values together.

If you want to require your user to enter a value in each box, then use the BeforeUpdate event of the Form, and check each of those boxes:

Sub Form_BeforeUpdate(Cancel As Integer)
  If Nz(Me.FirstControl, "") = "" Or Nz(Me.SecondControl, "") = "" Or Nz(Me.ThirdControl, "") = ""Then
    Msgbox "You must enter a value in all WorkCenters"
    Cancel = True
  End If
End Sub

This would not save the record, and the user would be forced to either complete the data entry, or hit the ESC key to clear out the data.

What's the purpose of tblQuotedLabor? It's hard to tell exactly what it's supposed to do, but if it's there to store the Labor for a specific workcenter for each quote then you don't need to store anything other than the WorkCenter, LaborRate, and EstimatedHours. I work extensively with programs just like this (i.e. manufacturing ERP systems), and the structure you're using could be incorrect (although we really don't know until we see the whole thing). Essentially, a Quote would be a combination of Labor, Materials, and AdditionalCosts (like Shipping, Outside Services, etc). If you need to track multiple WorkCenters for each Quote, it would seem your structure would be something like:

tblQuote >> tblQuoteOperations
tblQuote >> tblQuoteMaterials
tblQuote >> tblQuoteAddlCharges

So tblQuoteOperations would have a distinct record for each workcenter, and that record would store the WorkCenter, LaborRate, and EstimatedHours. Each record in tblQuoteOperations would be related back to it's parent Quote.

You'd store the details of each WorkCenter in a separate table:

tblWorkCenter
-----------------
WorkCenter_ID
WorkCenter_Name
WorkCenter_Rate
etc etc

That table would be used to get the default values when the user selects that WorkCenter for inclusion in a quote, but the actual details of that WorkCenter for that Quote would be stored in tblQuoteOperations.

So when you need to get the QuoteTotal, you Sum on the three child tables:

SELECT SUM(Quote_Operation_Est_Hrs * Quote_Operation_Labor_Rate) AS Operation_Total FROM tblQuoteOperations WHERE QuoteID=[YourQuoteID]
0
 

Author Comment

by:btgtech
ID: 40366100
Scott MCDaniel
I totally agree with the total field.  I am not using it and it should be removed.

Also, the tblQuoted Labor is the table to collect all of the labor for the Quote (There are other quote tables to collect the other details of the quote.)

I also have the WorkCentertable to define the work centers.

This structure would lead me to have a subform that has records and forces the user to select each workcenter with the possibility of the user missing a workcenter.

I would like to be able to have the subform look like the form attached.  What is the best way to query the data from the two tables shown to do that?
Labor-Example-Screen.png
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1336 total points
ID: 40366181
In that case, I'd think you'd need to use a temporary table situation. Your temporary tables would contain a row for each WorkCenter (you could create that with a DELETE and then INSERT query as needed).

When the user has finished filling the form, check to be sure the temporary table is filled, then use VBA/SQL to write that data back to the live tables.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40367008
This is just a first guess, after referring to your attached image.

Main record: WorkCenters:(wsId, wsName, ...)
Sub Record: Labors (wsId, lbrID, cutSawNoe, cutSawEst, cutSawRate,...repeated for Brake and other descriptions)

A main form is created bound to WorkCenters, and a subform is created bound to Labors table.

Try to upload what you have in your design.
0
 

Author Comment

by:btgtech
ID: 40450361
I have included a file as an example of the Labor tables and query that I would like to use.
1. Each Quote will have Estimated labor for each Work Center.  I would like to have a field on the form for each Work Center so that the user will need to put a value in each work center.
2. Each Quote can have more than 1 work order.
3. Each work order will have Planned Hours and Revised Plan Hours
4. Actual hours are captured in a timesheet - not included int he example.

Questions
1. How to a force a record to be created for each WorkCenter when estimating the hours for the quote?
2. I want to capture the Estimated, Planned and Revised plan hours in the tblQuotedWC table.  does this work?

Thanks for your help.
LaborExample.accdb
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question