Convert hours to Day(s) in query in MS Access

Look over the attached (query1, 2, 3) to give you a few ideas.
Time.mdb

@Daniel,
I look at the query, how to I get the 1/2 SL (half day) as 4 hours as I still trying to understand your queries?  Thanks

Shouldn't 1/2 SL be SL and 1/2 VL be VL?

I would setup Leave to be
SL
SL Hrs
VL
VL Hrs
CT

and the actual value be in the Day field (which in fact should be renamed more appropriately to something like amnt, ...).  If it true is Day, then Hrs values should be /8, thus 3.5 Hrs would be .4375.  Then everything would be on a common value base and you could more easily sum the values to extract the Days/Hrs you seek.

Also, you should never use words like Name, Date, Weekday, Day, Time, ... as field,control,object names as they are all reserved words and may lead to strange behaviors and issues.  You can learn more at http://www.devhut.net/2017/06/21/ms-access-reserved-word-checker/

This query will do:

SELECT 
    DaysOffChild.Name, 
    Right([Leave],2) AS Type, 
    Sum(Val(Replace([leave],"1/2","0.5"))*IIf(InStr([Leave],"hrs"),1,8)) AS TotalHours, 
    Sum(Val(Replace([leave],"1/2","0.5"))*IIf(InStr([Leave],"hrs"),1,8))\8 AS Days, 
    Sum(Val(Replace([leave],"1/2","0.5"))*IIf(InStr([Leave],"hrs"),1,8))-(Sum(Val(Replace([leave],"1/2","0.5"))*IIf(InStr([Leave],"hrs"),1,8))\8)*8 AS Hours
FROM 
    DaysOffChild
GROUP BY 
    DaysOffChild.Name, 
    Right([Leave],2);

Select allOpen in new window

See the attached, please.

@Gustav,
I tried your query, some are counting correctly but some of the calculation are counting double
TotalHours      Leave
2                     2 HRS CT
2.5                    2.5 HRS CT
28                    3.5 HR CT     - this should be 3.5 instead 28
4                    4 HRS CT
5.5                    5.5 HRS CT
8                    8 HRS CT
3                   1.50 HRS SL    -- this should be 1.5 instead 3
8                   1/2 SL              -- this should be 4 instead 8
6                   2 HRS SL          -- this should be 2 instead 6
0                   SL                    -- this should be 8 instead 0
8                  1/2 VL             - this should be 4
6                  2 HRS VL         -- this should be 2
5                  5 HRS VL
0                  VL                     -- this should be 8

While this question should get a solution the real question should be why there is such kind of mix between numbers and alphanumeric...
If you need to put some kind of "note" of what kind of day/hours just put it on a separate column...and let the numbers do their thing and the "notes" their own..
Would it be better :
Leave (hours)          TypeOfLeave
      8                               Normal
      3                               Medical
      2                               Parental

This single query will list your 33 records and the calculated total hours for each:

SELECT 
    DaysOffChild.Name, 
    Right([Leave],2) AS Type, 
    DaysOffChild.Leave, 
    (Val(IIf(Len([Leave])=2,1,Replace([leave],"1/2","0.5")))*IIf(InStr([Leave],"hr"),1,8)) AS TotalHours, 
    (Val(IIf(Len([Leave])=2,1,Replace([leave],"1/2","0.5")))*IIf(InStr([Leave],"hr"),1,8))\8 AS Days, 
    (Val(IIf(Len([Leave])=2,1,Replace([leave],"1/2","0.5")))*IIf(InStr([Leave],"hr"),1,8))-((Val(IIf(Len([Leave])=2,1,Replace([leave],"1/2","0.5")))*IIf(InStr([Leave],"hr"),1,8))\8)*8 AS Hours
FROM 
    DaysOffChild;

Select allOpen in new window

This single query will sum the total hours, days, and hours:

SELECT 
    DaysOffChild.Name, 
    Right([Leave],2) AS [Type], 
    Sum(Val(IIf(Len([Leave])=2,1,Replace([leave],"1/2","0.5")))*IIf(InStr([Leave],"hr"),1,8)) AS TotalHours, 
    Sum(Val(IIf(Len([Leave])=2,1,Replace([leave],"1/2","0.5")))*IIf(InStr([Leave],"hr"),1,8))\8 AS Days,
    Sum(Val(IIf(Len([Leave])=2,1,Replace([leave],"1/2","0.5")))*IIf(InStr([Leave],"hr"),1,8))-(Sum(Val(IIf(Len([Leave])=2,1,Replace([leave],"1/2","0.5")))*IIf(InStr([Leave],"hr"),1,8))\8)*8 AS Hours 
FROM 
    DaysOffChild
GROUP BY
    DaysOffChild.Name, 
    Right([Leave],2)

Select allOpen in new window

Time.mdb

John is right though.  If you have any control over the table structures, that is what truly should be address as it will subsequently simplify the rest of your work once it is fixed.

@Gustav,
your queries work perfect for the hours, I forgot to mention that we also have minutes like 15 mins SL or 30 mins VL, 45mins CT, 50 mins OT etc. how do I convert the mins to hours?  please be patience with me because I really stuck on this kind of calculations.  thanks

@John,
I will consider your suggestion to make changes of the DB.  Thanks

Keeping data clean will keep away from troubles that shouldn't exist in the 1st place...

please be patience with

OK, but please have in mind, that we only the sample data you provide.

15 minutes is
15/60 = 0.25
Similar
50/60 = 0.83
Just do the division with the leftovers (minutes) and add to the hours

I converted the mins to hr but having trouble to add them to the "Total Hour" & hour.  I create a new field in qyrNew called "MinToHr", please advise.
Timenew.mdb

Thank you experts!!

You're welcome. Glad to help out!