Is conditional statement possible in access across columns?

[helpmeimpoor]

I know I can do IF statements in excel, but i don't know if its possible in excel.



Here is the analogy.

We have 3 columns, where column 1 has job #, either 1 or 2. Second and third column has the left and return time.

I have 1 driver who leaves house at X hour and returns at Y hour. Let's call this job 1. Sometime the driver does another job, being job 2.


I want to find out time he spends on job 1 in column 4, which is time difference, such as 4 hours. Now since some days the driver does job 2, I want Access to add that time to that specific day, so lets say he did 2 hours in job 2, and we have 6 hours in column 4.


In excel, this will be done something like, IF column 1 contains "1", find me time, if contains "2", combine the time.

[June7]

Not sure what you mean by 'across columns'. Expressions can certainly reference multiple fields/controls.

[accesstos]

You need two more fields(columns) in a table for the works of each day of each driver:

WorkID	DriverID	workDay	Job	startTime	endTime
1	1	8/31/2019	1	7:00	12:00
2	1	8/31/2019	2	13:00	18:00
3	1	9/1/2019	1	7:00	15:00

Then, you can add the man-hours for each driver for each workday with a query like this:

SELECT driverID, workDay, CDate(Sum(endTime-startTime)) AS Man-Hours
FROM tblWorks 
GROUP BY driverID, workDay

You will get somethig like that:

driverID	workDay	Man-Hours
1	8/31/2019	10:00:00
1	9/1/2019	8:00:00

P.S.:
Note that this is a simple example.
In actually, you have to check if the endTime is in the next day of the startTime (startTime>endTime), and if so, you have to add 1 to the endTime (Sum(([endTime]+1)-[startTime])).

[helpmeimpoor]

You need two more fields(columns) in a table for the works of each day of each driver:

WorkID	DriverID	workDay	Job	startTime	endTime
1	1	8/31/2019	1	7:00	12:00
2	1	8/31/2019	2	13:00	18:00
3	1	9/1/2019	1	7:00	15:00

Then, you can add the man-hours for each driver for each workday with a query like this:

SELECT driverID, workDay, CDate(Sum(endTime-startTime)) AS Man-Hours
FROM tblWorks 
GROUP BY driverID, workDay

You will get somethig like that:

driverID	workDay	Man-Hours
1	8/31/2019	10:00:00
1	9/1/2019	8:00:00

P.S.:
Note that this is a simple example.
In actually, you have to check if the endTime is in the next day of the startTime (startTime>endTime), and if so, you have to add 1 to the endTime (Sum(([endTime]+1)-[startTime])).

Hello

Thanks for your answer, but I am not familiar with SQL code. I just do things in design mode. I am attaching the picture: https://ibb.co/G94x489

In the picture, while the database is single, i created duplicate so I have 2 sets of each database. As in, the database had both job 1 and job 2, i separated the two. Now here is what I am trying to do:

If say Dharminder went on job 1, i have his time spent TTime 1. Now Dharminder may or may not go on Job 2, but the second column is all messed up. So the CombinnedTT should add up TTime1 and TTime 2, but as in the picture you can see it is adding dharminder's with bunch of other people's time.



I tried to create query relationship, but then it only lists me drivers where the name matches on both sides, despite selecting to list name from primary file irrespective of name existing in secondary duplicate file or not.

I am going to keep trying to see if I can use your code and work within a single database instead of splitting the database.

[pbaldy]

Post 4 was moderated, I'm posting to trigger email notifications.