# How to calculate two value between different date and different reco

• (4 Pages)
• 1
• 2
• 3
• Last »

## 49 Replies - 4165 Views - Last Post: 14 January 2011 - 05:28 PMRate Topic: //<![CDATA[ rating = new ipb.rating( 'topic_rate_', { url: 'http://www.dreamincode.net/forums/index.php?app=forums&module=ajax&section=topics&do=rateTopic&t=208500&amp;s=2c5d2bc56d820321030cfa1490cf6ee9&md5check=' + ipb.vars['secure_hash'], cur_rating: 0, rated: 0, allow_rate: 0, multi_rate: 1, show_rate_text: true } ); //]]>

### #1 mnazera

Reputation: 0
• Posts: 37
• Joined: 03-January 11

# How to calculate two value between different date and different reco

Posted 05 January 2011 - 06:00 PM

Pleasr help me, how to calculated this example data in access ?

ID, date, Value1, Value2, Value3

1, 2/12/2011, 500, 1000, 1500
2, 2/13/2011, 2000, 2500, 3000
3, 2/14/2011, 3500, 4000, 4500

How to calculated different value : Value1 (2/13/2011) - Value3 (2/12/2011) ?

Thank
Nazir
Is This A Good Question/Topic? 0

## Replies To: How to calculate two value between different date and different reco

### #2 rgfirefly24

• D.I.C Lover

Reputation: 360
• Posts: 1,810
• Joined: 07-April 08

## Re: How to calculate two value between different date and different reco

Posted 05 January 2011 - 06:14 PM

Access DateDiff function will give you the exact result your looking for

### #3 mnazera

Reputation: 0
• Posts: 37
• Joined: 03-January 11

## Re: How to calculate two value between different date and different reco

Posted 05 January 2011 - 11:21 PM

rgfirefly24, on 05 January 2011 - 05:14 PM, said:

Access DateDiff function will give you the exact result your looking for

Thank rgfirefly24
But, I don't to calculated numbers of day" between different date, I want to calculate value as example.

Nazir

### #4 rgfirefly24

• D.I.C Lover

Reputation: 360
• Posts: 1,810
• Joined: 07-April 08

## Re: How to calculate two value between different date and different reco

Posted 06 January 2011 - 02:50 PM

wait what? i'm sorry but what you said makes no sense. What exactly do you want to calculate?

### #5 Ionut

• D.I.C Lover

Reputation: 385
• Posts: 1,057
• Joined: 17-July 10

## Re: How to calculate two value between different date and different reco

Posted 07 January 2011 - 02:44 AM

The question is rather where do you want to do such calculations?
Do you want using database engine or through a VBA project(in which case this thread should be moved to VB6 forum).
Where do you want to store the difference? Is it in another table? Gave us more details about what do you try to achive/about alogorithm if you want to get usefull answer for your problem.

### #6 mnazera

Reputation: 0
• Posts: 37
• Joined: 03-January 11

## Re: How to calculate two value between different date and different reco

Posted 07 January 2011 - 06:15 AM

Ionut, on 07 January 2011 - 01:44 AM, said:

The question is rather where do you want to do such calculations?
Do you want using database engine or through a VBA project(in which case this thread should be moved to VB6 forum).
Where do you want to store the difference? Is it in another table? Gave us more details about what do you try to achive/about alogorithm if you want to get usefull answer for your problem.

Sorry,
Firt, I want to use Microsoft Access

detail of my problem, I have 3 tables, Table1, Table2 and Table3
and each tables have fields : IDTable1, DateTable1, ValueTable1 : IDTable2, DateTable2, ValueTable2 : IDTable3, DateTable3, ValueTable3

IDTable1, DateTable1, ValueTable1
1 2/1/2011 500
2 2/2/2011 1000

IDTable2, DateTable2, ValueTable2
1 2/1/2011 600
2 2/2/2011 1200

IDTable3, DateTable3, ValueTable3
1 2/1/2011 750
2 2/2/2011 1500

I want to calculate deviation between "1000 - 750 = 250", where this values actually in different record

I hope this my problem clear

### #7 Ionut

• D.I.C Lover

Reputation: 385
• Posts: 1,057
• Joined: 17-July 10

