Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Tool bars, Menu bars + Ribbon _ Data Based On Another Field

Posted by: geoffk7828 Aug 14 2019, 03:12 AM

Hi All,

I am trying to populate a Field with data based on another Field:

Field "Job_Outcome" has 4 records: "Completed Install", "Task Not Completed", " Completed Assurance" and "Install Non standard"

"Completed Install" = 3 Hours
"Task Not Completed" = 1 Hour
" Completed Assurance" = 1.5 Hours
"Install Non standard" = 3 Hours

I need Field "Hours" to be populated with the correct hours for the task, the next step would be to get the Sub Total and then the total hours for each Technician.

The end result would be:

Tech Name
Completed Install 15 3 45
Completed Assurance 4 1.5 6
Task Not Completed 6 1 6

57



I have attached a copy of my DB to help.

 Costomer_Complete.zip ( 42.08K ): 3


Geoff

Posted by: RJD Aug 14 2019, 05:57 AM

Hi Geoff: The best way to do this, IMO, is to add a table of job outcome hours and tie that into the report record source query. You should also add the date range to the report. See the revised db attached. See the revised query and report.

Also, you should never use the field names "Date" or "Hour" since these are reserved words and can cause problems in some cases.

HTH
Joe


 Costomer_Complete_Rev1.zip ( 39.91K ): 7
 

Posted by: GroverParkGeorge Aug 14 2019, 07:35 AM

Unfortunately, the table in the sample provided doesn't quite meet the needs of an Access Relational Database Application.

It has a single table with a number of attribute fields that can be better handled with lookup tables as Joe added for the one field in question. I urge you to consider a more normalized design going forward. While this simple "spreadsheet" style table can be made to work, the process of working with non-normalized data tends to be both fussier and less efficient.

Let's start with https://www.UtterAccess.com/forum/index.php?showtopic=1998783


Posted by: GroverParkGeorge Aug 14 2019, 08:50 AM

I took what Joe suggested and expanded the process as far as possible, given the partial data provided.

There are at least two tables missing, so they'll need to be evaluated and included. I assume you have a "Customer" table and a "Job" table. Job_Type needs to be in that "Job" table, NOT in the Customer_Completed, because that attribute applies to the job, regardless of outcome I would expect. If Job_Type IS an attribute of the completion process, then put it back, using the appropriate Foreign Key. I further assume that your "Job" table has a Foreign Key to the Customer table indicating which customer each job is for.
And finally, I think it is the JOB that is completed or not, and that "Customer_Completed" therefore should be called "Job_Complete" or something similar. I say that because I don't think of Customers themselves as being "Completed' or "Not Completed". I would expect it is entirely possible for a customer to have multiple jobs....

Note that "spreadsheet" style tables are simple on the surface, at least as far as adding new data is concerned, but they clearly lead to inefficient, inflexible procedures when trying to manage and use the data.

 Costomer_Complete_Rev2.zip ( 51.44K ): 5

Posted by: RJD Aug 14 2019, 10:54 AM

George: Thanks for expanding on my solution - that was needed. I took the shortcut of simply solving the immediate issue, whereas you took a better, more comprehensive approach, which was much better.

Thanks,
Joe

Posted by: GroverParkGeorge Aug 14 2019, 10:57 AM

I have too much time on my hands these days....

Posted by: RJD Aug 14 2019, 11:17 AM

laugh.gif

Posted by: geoffk7828 Sep 2 2019, 02:22 AM

Apologies for the late reply, I have this part working ok now.

Thanks to all that commented, much appreciated.

Geoff

Posted by: RJD Sep 2 2019, 09:22 AM

You are welcome, from both George and me. Glad you got that working.

Regards,
Joe