Calculate Diff Date?
in [Queries]
|
From: kritter0021 on 27 Jan 2010 14:40 Ok, If I want to calculate the difference of a date with the following date in Access: so 3/29/08 in cell A1 and 5/3/08 in cell A2 and return the value in B1 of 35. How do I do this? Thanks! In Excell the formula looks like this: =IF(+A1-A2>0,A1-A2,0) Here is some data which may help. Thanks! Here is the data that my query looks like and Dif Date is what I want calculated. I think I need to number each record. How do I do that, so just 1,2,3,4.... Once I have them numbered then it can look at previous record. I don't know. Maybe...Thanks. Product Date Dif Date A 1/23/2009 40 A 3/4/2009 65 A 5/8/2009 0 B 3/2/2009 28 B 3/30/2009 0 Text Date Number I am wanting to calculate the Diff Date and when it moves to a new product display the average of of the product, so instead of displaying 0 show the average of A of 52.5. Not sure if this is possible. Real easy in excell.
From: raskew via AccessMonster.com on 27 Jan 2010 14:53 Hi - It's equally simple in Access. Look up the DateDiff(() function in your help file. Best wishes -- Bob kritter0021 wrote: >Ok, If I want to calculate the difference of a date with the following date >in Access: so 3/29/08 in cell A1 and 5/3/08 in cell A2 and return the value >in B1 of 35. How do I do this? Thanks! > >In Excell the formula looks like this: > >=IF(+A1-A2>0,A1-A2,0) > >Here is some data which may help. Thanks! > >Here is the data that my query looks like and Dif Date is what I want >calculated. I think I need to number each record. How do I do that, so just >1,2,3,4.... Once I have them numbered then it can look at previous record. >I don't know. Maybe...Thanks. > >Product Date Dif Date >A 1/23/2009 40 >A 3/4/2009 65 >A 5/8/2009 0 >B 3/2/2009 28 >B 3/30/2009 0 > >Text Date Number > >I am wanting to calculate the Diff Date and when it moves to a new product >display the average of of the product, so instead of displaying 0 show the >average of A of 52.5. Not sure if this is possible. Real easy in excell. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201001/1
From: kritter0021 on 27 Jan 2010 17:23 That works when you are calculating it between two fields, not when you are trying to use the previous date in the same field. So Product Date Dif Date > >A 1/23/2009 40 > >A 3/4/2009 65 > >A 5/8/2009 0 > >B 3/2/2009 28 > >B 3/30/2009 0 I need DiffDate beween the first two records calculated. Not sure how to make it do that. 3/4/09 - 1/23/09 = 40. "raskew via AccessMonster.com" wrote: > Hi - > > It's equally simple in Access. Look up the DateDiff(() function in your help > file. > > Best wishes -- Bob > > kritter0021 wrote: > >Ok, If I want to calculate the difference of a date with the following date > >in Access: so 3/29/08 in cell A1 and 5/3/08 in cell A2 and return the value > >in B1 of 35. How do I do this? Thanks! > > > >In Excell the formula looks like this: > > > >=IF(+A1-A2>0,A1-A2,0) > > > >Here is some data which may help. Thanks! > > > >Here is the data that my query looks like and Dif Date is what I want > >calculated. I think I need to number each record. How do I do that, so just > >1,2,3,4.... Once I have them numbered then it can look at previous record. > >I don't know. Maybe...Thanks. > > > >Product Date Dif Date > >A 1/23/2009 40 > >A 3/4/2009 65 > >A 5/8/2009 0 > >B 3/2/2009 28 > >B 3/30/2009 0 > > > >Text Date Number > > > >I am wanting to calculate the Diff Date and when it moves to a new product > >display the average of of the product, so instead of displaying 0 show the > >average of A of 52.5. Not sure if this is possible. Real easy in excell. > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201001/1 > > . >
From: KARL DEWEY on 27 Jan 2010 23:06 Use these three queries -- kritter0021 SELECT YourTable.Product, YourTable.ProdDate, (SELECT COUNT(*) FROM YourTable AS [XX] WHERE [XX].Product = YourTable.Product AND [XX].ProdDate <= YourTable.ProdDate) AS RANK FROM YourTable ORDER BY YourTable.Product, YourTable.ProdDate; kritter0021_X SELECT kritter0021.Product, kritter0021.ProdDate, kritter0021.RANK FROM kritter0021 UNION ALL SELECT YourTable.Product, NULL AS ProdDate, Max([RANK]) +1 AS RANK1 FROM YourTable INNER JOIN kritter0021 ON YourTable.Product = kritter0021.Product GROUP BY YourTable.Product; SELECT kritter0021_X.Product, kritter0021_X.ProdDate, IIf([kritter0021_X_1].[ProdDate] Is Null,0,DateDiff("d",[kritter0021_X].[ProdDate],[kritter0021_X_1].[ProdDate])) AS Expr1 FROM kritter0021_X INNER JOIN kritter0021_X AS kritter0021_X_1 ON kritter0021_X.Product = kritter0021_X_1.Product WHERE (((kritter0021_X_1.RANK)=[kritter0021_X].[RANK]+1)) ORDER BY kritter0021_X.Product, kritter0021_X.ProdDate, IIf([kritter0021_X_1].[ProdDate] Is Null,0,DateDiff("d",[kritter0021_X].[ProdDate],[kritter0021_X_1].[ProdDate])); -- Build a little, test a little. "kritter0021" wrote: > That works when you are calculating it between two fields, not when you are > trying to use the previous date in the same field. So > > Product Date Dif Date > > >A 1/23/2009 40 > > >A 3/4/2009 65 > > >A 5/8/2009 0 > > >B 3/2/2009 28 > > >B 3/30/2009 0 > > I need DiffDate beween the first two records calculated. Not sure how to > make it do that. 3/4/09 - 1/23/09 = 40. > > > "raskew via AccessMonster.com" wrote: > > > Hi - > > > > It's equally simple in Access. Look up the DateDiff(() function in your help > > file. > > > > Best wishes -- Bob > > > > kritter0021 wrote: > > >Ok, If I want to calculate the difference of a date with the following date > > >in Access: so 3/29/08 in cell A1 and 5/3/08 in cell A2 and return the value > > >in B1 of 35. How do I do this? Thanks! > > > > > >In Excell the formula looks like this: > > > > > >=IF(+A1-A2>0,A1-A2,0) > > > > > >Here is some data which may help. Thanks! > > > > > >Here is the data that my query looks like and Dif Date is what I want > > >calculated. I think I need to number each record. How do I do that, so just > > >1,2,3,4.... Once I have them numbered then it can look at previous record. > > >I don't know. Maybe...Thanks. > > > > > >Product Date Dif Date > > >A 1/23/2009 40 > > >A 3/4/2009 65 > > >A 5/8/2009 0 > > >B 3/2/2009 28 > > >B 3/30/2009 0 > > > > > >Text Date Number > > > > > >I am wanting to calculate the Diff Date and when it moves to a new product > > >display the average of of the product, so instead of displaying 0 show the > > >average of A of 52.5. Not sure if this is possible. Real easy in excell. > > > > -- > > Message posted via AccessMonster.com > > http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201001/1 > > > > . > >
From: kritter0021 on 10 Feb 2010 10:16 Ok, Thanks! That worked! Now, if I wanted to add a unique id for each product, how would I do that and involve that in this series of queries. I have tried myself, but failed. Thanks! "KARL DEWEY" wrote: > Use these three queries -- > > kritter0021 > SELECT YourTable.Product, YourTable.ProdDate, (SELECT COUNT(*) FROM > YourTable AS [XX] WHERE [XX].Product = YourTable.Product AND [XX].ProdDate > <= YourTable.ProdDate) AS RANK > FROM YourTable > ORDER BY YourTable.Product, YourTable.ProdDate; > > kritter0021_X > SELECT kritter0021.Product, kritter0021.ProdDate, kritter0021.RANK > FROM kritter0021 > UNION ALL SELECT YourTable.Product, NULL AS ProdDate, Max([RANK]) +1 AS RANK1 > FROM YourTable INNER JOIN kritter0021 ON YourTable.Product = > kritter0021.Product > GROUP BY YourTable.Product; > > SELECT kritter0021_X.Product, kritter0021_X.ProdDate, > IIf([kritter0021_X_1].[ProdDate] Is > Null,0,DateDiff("d",[kritter0021_X].[ProdDate],[kritter0021_X_1].[ProdDate])) > AS Expr1 > FROM kritter0021_X INNER JOIN kritter0021_X AS kritter0021_X_1 ON > kritter0021_X.Product = kritter0021_X_1.Product > WHERE (((kritter0021_X_1.RANK)=[kritter0021_X].[RANK]+1)) > ORDER BY kritter0021_X.Product, kritter0021_X.ProdDate, > IIf([kritter0021_X_1].[ProdDate] Is > Null,0,DateDiff("d",[kritter0021_X].[ProdDate],[kritter0021_X_1].[ProdDate])); > > -- > Build a little, test a little. > > > "kritter0021" wrote: > > > That works when you are calculating it between two fields, not when you are > > trying to use the previous date in the same field. So > > > > Product Date Dif Date > > > >A 1/23/2009 40 > > > >A 3/4/2009 65 > > > >A 5/8/2009 0 > > > >B 3/2/2009 28 > > > >B 3/30/2009 0 > > > > I need DiffDate beween the first two records calculated. Not sure how to > > make it do that. 3/4/09 - 1/23/09 = 40. > > > > > > "raskew via AccessMonster.com" wrote: > > > > > Hi - > > > > > > It's equally simple in Access. Look up the DateDiff(() function in your help > > > file. > > > > > > Best wishes -- Bob > > > > > > kritter0021 wrote: > > > >Ok, If I want to calculate the difference of a date with the following date > > > >in Access: so 3/29/08 in cell A1 and 5/3/08 in cell A2 and return the value > > > >in B1 of 35. How do I do this? Thanks! > > > > > > > >In Excell the formula looks like this: > > > > > > > >=IF(+A1-A2>0,A1-A2,0) > > > > > > > >Here is some data which may help. Thanks! > > > > > > > >Here is the data that my query looks like and Dif Date is what I want > > > >calculated. I think I need to number each record. How do I do that, so just > > > >1,2,3,4.... Once I have them numbered then it can look at previous record. > > > >I don't know. Maybe...Thanks. > > > > > > > >Product Date Dif Date > > > >A 1/23/2009 40 > > > >A 3/4/2009 65 > > > >A 5/8/2009 0 > > > >B 3/2/2009 28 > > > >B 3/30/2009 0 > > > > > > > >Text Date Number > > > > > > > >I am wanting to calculate the Diff Date and when it moves to a new product > > > >display the average of of the product, so instead of displaying 0 show the > > > >average of A of 52.5. Not sure if this is possible. Real easy in excell. > > > > > > -- > > > Message posted via AccessMonster.com > > > http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201001/1 > > > > > > . > > >
|
Pages: 1 Prev: query export wont allow a zero value? Next: calculating a percentage |