Solved

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

Posted on 2014-10-03
6
155 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 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 85

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

705 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