UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Data Based On Another Field, Access 2016    
 
   
geoffk7828
post Aug 14 2019, 03:12 AM
Post#1



Posts: 23
Joined: 21-October 06
From: Barossa Valley South Australia, Australia


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.

Attached File  Costomer_Complete.zip ( 42.08K )Number of downloads: 3


Geoff
Go to the top of the page
 
RJD
post Aug 14 2019, 05:57 AM
Post#2


UtterAccess VIP
Posts: 10,024
Joined: 25-October 10
From: Gulf South USA


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

Attached File(s)
Attached File  Costomer_Complete_Rev1.zip ( 39.91K )Number of downloads: 3
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
GroverParkGeorge
post Aug 14 2019, 07:35 AM
Post#3


UA Admin
Posts: 35,683
Joined: 20-June 02
From: Newcastle, WA


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 some readings on good relational database design.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Aug 14 2019, 08:50 AM
Post#4


UA Admin
Posts: 35,683
Joined: 20-June 02
From: Newcastle, WA


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.

Attached File  Costomer_Complete_Rev2.zip ( 51.44K )Number of downloads: 2

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
RJD
post Aug 14 2019, 10:54 AM
Post#5


UtterAccess VIP
Posts: 10,024
Joined: 25-October 10
From: Gulf South USA


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

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
GroverParkGeorge
post Aug 14 2019, 10:57 AM
Post#6


UA Admin
Posts: 35,683
Joined: 20-June 02
From: Newcastle, WA


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

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
RJD
post Aug 14 2019, 11:17 AM
Post#7


UtterAccess VIP
Posts: 10,024
Joined: 25-October 10
From: Gulf South USA


laugh.gif

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
geoffk7828
post Sep 2 2019, 02:22 AM
Post#8



Posts: 23
Joined: 21-October 06
From: Barossa Valley South Australia, Australia


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

Thanks to all that commented, much appreciated.

Geoff
Go to the top of the page
 
RJD
post Sep 2 2019, 09:22 AM
Post#9


UtterAccess VIP
Posts: 10,024
Joined: 25-October 10
From: Gulf South USA


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

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th September 2019 - 03:56 PM