• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Subtraction in query (1 Viewer)

MikiA

New member
Local time
Today, 23:58
Joined
Sep 2, 2020
Messages
17
Hello,

i wanna try to create query where I want to subtract the value of C from the two fields, A and B.
Basically A-C and B-C.
But that the subtraction from the column A goes to result 0. When the final result is zero the subtraction from B the column begins.

I was try until now to use conditional formating.
In same time i substract c from both column, and if resul > 0 display, result, if = 0 i says display "0" for fisrt column
For second i add A+B like F. Until F>B display. B starting value. Its not good.
Is it posible and on which way do something like this:

Code:
If A>0 then
A-C = result
else
B-C = result
.....
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:58
Joined
Oct 29, 2018
Messages
12,624
Hi. Can you show us some sample data to help clarify what you mean? Is C a constant value or does it change?
 

MikiA

New member
Local time
Today, 23:58
Joined
Sep 2, 2020
Messages
17
Value C is constant value and always have value 1
So, A-1 until zero, then when A = 0 starts B-1
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:58
Joined
Oct 29, 2018
Messages
12,624
Yes. Will be always zero
Okay, sounds like you might be able to use the concept of retrieving the previous record, so you can tell if you're supposed to subtract from A or from B.
 

MikiA

New member
Local time
Today, 23:58
Joined
Sep 2, 2020
Messages
17
I just google about that and i am not sure that i understand very well
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:58
Joined
Oct 29, 2018
Messages
12,624
I just google about that and i am not sure that i understand very well
It works well if you table has a PK field, and it might go something like this.

Code:
ID     A     B    PrevC    C
1      3     5             2
2      2     4     2       1
3      1     3     1       0
4      5     2     0       1
5      6     1     1       0
Actually, I tried to create a sample data for you, but I am not sure I got any close. I asked earlier if you could post a sample set of data. Can you please do that to help us get close to what you want? Thanks.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:58
Joined
Feb 19, 2002
Messages
29,803
Create 2 functions. One to return a value for A and the other to return a value for B. I'm assuming that you don't decrement B for the record where A goes to zero.

Code:
Public Function Avalue(A, C) as Integer
    if A > 0 Then
        Avalue = A - C
    else
        Avalue = A
    end if
End Function
Public Function Bvalue(A, B, C) as Integer
    if A = 0 Then
        Bvalue = B - C
    else
        Bvalue = B
    End If
End Function
 

MikiA

New member
Local time
Today, 23:58
Joined
Sep 2, 2020
Messages
17
Actually i dont have a sample. That was a start. And i stuck.
At first it seemed simple to me....
I was think its just A-C and B-C.... but i wrong.
Now I am looking for get value from the previous record as you suggested to me.
But table should be somethong like this
222.jpg

B will be always 20 at start
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:58
Joined
Oct 29, 2018
Messages
12,624
Actually i dont have a sample. That was a start. And i stuck.
At first it seemed simple to me....
I was think its just A-C and B-C.... but i wrong.
Now I am looking for get value from the previous record as you suggested to me.
But table should be somethong like this
View attachment 85086
B will be always 20 at start
Hi. Thanks for posting the sample data. So, is this projection of C column correct?
Code:
ID    A    B      C
1    12    20    11
2    10    20     9
3     8    20     7
4     7    20     6
5    18    20    17
 

MikiA

New member
Local time
Today, 23:58
Joined
Sep 2, 2020
Messages
17
Yes. Exactly. When value in C down to zero, start subtraction from B
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:58
Joined
Oct 29, 2018
Messages
12,624
Yes. Exactly. When value in C down to zero, start subtraction from B
Okay, in that case, the last piece of the puzzle then is once we start subtracting from col B, we start ignoring col A, correct? For instance:
Code:
ID    A    B      C
1    12    20    11
2    10    20     9
3     1    20    19
4     7    20    19
5    18    20    19
Is this correct?
 

MikiA

New member
Local time
Today, 23:58
Joined
Sep 2, 2020
Messages
17
When all field in col A down to zero, will start substract from col B.
20 -1 = 19
But for ID 1 for example can be substract several time 20 - 1 - 1 - 1
for ID 2 = 20 - 1
for ID 3 = 20 -1 -1

end col B will go to zero....

For each name (or ID), C values will be assigned individually in the table.
Column A presents the vacation days for employees from last year. Column B are the holiday days for the new year. The value of C is the day the employee used.
When the employee uses all the days of the old vacation (column A), the use of the new vacation begins (column B)
 

plog

Banishment Pending
Local time
Today, 17:58
Joined
May 11, 2011
Messages
10,083
To what end? What's the big picture? Specifically why must days be tracked so granularly? Do they expire at some point such that you need to know when they were given? That's the only scenario I can imagine. The thing is, your table is not set up in the the proper way to accomodate that.

You are essentially creating a ledger--you have credits, you have debits. In a database a ledger puts all the values it wants to do math on in the same column and rows of data are added to accomodate more data, not more columns. Every new year you will have to add a new column to accomodate new data, that's not the right way.

I suggest you give us the big picture of what you are trying to do with your data and not this particular step which may not be the correct way to reach your ultimate aim.
 

MikiA

New member
Local time
Today, 23:58
Joined
Sep 2, 2020
Messages
17
Thats all i want. To track spended days. But cos have many employee, hand tracking its to complicate
 

MikiA

New member
Local time
Today, 23:58
Joined
Sep 2, 2020
Messages
17
But, you push me to think...
Can i all values put at same col ?
Then will be easy to calculate old and new days. Cos new days value is always 20. And spended days have value 1.
Right ?
 

plog

Banishment Pending
Local time
Today, 17:58
Joined
May 11, 2011
Messages
10,083
Thats all i want.

What's "that"? Please describe the ultimate purpose. Take technology out of it. Pretend I'm a new employee and explain how your vacation policy works.
 

MikiA

New member
Local time
Today, 23:58
Joined
Sep 2, 2020
Messages
17
You, like new employee have 20 days.
If you don’t spend those days, they become "old" days. At the beginning of each year you get 20 new days
You have to spend the "old" days until the sixth month of the following year or you lose the right to them.
The days of the "old" vacation are spent first, and then the new ones. And so on...
Complicated ?
 

plog

Banishment Pending
Local time
Today, 17:58
Joined
May 11, 2011
Messages
10,083
Not too complicated, but different from your method. You just need to set up your tables correctly and then build a few queries to apply your business rules. The ledger table would look like this:

tblVacation
vac_ID, autonumber, primary key
ID_Employee, number, foreign key to employee table
vac_Days, number, number of days in this transaction (-1 for a used day, +20 for new year allocation)
vac_Date, date, date transaction occured
vac_Notes, text, description of the transaction (took Vacation, Annual Allocation, etc)

Those 5 fields will tell you all about an employees vacation allocation and use. What you then need is 2 INSERT queries:

1. This query will credit 20 days for every active employee and is run the first of the year.

2. This query will debit all the days an employee has that need to expire from prior years allocations and is run each June.

That's how you should approach this.
 

Users who are viewing this thread

Top Bottom