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
|
|||
|
|||
Date difference for single field
Hi. I'd appreciate any help with this problem. I've read threads on this
before, but couldn't get it right. I want to calculate the difference between dates in a single field to help calculate trap*nights. Here's the table structure in the table called tblADateID: ADateID (pk) Status ADate TrapsSet 1 Opened 3/13/06 2 2 Closed 3/15/06 2 3 Opened 4/1/06 3 4 Checked 4/15/06 2 5 Checked 4/16/06 4 6 Closed 4/17/06 4 I'm trying to get a query that will write this: TrapsSet Checked PriorCheck Nights TrapNights 2 3/15/06 3/13/06 2 4 3 4/15/06 4/1/06 14 42 2 4/16/06 4/15/06 1 2 4 4/17/06 4/16/06 1 4 "Nights" is the number of nights between an open and a check, a check and a check, a check and a closed, or an open and a closed- essentially everything except nights between closing and opening. TrapNights is the number of traps set previously times the number of nights in that interval. Eventually, I'd like to supply date parameters to calculate TrapNights for a given interval. Thanks for any help. ak |
#2
|
|||
|
|||
Date difference for single field
Dear Kastle:
What you can do is JOIN the table to itself. Create two subsets of the table, one for "Opened" Status and the other for "Closed", then JOIN them together: SELECT T1.ADate, T2.ADate T1.TrapsSet FROM (SELECT * FROM tblADateID WHERE Status = "Opened") T1 INNER JOIN (SELECT * FROM tblADateID WHERE Status = "Closed") T2 ON T2.TrapsSet = T1.TrapsSet If this works, you can simply add a DateDiff() on T1.ADate and T2.ADate to give you the result you want. Did this help? What can I do to help you further? Tom Ellison Microsoft Access MVP "accesskastle" wrote in message ... Hi. I'd appreciate any help with this problem. I've read threads on this before, but couldn't get it right. I want to calculate the difference between dates in a single field to help calculate trap*nights. Here's the table structure in the table called tblADateID: ADateID (pk) Status ADate TrapsSet 1 Opened 3/13/06 2 2 Closed 3/15/06 2 3 Opened 4/1/06 3 4 Checked 4/15/06 2 5 Checked 4/16/06 4 6 Closed 4/17/06 4 I'm trying to get a query that will write this: TrapsSet Checked PriorCheck Nights TrapNights 2 3/15/06 3/13/06 2 4 3 4/15/06 4/1/06 14 42 2 4/16/06 4/15/06 1 2 4 4/17/06 4/16/06 1 4 "Nights" is the number of nights between an open and a check, a check and a check, a check and a closed, or an open and a closed- essentially everything except nights between closing and opening. TrapNights is the number of traps set previously times the number of nights in that interval. Eventually, I'd like to supply date parameters to calculate TrapNights for a given interval. Thanks for any help. ak |
#3
|
|||
|
|||
Date difference for single field
Thanks, Tom. I tried your SQL, and it seemed to do a Cartesian, so it
doesn't take the dates in order, from one date to the next date, but seems to throw them all together in all possible combinations. Is there a way to go from one date to the next? "Tom Ellison" wrote: Dear Kastle: What you can do is JOIN the table to itself. Create two subsets of the table, one for "Opened" Status and the other for "Closed", then JOIN them together: SELECT T1.ADate, T2.ADate T1.TrapsSet FROM (SELECT * FROM tblADateID WHERE Status = "Opened") T1 INNER JOIN (SELECT * FROM tblADateID WHERE Status = "Closed") T2 ON T2.TrapsSet = T1.TrapsSet If this works, you can simply add a DateDiff() on T1.ADate and T2.ADate to give you the result you want. Did this help? What can I do to help you further? Tom Ellison Microsoft Access MVP "accesskastle" wrote in message ... Hi. I'd appreciate any help with this problem. I've read threads on this before, but couldn't get it right. I want to calculate the difference between dates in a single field to help calculate trap*nights. Here's the table structure in the table called tblADateID: ADateID (pk) Status ADate TrapsSet 1 Opened 3/13/06 2 2 Closed 3/15/06 2 3 Opened 4/1/06 3 4 Checked 4/15/06 2 5 Checked 4/16/06 4 6 Closed 4/17/06 4 I'm trying to get a query that will write this: TrapsSet Checked PriorCheck Nights TrapNights 2 3/15/06 3/13/06 2 4 3 4/15/06 4/1/06 14 42 2 4/16/06 4/15/06 1 2 4 4/17/06 4/16/06 1 4 "Nights" is the number of nights between an open and a check, a check and a check, a check and a closed, or an open and a closed- essentially everything except nights between closing and opening. TrapNights is the number of traps set previously times the number of nights in that interval. Eventually, I'd like to supply date parameters to calculate TrapNights for a given interval. Thanks for any help. ak |
#4
|
|||
|
|||
Date difference for single field
Dear Kastle:
I had not correctly understood your requirements. Perhaps I do now. Here's a query that matches your results from the data given: SELECT T1.*, T2.*, DateDiff("d",[T1].[ADate],[T2].[ADate]) AS Diff, DateDiff("d",[T1].[ADate],[T2].[ADate])*[T1].[TrapsSet] AS TrapNights FROM tblADateID AS T1, tblADateID AS T2 WHERE T1.Status "Closed" AND T2.Status "Opened" AND T2.ADate = (SELECT MIN(ADate) FROM tblADateID T3 WHERE T3.ADate T1.ADate); Perhaps that's what you were wanting. I believe this imposes some requirements on the way data is entered. In sequential order, there must be an "Opened", then any number of "Checked" rows, then a single "Closed". Only the most recent set may have be without a "Closed". I'm not sure this is what you have in mind, or how you are going to enforce that, but it would seem to be a necessity. You might want to have a column which might be called "SetName" which has the same value for all the rows within a set. Each set would be required to start with an "Opened" row and would remain "open" until a "closed" row is added. If you had this, you could then have multiple sets open at any date, and sets could overlap in time. Don't know what the physical realities of your system are, but this could be useful. Tom Ellison "accesskastle" wrote in message ... Thanks, Tom. I tried your SQL, and it seemed to do a Cartesian, so it doesn't take the dates in order, from one date to the next date, but seems to throw them all together in all possible combinations. Is there a way to go from one date to the next? "Tom Ellison" wrote: Dear Kastle: What you can do is JOIN the table to itself. Create two subsets of the table, one for "Opened" Status and the other for "Closed", then JOIN them together: SELECT T1.ADate, T2.ADate T1.TrapsSet FROM (SELECT * FROM tblADateID WHERE Status = "Opened") T1 INNER JOIN (SELECT * FROM tblADateID WHERE Status = "Closed") T2 ON T2.TrapsSet = T1.TrapsSet If this works, you can simply add a DateDiff() on T1.ADate and T2.ADate to give you the result you want. Did this help? What can I do to help you further? Tom Ellison Microsoft Access MVP "accesskastle" wrote in message ... Hi. I'd appreciate any help with this problem. I've read threads on this before, but couldn't get it right. I want to calculate the difference between dates in a single field to help calculate trap*nights. Here's the table structure in the table called tblADateID: ADateID (pk) Status ADate TrapsSet 1 Opened 3/13/06 2 2 Closed 3/15/06 2 3 Opened 4/1/06 3 4 Checked 4/15/06 2 5 Checked 4/16/06 4 6 Closed 4/17/06 4 I'm trying to get a query that will write this: TrapsSet Checked PriorCheck Nights TrapNights 2 3/15/06 3/13/06 2 4 3 4/15/06 4/1/06 14 42 2 4/16/06 4/15/06 1 2 4 4/17/06 4/16/06 1 4 "Nights" is the number of nights between an open and a check, a check and a check, a check and a closed, or an open and a closed- essentially everything except nights between closing and opening. TrapNights is the number of traps set previously times the number of nights in that interval. Eventually, I'd like to supply date parameters to calculate TrapNights for a given interval. Thanks for any help. ak |
Thread Tools | |
Display Modes | |
|
|