Solved

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

Posted on 2014-10-03
6
145 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 84

Accepted Solution

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now