I’M to query based on period then calculation with MS ACCESS in VB.NET.
Please Guide me
I don’t want to use functions in MS Access because I use SQL for VB.NET.
Thanks
Table ABSEN
| ID | DATE | TIME | FP | INOUT | OTHERS1 | OTHERS2 | STATUS | OPTION |
|---|---|---|---|---|---|---|---|---|
| 5008 | 28-Apr-24 | 08:00 | 1 | IN | 1 | 1 | PRESENT | |
| 5008 | 28-Apr-24 | 17:00 | 1 | OUT | 1 | 1 | PRESENT | |
| 5009 | 29-Apr-24 | 08:00 | 1 | IN | 1 | 1 | PRESENT | |
| 5009 | 29-Apr-24 | 17:00 | 1 | OUT | 1 | 1 | PRESENT | |
| 5010 | 29-Apr-24 | 08:00 | 1 | IN | 1 | 1 | PRESENT | |
| 5011 | 29-Apr-24 | 08:00 | 1 | IN | 1 | 1 | PRESENT | NO |
| 5012 | 29-Apr-24 | 1 | IN | 1 | 1 | NO | ||
| 5008 | 30-Apr-24 | 08:00 | 1 | IN | 1 | 1 | PRESENT | |
| 5008 | 30-Apr-24 | 17:00 | 1 | OUT | 1 | 1 | PRESENT | |
| 5009 | 31-Apr-24 | 08:00 | 1 | IN | 1 | 1 | PRESENT | |
| 5009 | 31-Apr-24 | 17:00 | 1 | OUT | 1 | 1 | PRESENT |
TABLE MASTERID
| ID | IDNAME |
|---|---|
| 5008 | A |
| 5009 | B |
| 5010 | C |
| 5011 | D |
| 5012 | E |
TABLE PERIOD
| PERIOD | STARTDATE | ENDDATE |
|---|---|---|
| FIRST | 28-Apr-24 | 29-Apr-24 |
| SECOND | 30-Apr-24 | 31-Apr-24 |
DESIRED RESULT
BASED ON CONDITION PERIOD = ‘FIRSTFROM TABLEPERIOD`
BASED ON CONDITION OPTION =”
BASED ON CONDITION STATUS =’PRESENT’
SUMIN = TOTAL CONDITION IN
SUMOUT = TOTAL CONDITION OUT
DAYWORK = SUMIN+ SUMOUT / 2 ‘
if the result is odd then the daywork becomes check or the total sumout and total sumin are not the same, so the result is check
| ID | IDNAME | SUMIN | SUMOUT | DAYSWORK |
|---|---|---|---|---|
| 5008 | A | 2 | 2 | 2 |
| 5009 | B | 2 | 2 | 2 |
| 5010 | C | 1 | CHECK |
BASED ON CONDITION PERIOD ‘SECONDFROM TABLEPERIOD`
BASED ON CONDITION OPTION =”
BASED ON CONDITION STATUS =’PRESENT’
SUMIN = TOTAL CONDITION IN
SUMOUT = TOTAL CONDITION OUT
DAYWORK = SUMIN+ SUMOUT / 2 ‘
if the result is odd then the daywork becomes check or the total sumout and total sumin are not the same, so the result is check
| ID | IDNAME | SUMIN | SUMOUT | DAYSWORK |
|---|---|---|---|---|
| 5008 | A | 2 | 2 | 2 |
| 5009 | B | 2 | 2 | 2 |
Tam88 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.