[mnazera]

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

[rgfirefly24]

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

[mnazera]

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

[rgfirefly24]

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

[Ionut]

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.

[mnazera]

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
Thanks for your response

[Ionut]

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

[mnazera]

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
    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

[mnazera]

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
    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

[mnazera]

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
    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

[mnazera]

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
    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

[Ionut]

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

[mnazera]

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

[Ionut]

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

[mnazera]

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