## Re: How to calculate two value between different date and different reco

Posted 07 January 2011 - 11:09 AM

well, this raises another serie of question, but, based on your example, I give you a way to do it and then feel free to ask other questions.
select
Table1.ValueTable1 - Table3.ValueTable3
From
Table1, Table3
where

This code calculates the difference between ValueTable1 and ValueTable3 depending on the input dates you give.
You can also join all three tables and make differences between columns.

Hope this helps.
Ionut

### #8 mnazera

Reputation: 0
• Posts: 37
• Joined: 03-January 11

## Re: How to calculate two value between different date and different reco

Posted 07 January 2011 - 05:00 PM

Ionut, on 07 January 2011 - 10:09 AM, said:

well, this raises another serie of question, but, based on your example, I give you a way to do it and then feel free to ask other questions.
select
Table1.ValueTable1 - Table3.ValueTable3
From
Table1, Table3
where

This code calculates the difference between ValueTable1 and ValueTable3 depending on the input dates you give.
You can also join all three tables and make differences between columns.

Hope this helps.
Ionut

Ok, thank very much for you, I will be try

### #9 mnazera

Reputation: 0
• Posts: 37
• Joined: 03-January 11

## Re: How to calculate two value between different date and different reco

Posted 07 January 2011 - 05:16 PM

mnazera, on 07 January 2011 - 04:00 PM, said:

Ionut, on 07 January 2011 - 10:09 AM, said:

well, this raises another serie of question, but, based on your example, I give you a way to do it and then feel free to ask other questions.
select
Table1.ValueTable1 - Table3.ValueTable3
From
Table1, Table3
where

This code calculates the difference between ValueTable1 and ValueTable3 depending on the input dates you give.
You can also join all three tables and make differences between columns.

Hope this helps.
Ionut

Ok, thank very much for you, I will be try

I'm sorry, do you mean like this ?

1 select
2 Table1.ValueTable1 - Table3.ValueTable3
3 From
4 Table1, Table3
5 where
6 Table3.ValueTable3 = DateTable1 and Table1.DataTable1 = DateTable2

Because I want to calculate different value with value 1 day before (data on 2/2/2011 with data on 2/1/2011), not same day

Thank

### #10 mnazera

Reputation: 0
• Posts: 37
• Joined: 03-January 11

## Re: How to calculate two value between different date and different reco

Posted 07 January 2011 - 06:16 PM

mnazera, on 07 January 2011 - 04:16 PM, said:

mnazera, on 07 January 2011 - 04:00 PM, said:

Ionut, on 07 January 2011 - 10:09 AM, said:

well, this raises another serie of question, but, based on your example, I give you a way to do it and then feel free to ask other questions.
select
Table1.ValueTable1 - Table3.ValueTable3
From
Table1, Table3
where

This code calculates the difference between ValueTable1 and ValueTable3 depending on the input dates you give.
You can also join all three tables and make differences between columns.

Hope this helps.
Ionut

Ok, thank very much for you, I will be try

I'm sorry, do you mean like this ?

1 select
2 Table1.ValueTable1 - Table3.ValueTable3
3 From
4 Table1, Table3
5 where
6 Table3.ValueTable3 = DateTable1 and Table1.DataTable1 = DateTable2

Because I want to calculate different value with value 1 day before (data on 2/2/2011 with data on 2/1/2011), not same day

Thank

If like this ?

select
Table1.ValueTable1 - Table3.ValueTable3
From
Table1, Table3
where
Table1.DateTable1 - Table3.DateTable3 = 1

Acually I want to calculate deviation data today with data yesterday
I want to create this code in Query

### #11 mnazera

Reputation: 0
• Posts: 37
• Joined: 03-January 11

## Re: How to calculate two value between different date and different reco

Posted 08 January 2011 - 12:28 AM

mnazera, on 07 January 2011 - 05:16 PM, said:

mnazera, on 07 January 2011 - 04:16 PM, said:

mnazera, on 07 January 2011 - 04:00 PM, said:

Ionut, on 07 January 2011 - 10:09 AM, said:

well, this raises another serie of question, but, based on your example, I give you a way to do it and then feel free to ask other questions.
select
Table1.ValueTable1 - Table3.ValueTable3
From
Table1, Table3
where

