Translating Yes/No check as a number 3

[Shinaab]

Faulty logic or my lack of Access knowledge seems to have put a halt on my project. I have a database that keeps track of the tasks that people perform on a loan. So, in my case, the user enters his/her loan number and checks a box that shows that they did task1, task2, task3, and/or task4 on the loan.

Now, I have to make a report for the higher ups that shows how people are spending their day. This is where the trouble for me starts. I need to translate a task1 into 15 minutes of work, task2 into 25 minutes of work, task3 into 15 minutes of work, and task4 into 42 minutes of work. So that one of the reports looks like this:

Jane
-- Loan number -- task1 -- task2 -- task3 -- task4 -- time
12345678 yes yes no no 40
56789012 yes no yes no 30

the sum of each column should show here.

John
-- Loan number -- task1 -- task2 -- task3 -- task4 -- time
89789012 no no no yes 42
12338564 yes no yes no 30

the sum of each column should show here.

Did that make sense to anyone? Can someone point me in the right direction?

 

[IGPCS]

yes you can use dcount function in the code builder where the task1 = "yes" something like this

Dcount (Task1, FormName,task1 = "yes") * 15

this should work just play around with the dcount i am not sure i described it correctly i dont have patient to check it in MS Access

Have a great and fantastic Evening

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[traingamer]

Assuming that your values are -1 for false and 0 for true,

 (([task1]+1)*15) +  (([task2]+1)*25) + (([task3]+1)*15) + (([task4]+1)*42)

would be one way to get your totals.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

Sorry -1 for true and 0 for false

(abs([task1]*15)) +  (abs([task2]*25)) + ...

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[IGPCS]

Traingamer
what i think he needs is the sum of each column not the total of all

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[lespaul]

Just as an FYI, having fields named task1, task2, task3 and task4 breaks the rules of normalization. See the fundamentals document below for more on the rules for your next database.

If you can't change your table structure, you can use a UNION query to get the data into a normalized structure and then use that query as the table.

The "format" you displayed:

UserName
LoanID Tasks

is set up in the report, not in the query. You won't be able to get a query to show the "levels".

So, what I would do is:

1. Create and save this query:

SELECT UserName, LoanID, "Task1" As Task From TableName where Task1
UNION
SELECT UserName, LoanID, "Task2" From TableName where Task
UNION
SELECT UserName, LoanID, "Task3" From TableName where Task3
UNION
SELECT UserName, LoanID, "Task4" From TableName where Task4

create a table, TaskTimes:
[tt]
TaskName TimeToComplete
Task1 15
Task2 25
Task3 15
Task4 42[/tt]

then a query to get the information you need:

SELECT UserName, LoanID, Task, TimeToComplete FROM qryNormalized INNER JOIN TaskTimes on qryNormalized.Task = TaskTime.TaskName

this will return:
[tt]
UserName LoanID Task TimeToComplete
Jane 12345678 Task1 15
Jane 12345678 Task2 25
Jane 56789012 Task1 15
Jane 56789012 Task3 15
John 89789012 Task4 42
John 12338564 Task1 15
John 12338564 Task3 15
[/tt]
now in the report you can modify the sorting and grouping to get the levels that you want.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Shinaab]

Thanks for all your help. I'm on the way to solving this.