• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

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

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
btgtech
Asked:
btgtech
  • 2
  • 2
  • 2
3 Solutions
 
hnasrCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
btgtechAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
hnasrCommented:
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
 
btgtechAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now