If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with computing date\Time between previous record
I am trying to calculate how long it has been from a previous visit. I am
tracking visit dates for customers. So I am sorting visit dates by customer. I want to look at a record date and subtract the previous reocrds date from it for all dates in the db. So in the end there is another Field that shows how long it has been bewteen customer visits? Dave K |
#2
|
|||
|
|||
Help with computing date\Time between previous record
Use in your query: Datediff("d",[firstdatefield],[seconddatefield])
The "D" is for days. -- Milton Purdy ACCESS State of Arkansas "Dave" wrote: I am trying to calculate how long it has been from a previous visit. I am tracking visit dates for customers. So I am sorting visit dates by customer. I want to look at a record date and subtract the previous reocrds date from it for all dates in the db. So in the end there is another Field that shows how long it has been bewteen customer visits? Dave K |
#3
|
|||
|
|||
Help with computing date\Time between previous record
Bring the table TWICE, one will get an _1 appended to its name.
Change the query type to a Total query. Join the two references through their clientID field. Bring all the required fields from the first reference in the grid. Keep GROUP BY under clientID and under the dateTimeStamp field, use LAST, or FIRST, for any other (optionally required) field. Bring only the dateTimeStamp field for the second table reference in the grid, the _1 one, and change the GROUP BY to WHERE, and, under it, add the criteria: [tableName].[dateTimeStampFieldName] (tableName without the _1 in the criteria) So, now, you made _1 the whole history (in relation of the not_1 reference). Make a computed expression in a new grid's column: [tableName].[dateTimeStamp] - MAX( [tableName_1].[dateTimeStamp] That should do the job, since MAX recuperate only the maximum value of the 'said' history. If you prefer to do it in SQL view: SELECT a.clientID, a.dateTimeStamp, LAST(a.someOtherField), a.dateTimeStamp)-MAX(b.dateTimeStamp) FROM tableName As a INNER JOIN tableName AS b ON a.clientID = b.clientID WHERE b.dateTimeStamp a.dateTimeStamp GROUP BY a.clientID, a.dateTimeStamp Sure, that does not show records having no previous record (for that client). Modify the SQL statement like below, to get those: SELECT a.clientID, a.dateTimeStamp, LAST(a.someOtherField), a.dateTimeStamp)-MAX(b.dateTimeStamp) FROM tableName As a LEFT JOIN tableName AS b ON a.clientID = b.clientID AND b.dateTimeStamp a.dateTimeStamp GROUP BY a.clientID, a.dateTimeStamp where the INNER JOIN has become a LEFT JOIN, and the where clause included in the join. The expression a.dateTimeStamp)-MAX(b.dateTimeStamp) will return null for records without previous one. Vanderghast, Access MVP "Dave" wrote in message ... I am trying to calculate how long it has been from a previous visit. I am tracking visit dates for customers. So I am sorting visit dates by customer. I want to look at a record date and subtract the previous reocrds date from it for all dates in the db. So in the end there is another Field that shows how long it has been bewteen customer visits? Dave K |
#4
|
|||
|
|||
Help with computing date\Time between previous record
Thanks I will give this a try
Dave "vanderghast" wrote: Bring the table TWICE, one will get an _1 appended to its name. Change the query type to a Total query. Join the two references through their clientID field. Bring all the required fields from the first reference in the grid. Keep GROUP BY under clientID and under the dateTimeStamp field, use LAST, or FIRST, for any other (optionally required) field. Bring only the dateTimeStamp field for the second table reference in the grid, the _1 one, and change the GROUP BY to WHERE, and, under it, add the criteria: [tableName].[dateTimeStampFieldName] (tableName without the _1 in the criteria) So, now, you made _1 the whole history (in relation of the not_1 reference). Make a computed expression in a new grid's column: [tableName].[dateTimeStamp] - MAX( [tableName_1].[dateTimeStamp] That should do the job, since MAX recuperate only the maximum value of the 'said' history. If you prefer to do it in SQL view: SELECT a.clientID, a.dateTimeStamp, LAST(a.someOtherField), a.dateTimeStamp)-MAX(b.dateTimeStamp) FROM tableName As a INNER JOIN tableName AS b ON a.clientID = b.clientID WHERE b.dateTimeStamp a.dateTimeStamp GROUP BY a.clientID, a.dateTimeStamp Sure, that does not show records having no previous record (for that client). Modify the SQL statement like below, to get those: SELECT a.clientID, a.dateTimeStamp, LAST(a.someOtherField), a.dateTimeStamp)-MAX(b.dateTimeStamp) FROM tableName As a LEFT JOIN tableName AS b ON a.clientID = b.clientID AND b.dateTimeStamp a.dateTimeStamp GROUP BY a.clientID, a.dateTimeStamp where the INNER JOIN has become a LEFT JOIN, and the where clause included in the join. The expression a.dateTimeStamp)-MAX(b.dateTimeStamp) will return null for records without previous one. Vanderghast, Access MVP "Dave" wrote in message ... I am trying to calculate how long it has been from a previous visit. I am tracking visit dates for customers. So I am sorting visit dates by customer. I want to look at a record date and subtract the previous reocrds date from it for all dates in the db. So in the end there is another Field that shows how long it has been bewteen customer visits? Dave K |
#5
|
|||
|
|||
Help with computing date\Time between previous record
I have the SQL query you sent working, now how do i adjust the end result?
I want to compare the LED of the current record to the TCD of the next record. How do I do that? My present SQL: SELECT a.[Item Number], a.[Serial Number], a.LSD, a.LED, Last(a.TCD) AS LastOfTCD, [a].[LED]-Max([b].[LED]) AS Days INTO FTFDays FROM AOCTCA AS a INNER JOIN AOCTCA AS b ON (a.[Serial Number] = b.[Serial Number]) AND (a.[Item Number] = b.[Item Number]) WHERE (((b.LED)[a].[LED])) GROUP BY a.[Item Number], a.[Serial Number], a.LSD, a.LED; "vanderghast" wrote: Bring the table TWICE, one will get an _1 appended to its name. Change the query type to a Total query. Join the two references through their clientID field. Bring all the required fields from the first reference in the grid. Keep GROUP BY under clientID and under the dateTimeStamp field, use LAST, or FIRST, for any other (optionally required) field. Bring only the dateTimeStamp field for the second table reference in the grid, the _1 one, and change the GROUP BY to WHERE, and, under it, add the criteria: [tableName].[dateTimeStampFieldName] (tableName without the _1 in the criteria) So, now, you made _1 the whole history (in relation of the not_1 reference). Make a computed expression in a new grid's column: [tableName].[dateTimeStamp] - MAX( [tableName_1].[dateTimeStamp] That should do the job, since MAX recuperate only the maximum value of the 'said' history. If you prefer to do it in SQL view: SELECT a.clientID, a.dateTimeStamp, LAST(a.someOtherField), a.dateTimeStamp)-MAX(b.dateTimeStamp) FROM tableName As a INNER JOIN tableName AS b ON a.clientID = b.clientID WHERE b.dateTimeStamp a.dateTimeStamp GROUP BY a.clientID, a.dateTimeStamp Sure, that does not show records having no previous record (for that client). Modify the SQL statement like below, to get those: SELECT a.clientID, a.dateTimeStamp, LAST(a.someOtherField), a.dateTimeStamp)-MAX(b.dateTimeStamp) FROM tableName As a LEFT JOIN tableName AS b ON a.clientID = b.clientID AND b.dateTimeStamp a.dateTimeStamp GROUP BY a.clientID, a.dateTimeStamp where the INNER JOIN has become a LEFT JOIN, and the where clause included in the join. The expression a.dateTimeStamp)-MAX(b.dateTimeStamp) will return null for records without previous one. Vanderghast, Access MVP "Dave" wrote in message ... I am trying to calculate how long it has been from a previous visit. I am tracking visit dates for customers. So I am sorting visit dates by customer. I want to look at a record date and subtract the previous reocrds date from it for all dates in the db. So in the end there is another Field that shows how long it has been bewteen customer visits? Dave K |
#6
|
|||
|
|||
Help with computing date\Time between previous record
Maybe using a subquery is the easiest way, at this point (another
possibility would be to rank of LED values, per item number, thus rank-1 and rank+1 would point to the previous and next record (in terms of 'date') ), .... It is untested though ... SELECT ..., ( SELECT c.tcd FROM aoctca AS c WHERE c.[item number] = a.[item number] AND c.[serial number]= a.[serial number] AND c.led = (SELECT MIN(d.led) FROM aoctca AS d WHERE d.[item number] = a.[item number] AND d.[serial number]= a.[serial number] AND d.led a.led )) FROM ... The inner most subquery returns the earliest date a.led while the subquery itself spot that record and return its tcd value. May be quite slow, though. Vanderghast, Access MVP "Dave" wrote in message ... I have the SQL query you sent working, now how do i adjust the end result? I want to compare the LED of the current record to the TCD of the next record. How do I do that? My present SQL: SELECT a.[Item Number], a.[Serial Number], a.LSD, a.LED, Last(a.TCD) AS LastOfTCD, [a].[LED]-Max([b].[LED]) AS Days INTO FTFDays FROM AOCTCA AS a INNER JOIN AOCTCA AS b ON (a.[Serial Number] = b.[Serial Number]) AND (a.[Item Number] = b.[Item Number]) WHERE (((b.LED)[a].[LED])) GROUP BY a.[Item Number], a.[Serial Number], a.LSD, a.LED; "vanderghast" wrote: Bring the table TWICE, one will get an _1 appended to its name. Change the query type to a Total query. Join the two references through their clientID field. Bring all the required fields from the first reference in the grid. Keep GROUP BY under clientID and under the dateTimeStamp field, use LAST, or FIRST, for any other (optionally required) field. Bring only the dateTimeStamp field for the second table reference in the grid, the _1 one, and change the GROUP BY to WHERE, and, under it, add the criteria: [tableName].[dateTimeStampFieldName] (tableName without the _1 in the criteria) So, now, you made _1 the whole history (in relation of the not_1 reference). Make a computed expression in a new grid's column: [tableName].[dateTimeStamp] - MAX( [tableName_1].[dateTimeStamp] That should do the job, since MAX recuperate only the maximum value of the 'said' history. If you prefer to do it in SQL view: SELECT a.clientID, a.dateTimeStamp, LAST(a.someOtherField), a.dateTimeStamp)-MAX(b.dateTimeStamp) FROM tableName As a INNER JOIN tableName AS b ON a.clientID = b.clientID WHERE b.dateTimeStamp a.dateTimeStamp GROUP BY a.clientID, a.dateTimeStamp Sure, that does not show records having no previous record (for that client). Modify the SQL statement like below, to get those: SELECT a.clientID, a.dateTimeStamp, LAST(a.someOtherField), a.dateTimeStamp)-MAX(b.dateTimeStamp) FROM tableName As a LEFT JOIN tableName AS b ON a.clientID = b.clientID AND b.dateTimeStamp a.dateTimeStamp GROUP BY a.clientID, a.dateTimeStamp where the INNER JOIN has become a LEFT JOIN, and the where clause included in the join. The expression a.dateTimeStamp)-MAX(b.dateTimeStamp) will return null for records without previous one. Vanderghast, Access MVP "Dave" wrote in message ... I am trying to calculate how long it has been from a previous visit. I am tracking visit dates for customers. So I am sorting visit dates by customer. I want to look at a record date and subtract the previous reocrds date from it for all dates in the db. So in the end there is another Field that shows how long it has been bewteen customer visits? Dave K |
Thread Tools | |
Display Modes | |
|
|