Solved

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

Posted on 2014-10-03
6
152 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
  • 2
  • 2
  • 2
6 Comments
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 166 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 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 334 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 334 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

770 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