How to calculate two value between different date and different reco

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

49 Replies - 3770 Views - Last Post: 14 January 2011 - 05:28 PM Rate Topic: -----

#1 mnazera  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 296
  • View blog
  • Posts: 1,537
  • 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
Was This Post Helpful? 0
  • +
  • -

#3 mnazera  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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

View Postrgfirefly24, 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
Was This Post Helpful? 0
  • +
  • -

#4 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 296
  • View blog
  • Posts: 1,537
  • 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?
Was This Post Helpful? 0
  • +
  • -

#5 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • 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.
Was This Post Helpful? 0
  • +
  • -

#6 mnazera  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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

View PostIonut, 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
Thanks for your response
Was This Post Helpful? 0
  • +
  • -

#7 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • 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
    Table3.DataTable3 = ADate1 and Table1.DataTable1 = aDate2



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
Was This Post Helpful? 1
  • +
  • -

#8 mnazera  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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

View PostIonut, 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
    Table3.DataTable3 = ADate1 and Table1.DataTable1 = aDate2



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
Was This Post Helpful? 0
  • +
  • -

#9 mnazera  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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

View Postmnazera, on 07 January 2011 - 04:00 PM, said:

View PostIonut, 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
    Table3.DataTable3 = ADate1 and Table1.DataTable1 = aDate2



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
Was This Post Helpful? 0
  • +
  • -

#10 mnazera  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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

View Postmnazera, on 07 January 2011 - 04:16 PM, said:

View Postmnazera, on 07 January 2011 - 04:00 PM, said:

View PostIonut, 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
    Table3.DataTable3 = ADate1 and Table1.DataTable1 = aDate2



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
Was This Post Helpful? 0
  • +
  • -

#11 mnazera  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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

View Postmnazera, on 07 January 2011 - 05:16 PM, said:

View Postmnazera, on 07 January 2011 - 04:16 PM, said:

View Postmnazera, on 07 January 2011 - 04:00 PM, said:

View PostIonut, 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
    Table3.DataTable3 = ADate1 and Table1.DataTable1 = aDate2



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
Was This Post Helpful? 0
  • +
  • -

#12 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • 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
Was This Post Helpful? 0
  • +
  • -

#13 mnazera  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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

View PostIonut, 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
Was This Post Helpful? 0
  • +
  • -

#14 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • 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.
Was This Post Helpful? 0
  • +
  • -

#15 mnazera  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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

View PostIonut, 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
Was This Post Helpful? 0
  • +
  • -

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