This code calculates the difference between ValueTable1 and ValueTable3 depending on the input dates you give.
You can also join all three tables and make differences between columns.

Hope this helps.
Ionut

Ok, thank very much for you, I will be try

I'm sorry, do you mean like this ?

1 select
2 Table1.ValueTable1 - Table3.ValueTable3
3 From
4 Table1, Table3
5 where
6 Table3.ValueTable3 = DateTable1 and Table1.DataTable1 = DateTable2

Because I want to calculate different value with value 1 day before (data on 2/2/2011 with data on 2/1/2011), not same day

Thank

If like this ?

select
Table1.ValueTable1 - Table3.ValueTable3
From
Table1, Table3
where
Table1.DateTable1 - Table3.DateTable3 = 1

Acually I want to calculate deviation data today with data yesterday
I want to create this code in Query

I give more detail of my problem

I have a table that includes production data for each shift of
processing, I want to calculated deviation every shift :
I have 3 table for 1 day , I plan use calculation in query

tblShift1 : IDShift1, ProcDateShift1, ProcDataShift1
tblShift2 : IDShift2, ProcDateShift2, ProcDataShift2
tblShift3 : IDShift3, ProcDateShift3, ProcDataShift3

to calculate ; ProcDataShift1 (today) - ProcDataShift3 (yesterday), not same day or same date but i want to calculate deviation today record (ProcDataShift1) with yesterday record (ProcDataShift3)

Can someone help me ?
Thank

### #12 Ionut

• D.I.C Lover

Reputation: 385
• Posts: 1,057
• Joined: 17-July 10

## Re: How to calculate two value between different date and different reco

Posted 08 January 2011 - 01:30 AM

ok, now we're getting closer.
My initial query almost gave you the answer, all you have to do is to use DateTime functions that access offers.
So, DateValue1 should be the current date, meaning that you change with Date(). DateValue2 sould be the previous day, for which we use DateDiff function (more about DateTime functions

select
Table1.ValueTable1 - Table3.ValueTable3
From
Table1, Table3
where
Table3.DataTable3 = DateDiff(dd, -1, Date()) and Table1.DataTable1 = Date()
--or as you suggested DateDiff(dd, Table3.DataTable3, Table1.DataTable1) = 1

Ionut

### #13 mnazera

Reputation: 0
• Posts: 37
• Joined: 03-January 11

## Re: How to calculate two value between different date and different reco

Posted 08 January 2011 - 01:58 AM

Ionut, on 08 January 2011 - 12:30 AM, said:

ok, now we're getting closer.
My initial query almost gave you the answer, all you have to do is to use DateTime functions that access offers.
So, DateValue1 should be the current date, meaning that you change with Date(). DateValue2 sould be the previous day, for which we use DateDiff function (more about DateTime functions

select
Table1.ValueTable1 - Table3.ValueTable3
From
Table1, Table3
where
Table3.DataTable3 = DateDiff(dd, -1, Date()) and Table1.DataTable1 = Date()
--or as you suggested DateDiff(dd, Table3.DataTable3, Table1.DataTable1) = 1

Ionut

Again, Thank Ionur,
Can explain me, what do you mean "dd" ?

I am waiting your explanation now

Thank

### #14 Ionut

• D.I.C Lover

Reputation: 385
• Posts: 1,057
• Joined: 17-July 10

## Re: How to calculate two value between different date and different reco

Posted 08 January 2011 - 02:02 AM

dd is an abbreviation for days. Check out the link I posted, you will find there explanations about date time functions.

### #15 mnazera

Reputation: 0
• Posts: 37
• Joined: 03-January 11

## Re: How to calculate two value between different date and different reco

Posted 08 January 2011 - 02:18 AM

Ionut, on 08 January 2011 - 01:02 AM, said:

dd is an abbreviation for days. Check out the link I posted, you will find there explanations about date time functions.

Thank
I will be try

• (4 Pages)
• 1
• 2
• 3
• Last »

 .related ul { list-style-type: circle; font-size: 12px; font-weight: bold; } .related li { margin-bottom: 5px; background-position: left 7px !important; margin-left: -35px; } .related h2 { font-size: 18px; font-weight: bold; } .related a { color: blue; }