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
|
|||
|
|||
Please Help! Ref Article ID 210504
While I found previous comments helpful, I still have an issue I can't
seem to figure out. Say I have a table with an "ID" field, an "In Date/Time" and "Out Date/Time". I want to calculate the elapsed time between events ("Out Time" of the previous event to the "In Time" of the next event). Everything works fine until I forget to add a record and need to add a record with times which fit between 2 previous records (Based on the In Time). The new record is entered as the last record in the table and the DLookUp is now finding the "Out Time" of the previous record in the table but not the previous event (Based on the "In Time" and "Out Time" fields). What I want is for the DLookUp to find the "Out Time" of the previous event based on the "In Time" and not the "ID" field for cases like this where a record may be entered later. Any help would be greatly appreciated! -- If you can read this thank a teacher... If your reading it in english, thank a veteran! |
#2
|
|||
|
|||
"Telobamipada" wrote in message
... While I found previous comments helpful, I still have an issue I can't seem to figure out. Say I have a table with an "ID" field, an "In Date/Time" and "Out Date/Time". I want to calculate the elapsed time between events ("Out Time" of the previous event to the "In Time" of the next event). Everything works fine until I forget to add a record and need to add a record with times which fit between 2 previous records (Based on the In Time). The new record is entered as the last record in the table and the DLookUp is now finding the "Out Time" of the previous record in the table but not the previous event (Based on the "In Time" and "Out Time" fields). What I want is for the DLookUp to find the "Out Time" of the previous event based on the "In Time" and not the "ID" field for cases like this where a record may be entered later. Any help would be greatly appreciated! -- If you can read this thank a teacher... If your reading it in english, thank a veteran! It's difficult to understand what you are trying to do. Suggest you post some sample data to provide a clearer explanation. BTW - your tag line is a nice sentiment. Given its content, however, you should correct the grammatical error in it. (my 2 cents worth for both suggestions) |
#3
|
|||
|
|||
Randy, thanks for the reply. Here's an example:
If you have a table with the 3 fields I have described and populate it with 4 records as shown below: ID In Time Out Time 1 4/1/2005 09:00 AM 4/1/2005 09:30 AM 2 4/1/2005 10:00 AM 4/1/2005 10:30 AM 3 4/1/2005 12:00 AM 4/1/2005 12:30 AM 4 4/1/2005 01:00 PM 4/1/2005 01:30 PM Now, using the DLookUp formula posted in Article 210504 for "Queries" everything is fine. I now need to add a record, say for the period 11:00 to 11:30. Now my table looks like this: ID In Time Out Time 1 4/1/2005 09:00 AM 4/1/2005 09:30 AM 2 4/1/2005 10:00 AM 4/1/2005 10:30 AM 3 4/1/2005 12:00 AM 4/1/2005 12:30 AM 4 4/1/2005 01:00 PM 4/1/2005 01:30 PM 5 4/1/2005 11:00 AM 4/1/2005 11:30 AM If I set up a macro to sort the table based on the "In Time" field when the record is saved from the form, the table looks like this: ID In Time Out Time 1 4/1/2005 09:00 AM 4/1/2005 09:30 AM 2 4/1/2005 10:00 AM 4/1/2005 10:30 AM 5 4/1/2005 11:00 AM 4/1/2005 11:30 AM 3 4/1/2005 12:00 AM 4/1/2005 12:30 AM 4 4/1/2005 01:00 PM 4/1/2005 01:30 PM The way the DLookUp formula works is based on the "ID" field which is an auto number and primary key. I've attempted to use the DLookUp with the "In Time" field and it is obviously not going to work... I simply need to set up a report which will show me the the elapsed time between the "Out Time" of the first event for that day and the "In Time" for the next event , in sequencial order. The query is set up to have the user enter a date they are after. The report when finished would look like this: Procedures for 4/1/2005 In Time Out Time Elapsed Time 4/1/2005 09:00 AM 4/1/2005 09:30 AM 30 4/1/2005 10:00 AM 4/1/2005 10:30 AM 30 4/1/2005 11:00 AM 4/1/2005 11:30 AM 30 4/1/2005 12:00 AM 4/1/2005 12:30 AM 30 4/1/2005 01:00 PM 4/1/2005 01:30 PM 30 I hope this helps you to understand what I'm trying to accomplish here, I am no expert by any stretch of the imagination! Since the DLookUp obviously will not work with a date field I thought I would add a field to my table and form such as "Patient Sequence Number" that the user would enter and base my DLookUp on that... I'm thinking there's a better way... Randy Harris" wrote: "Telobamipada" wrote in message ... While I found previous comments helpful, I still have an issue I can't seem to figure out. Say I have a table with an "ID" field, an "In Date/Time" and "Out Date/Time". I want to calculate the elapsed time between events ("Out Time" of the previous event to the "In Time" of the next event). Everything works fine until I forget to add a record and need to add a record with times which fit between 2 previous records (Based on the In Time). The new record is entered as the last record in the table and the DLookUp is now finding the "Out Time" of the previous record in the table but not the previous event (Based on the "In Time" and "Out Time" fields). What I want is for the DLookUp to find the "Out Time" of the previous event based on the "In Time" and not the "ID" field for cases like this where a record may be entered later. Any help would be greatly appreciated! -- If you can read this thank a teacher... If your reading it in english, thank a veteran! It's difficult to understand what you are trying to do. Suggest you post some sample data to provide a clearer explanation. BTW - your tag line is a nice sentiment. Given its content, however, you should correct the grammatical error in it. (my 2 cents worth for both suggestions) |
#4
|
|||
|
|||
You are correct that you would not be able to use the DLookup method
described in that article, because your records won't be ordered by ID number. Frankly, I don't think much of that method anyway, your ID field would always have to be perfectly sequential. That could prove to be a real nightmare in the future. This is one of those problems that have many different solutions. One solution is to do the math in a query. For example: SELECT [IN-OutDates].ID, [IN-OutDates].InTime, [IN-OutDates].OutTime, DateDiff("n",[OutTime],DMin("InTime","In-OutDates","[InTime]#" & [OutTime] & "#")) AS Elapsed FROM [IN-OutDates] ORDER BY [IN-OutDates].InTime; ID InTime OutTime Elapsed 1 4/1/2005 9:00:00 AM 4/1/2005 9:30:00 AM 30 2 4/1/2005 10:00:00 AM 4/1/2005 10:30:00 AM 30 5 4/1/2005 11:00:00 AM 4/1/2005 11:30:00 AM 30 3 4/1/2005 12:00:00 PM 4/1/2005 12:30:00 PM 30 4 4/1/2005 1:00:00 PM 4/1/2005 1:30:00 PM I pasted the output from the query above, I hope it comes through. The calculation could also be done in the report, using the same sort of date math. Hope this helps. -- Randy Harris (tech at promail dot com) "Telobamipada" wrote in message news Randy, thanks for the reply. Here's an example: If you have a table with the 3 fields I have described and populate it with 4 records as shown below: ID In Time Out Time 1 4/1/2005 09:00 AM 4/1/2005 09:30 AM 2 4/1/2005 10:00 AM 4/1/2005 10:30 AM 3 4/1/2005 12:00 AM 4/1/2005 12:30 AM 4 4/1/2005 01:00 PM 4/1/2005 01:30 PM Now, using the DLookUp formula posted in Article 210504 for "Queries" everything is fine. I now need to add a record, say for the period 11:00 to 11:30. Now my table looks like this: ID In Time Out Time 1 4/1/2005 09:00 AM 4/1/2005 09:30 AM 2 4/1/2005 10:00 AM 4/1/2005 10:30 AM 3 4/1/2005 12:00 AM 4/1/2005 12:30 AM 4 4/1/2005 01:00 PM 4/1/2005 01:30 PM 5 4/1/2005 11:00 AM 4/1/2005 11:30 AM If I set up a macro to sort the table based on the "In Time" field when the record is saved from the form, the table looks like this: ID In Time Out Time 1 4/1/2005 09:00 AM 4/1/2005 09:30 AM 2 4/1/2005 10:00 AM 4/1/2005 10:30 AM 5 4/1/2005 11:00 AM 4/1/2005 11:30 AM 3 4/1/2005 12:00 AM 4/1/2005 12:30 AM 4 4/1/2005 01:00 PM 4/1/2005 01:30 PM The way the DLookUp formula works is based on the "ID" field which is an auto number and primary key. I've attempted to use the DLookUp with the "In Time" field and it is obviously not going to work... I simply need to set up a report which will show me the the elapsed time between the "Out Time" of the first event for that day and the "In Time" for the next event , in sequencial order. The query is set up to have the user enter a date they are after. The report when finished would look like this: Procedures for 4/1/2005 In Time Out Time Elapsed Time 4/1/2005 09:00 AM 4/1/2005 09:30 AM 30 4/1/2005 10:00 AM 4/1/2005 10:30 AM 30 4/1/2005 11:00 AM 4/1/2005 11:30 AM 30 4/1/2005 12:00 AM 4/1/2005 12:30 AM 30 4/1/2005 01:00 PM 4/1/2005 01:30 PM 30 I hope this helps you to understand what I'm trying to accomplish here, I am no expert by any stretch of the imagination! Since the DLookUp obviously will not work with a date field I thought I would add a field to my table and form such as "Patient Sequence Number" that the user would enter and base my DLookUp on that... I'm thinking there's a better way... Randy Harris" wrote: "Telobamipada" wrote in message ... While I found previous comments helpful, I still have an issue I can't seem to figure out. Say I have a table with an "ID" field, an "In Date/Time" and "Out Date/Time". I want to calculate the elapsed time between events ("Out Time" of the previous event to the "In Time" of the next event). Everything works fine until I forget to add a record and need to add a record with times which fit between 2 previous records (Based on the In Time). The new record is entered as the last record in the table and the DLookUp is now finding the "Out Time" of the previous record in the table but not the previous event (Based on the "In Time" and "Out Time" fields). What I want is for the DLookUp to find the "Out Time" of the previous event based on the "In Time" and not the "ID" field for cases like this where a record may be entered later. Any help would be greatly appreciated! -- If you can read this thank a teacher... If your reading it in english, thank a veteran! It's difficult to understand what you are trying to do. Suggest you post some sample data to provide a clearer explanation. BTW - your tag line is a nice sentiment. Given its content, however, you should correct the grammatical error in it. (my 2 cents worth for both suggestions) |
#5
|
|||
|
|||
Randy, One small problem. Your example is showing the elapsed time for each
occurance. What I need to do is show the elapsed time from the "Out Time" of each occurance to the "In Time" of the next occurance. Basically, the first occurance wouldn't have an elapsed time because there wouldn't be anything previous to it for that day. Thats why I was attempting to use the DLookUp, this allowed me to pull the "Out Time of the previous Occurance and compare it to the "In Time" of next occurance. I was also getting this error; Syntax Error (missing operator) in query expression 'Min(In Time)'. "Randy Harris" wrote: You are correct that you would not be able to use the DLookup method described in that article, because your records won't be ordered by ID number. Frankly, I don't think much of that method anyway, your ID field would always have to be perfectly sequential. That could prove to be a real nightmare in the future. I was also getting a This is one of those problems that have many different solutions. One solution is to do the math in a query. For example: SELECT [IN-OutDates].ID, [IN-OutDates].InTime, [IN-OutDates].OutTime, DateDiff("n",[OutTime],DMin("InTime","In-OutDates","[InTime]#" & [OutTime] & "#")) AS Elapsed FROM [IN-OutDates] ORDER BY [IN-OutDates].InTime; ID InTime OutTime Elapsed 1 4/1/2005 9:00:00 AM 4/1/2005 9:30:00 AM 30 2 4/1/2005 10:00:00 AM 4/1/2005 10:30:00 AM 30 5 4/1/2005 11:00:00 AM 4/1/2005 11:30:00 AM 30 3 4/1/2005 12:00:00 PM 4/1/2005 12:30:00 PM 30 4 4/1/2005 1:00:00 PM 4/1/2005 1:30:00 PM I pasted the output from the query above, I hope it comes through. The calculation could also be done in the report, using the same sort of date math. Hope this helps. -- Randy Harris (tech at promail dot com) "Telobamipada" wrote in message news Randy, thanks for the reply. Here's an example: If you have a table with the 3 fields I have described and populate it with 4 records as shown below: ID In Time Out Time 1 4/1/2005 09:00 AM 4/1/2005 09:30 AM 2 4/1/2005 10:00 AM 4/1/2005 10:30 AM 3 4/1/2005 12:00 AM 4/1/2005 12:30 AM 4 4/1/2005 01:00 PM 4/1/2005 01:30 PM Now, using the DLookUp formula posted in Article 210504 for "Queries" everything is fine. I now need to add a record, say for the period 11:00 to 11:30. Now my table looks like this: ID In Time Out Time 1 4/1/2005 09:00 AM 4/1/2005 09:30 AM 2 4/1/2005 10:00 AM 4/1/2005 10:30 AM 3 4/1/2005 12:00 AM 4/1/2005 12:30 AM 4 4/1/2005 01:00 PM 4/1/2005 01:30 PM 5 4/1/2005 11:00 AM 4/1/2005 11:30 AM If I set up a macro to sort the table based on the "In Time" field when the record is saved from the form, the table looks like this: ID In Time Out Time 1 4/1/2005 09:00 AM 4/1/2005 09:30 AM 2 4/1/2005 10:00 AM 4/1/2005 10:30 AM 5 4/1/2005 11:00 AM 4/1/2005 11:30 AM 3 4/1/2005 12:00 AM 4/1/2005 12:30 AM 4 4/1/2005 01:00 PM 4/1/2005 01:30 PM The way the DLookUp formula works is based on the "ID" field which is an auto number and primary key. I've attempted to use the DLookUp with the "In Time" field and it is obviously not going to work... I simply need to set up a report which will show me the the elapsed time between the "Out Time" of the first event for that day and the "In Time" for the next event , in sequencial order. The query is set up to have the user enter a date they are after. The report when finished would look like this: Procedures for 4/1/2005 In Time Out Time Elapsed Time 4/1/2005 09:00 AM 4/1/2005 09:30 AM 30 4/1/2005 10:00 AM 4/1/2005 10:30 AM 30 4/1/2005 11:00 AM 4/1/2005 11:30 AM 30 4/1/2005 12:00 AM 4/1/2005 12:30 AM 30 4/1/2005 01:00 PM 4/1/2005 01:30 PM 30 I hope this helps you to understand what I'm trying to accomplish here, I am no expert by any stretch of the imagination! Since the DLookUp obviously will not work with a date field I thought I would add a field to my table and form such as "Patient Sequence Number" that the user would enter and base my DLookUp on that... I'm thinking there's a better way... Randy Harris" wrote: "Telobamipada" wrote in message ... While I found previous comments helpful, I still have an issue I can't seem to figure out. Say I have a table with an "ID" field, an "In Date/Time" and "Out Date/Time". I want to calculate the elapsed time between events ("Out Time" of the previous event to the "In Time" of the next event). Everything works fine until I forget to add a record and need to add a record with times which fit between 2 previous records (Based on the In Time). The new record is entered as the last record in the table and the DLookUp is now finding the "Out Time" of the previous record in the table but not the previous event (Based on the "In Time" and "Out Time" fields). What I want is for the DLookUp to find the "Out Time" of the previous event based on the "In Time" and not the "ID" field for cases like this where a record may be entered later. Any help would be greatly appreciated! -- If you can read this thank a teacher... If your reading it in english, thank a veteran! It's difficult to understand what you are trying to do. Suggest you post some sample data to provide a clearer explanation. BTW - your tag line is a nice sentiment. Given its content, however, you should correct the grammatical error in it. (my 2 cents worth for both suggestions) |
#6
|
|||
|
|||
No sir, I don't think so.
ID InTime OutTime Elapsed 1 4/1/2005 9:00:00 AM 4/1/2005 9:30:00 AM 40 2 4/1/2005 10:10:00 AM 4/1/2005 10:25:00 AM 35 5 4/1/2005 11:00:00 AM 4/1/2005 11:30:00 AM 45 3 4/1/2005 12:15:00 PM 4/1/2005 12:30:00 PM 60 4 4/1/2005 1:30:00 PM 4/1/2005 1:45:00 PM It is computing the time between each Out Time and the next Occurring In Time. That's what you said you wanted. Did I misunderstand the requirement? -- Randy Harris (tech at promail dot com) "Telobamipada" wrote in message ... Randy, One small problem. Your example is showing the elapsed time for each occurance. What I need to do is show the elapsed time from the "Out Time" of each occurance to the "In Time" of the next occurance. Basically, the first occurance wouldn't have an elapsed time because there wouldn't be anything previous to it for that day. Thats why I was attempting to use the DLookUp, this allowed me to pull the "Out Time of the previous Occurance and compare it to the "In Time" of next occurance. I was also getting this error; Syntax Error (missing operator) in query expression 'Min(In Time)'. "Randy Harris" wrote: You are correct that you would not be able to use the DLookup method described in that article, because your records won't be ordered by ID number. Frankly, I don't think much of that method anyway, your ID field would always have to be perfectly sequential. That could prove to be a real nightmare in the future. I was also getting a This is one of those problems that have many different solutions. One solution is to do the math in a query. For example: SELECT [IN-OutDates].ID, [IN-OutDates].InTime, [IN-OutDates].OutTime, DateDiff("n",[OutTime],DMin("InTime","In-OutDates","[InTime]#" & [OutTime] & "#")) AS Elapsed FROM [IN-OutDates] ORDER BY [IN-OutDates].InTime; ID InTime OutTime Elapsed 1 4/1/2005 9:00:00 AM 4/1/2005 9:30:00 AM 30 2 4/1/2005 10:00:00 AM 4/1/2005 10:30:00 AM 30 5 4/1/2005 11:00:00 AM 4/1/2005 11:30:00 AM 30 3 4/1/2005 12:00:00 PM 4/1/2005 12:30:00 PM 30 4 4/1/2005 1:00:00 PM 4/1/2005 1:30:00 PM I pasted the output from the query above, I hope it comes through. The calculation could also be done in the report, using the same sort of date math. Hope this helps. -- Randy Harris (tech at promail dot com) "Telobamipada" wrote in message news Randy, thanks for the reply. Here's an example: If you have a table with the 3 fields I have described and populate it with 4 records as shown below: ID In Time Out Time 1 4/1/2005 09:00 AM 4/1/2005 09:30 AM 2 4/1/2005 10:00 AM 4/1/2005 10:30 AM 3 4/1/2005 12:00 AM 4/1/2005 12:30 AM 4 4/1/2005 01:00 PM 4/1/2005 01:30 PM Now, using the DLookUp formula posted in Article 210504 for "Queries" everything is fine. I now need to add a record, say for the period 11:00 to 11:30. Now my table looks like this: ID In Time Out Time 1 4/1/2005 09:00 AM 4/1/2005 09:30 AM 2 4/1/2005 10:00 AM 4/1/2005 10:30 AM 3 4/1/2005 12:00 AM 4/1/2005 12:30 AM 4 4/1/2005 01:00 PM 4/1/2005 01:30 PM 5 4/1/2005 11:00 AM 4/1/2005 11:30 AM If I set up a macro to sort the table based on the "In Time" field when the record is saved from the form, the table looks like this: ID In Time Out Time 1 4/1/2005 09:00 AM 4/1/2005 09:30 AM 2 4/1/2005 10:00 AM 4/1/2005 10:30 AM 5 4/1/2005 11:00 AM 4/1/2005 11:30 AM 3 4/1/2005 12:00 AM 4/1/2005 12:30 AM 4 4/1/2005 01:00 PM 4/1/2005 01:30 PM The way the DLookUp formula works is based on the "ID" field which is an auto number and primary key. I've attempted to use the DLookUp with the "In Time" field and it is obviously not going to work... I simply need to set up a report which will show me the the elapsed time between the "Out Time" of the first event for that day and the "In Time" for the next event , in sequencial order. The query is set up to have the user enter a date they are after. The report when finished would look like this: Procedures for 4/1/2005 In Time Out Time Elapsed Time 4/1/2005 09:00 AM 4/1/2005 09:30 AM 30 4/1/2005 10:00 AM 4/1/2005 10:30 AM 30 4/1/2005 11:00 AM 4/1/2005 11:30 AM 30 4/1/2005 12:00 AM 4/1/2005 12:30 AM 30 4/1/2005 01:00 PM 4/1/2005 01:30 PM 30 I hope this helps you to understand what I'm trying to accomplish here, I am no expert by any stretch of the imagination! Since the DLookUp obviously will not work with a date field I thought I would add a field to my table and form such as "Patient Sequence Number" that the user would enter and base my DLookUp on that... I'm thinking there's a better way... Randy Harris" wrote: "Telobamipada" wrote in message ... While I found previous comments helpful, I still have an issue I can't seem to figure out. Say I have a table with an "ID" field, an "In Date/Time" and "Out Date/Time". I want to calculate the elapsed time between events ("Out Time" of the previous event to the "In Time" of the next event). Everything works fine until I forget to add a record and need to add a record with times which fit between 2 previous records (Based on the In Time). The new record is entered as the last record in the table and the DLookUp is now finding the "Out Time" of the previous record in the table but not the previous event (Based on the "In Time" and "Out Time" fields). What I want is for the DLookUp to find the "Out Time" of the previous event based on the "In Time" and not the "ID" field for cases like this where a record may be entered later. Any help would be greatly appreciated! -- If you can read this thank a teacher... If your reading it in english, thank a veteran! It's difficult to understand what you are trying to do. Suggest you post some sample data to provide a clearer explanation. BTW - your tag line is a nice sentiment. Given its content, however, you should correct the grammatical error in it. (my 2 cents worth for both suggestions) |
#7
|
|||
|
|||
Randy, Let's try it like this (I haven't had time to work on this for awhile)
I'm using MS Access 2002 My Table is named "Master Data" I have a query looking at the fields "ID", "Date", "OR", "Procedure", "SUrgeon", "In OR" and "Out OR"... There can be multiple records in this table for each day by "OR" (which is Operating Room). I am trying to set up a report that can show me the elapsed time from the "Out OR" time of each record to the "In OR" time of the next record so I can tell how much time passed between procedures for each OR. I do not want a calculation to be done if the "OR" field is different. In other words, I do not want to compare a time from Operating Room 1 with a time from Operating Room 2. I had my query designed to ask for which "OR" is to be used but it still does the calculation based on the next record by the "ID" field. If you can read this, thank a Teacher... If your reading it in english, thank a Veteran! "Randy Harris" wrote: No sir, I don't think so. ID InTime OutTime Elapsed 1 4/1/2005 9:00:00 AM 4/1/2005 9:30:00 AM 40 2 4/1/2005 10:10:00 AM 4/1/2005 10:25:00 AM 35 5 4/1/2005 11:00:00 AM 4/1/2005 11:30:00 AM 45 3 4/1/2005 12:15:00 PM 4/1/2005 12:30:00 PM 60 4 4/1/2005 1:30:00 PM 4/1/2005 1:45:00 PM It is computing the time between each Out Time and the next Occurring In Time. That's what you said you wanted. Did I misunderstand the requirement? -- Randy Harris (tech at promail dot com) "Telobamipada" wrote in message ... Randy, One small problem. Your example is showing the elapsed time for each occurance. What I need to do is show the elapsed time from the "Out Time" of each occurance to the "In Time" of the next occurance. Basically, the first occurance wouldn't have an elapsed time because there wouldn't be anything previous to it for that day. Thats why I was attempting to use the DLookUp, this allowed me to pull the "Out Time of the previous Occurance and compare it to the "In Time" of next occurance. I was also getting this error; Syntax Error (missing operator) in query expression 'Min(In Time)'. "Randy Harris" wrote: You are correct that you would not be able to use the DLookup method described in that article, because your records won't be ordered by ID number. Frankly, I don't think much of that method anyway, your ID field would always have to be perfectly sequential. That could prove to be a real nightmare in the future. I was also getting a This is one of those problems that have many different solutions. One solution is to do the math in a query. For example: SELECT [IN-OutDates].ID, [IN-OutDates].InTime, [IN-OutDates].OutTime, DateDiff("n",[OutTime],DMin("InTime","In-OutDates","[InTime]#" & [OutTime] & "#")) AS Elapsed FROM [IN-OutDates] ORDER BY [IN-OutDates].InTime; ID InTime OutTime Elapsed 1 4/1/2005 9:00:00 AM 4/1/2005 9:30:00 AM 30 2 4/1/2005 10:00:00 AM 4/1/2005 10:30:00 AM 30 5 4/1/2005 11:00:00 AM 4/1/2005 11:30:00 AM 30 3 4/1/2005 12:00:00 PM 4/1/2005 12:30:00 PM 30 4 4/1/2005 1:00:00 PM 4/1/2005 1:30:00 PM I pasted the output from the query above, I hope it comes through. The calculation could also be done in the report, using the same sort of date math. Hope this helps. -- Randy Harris (tech at promail dot com) "Telobamipada" wrote in message news Randy, thanks for the reply. Here's an example: If you have a table with the 3 fields I have described and populate it with 4 records as shown below: ID In Time Out Time 1 4/1/2005 09:00 AM 4/1/2005 09:30 AM 2 4/1/2005 10:00 AM 4/1/2005 10:30 AM 3 4/1/2005 12:00 AM 4/1/2005 12:30 AM 4 4/1/2005 01:00 PM 4/1/2005 01:30 PM Now, using the DLookUp formula posted in Article 210504 for "Queries" everything is fine. I now need to add a record, say for the period 11:00 to 11:30. Now my table looks like this: ID In Time Out Time 1 4/1/2005 09:00 AM 4/1/2005 09:30 AM 2 4/1/2005 10:00 AM 4/1/2005 10:30 AM 3 4/1/2005 12:00 AM 4/1/2005 12:30 AM 4 4/1/2005 01:00 PM 4/1/2005 01:30 PM 5 4/1/2005 11:00 AM 4/1/2005 11:30 AM If I set up a macro to sort the table based on the "In Time" field when the record is saved from the form, the table looks like this: ID In Time Out Time 1 4/1/2005 09:00 AM 4/1/2005 09:30 AM 2 4/1/2005 10:00 AM 4/1/2005 10:30 AM 5 4/1/2005 11:00 AM 4/1/2005 11:30 AM 3 4/1/2005 12:00 AM 4/1/2005 12:30 AM 4 4/1/2005 01:00 PM 4/1/2005 01:30 PM The way the DLookUp formula works is based on the "ID" field which is an auto number and primary key. I've attempted to use the DLookUp with the "In Time" field and it is obviously not going to work... I simply need to set up a report which will show me the the elapsed time between the "Out Time" of the first event for that day and the "In Time" for the next event , in sequencial order. The query is set up to have the user enter a date they are after. The report when finished would look like this: Procedures for 4/1/2005 In Time Out Time Elapsed Time 4/1/2005 09:00 AM 4/1/2005 09:30 AM 30 4/1/2005 10:00 AM 4/1/2005 10:30 AM 30 4/1/2005 11:00 AM 4/1/2005 11:30 AM 30 4/1/2005 12:00 AM 4/1/2005 12:30 AM 30 4/1/2005 01:00 PM 4/1/2005 01:30 PM 30 I hope this helps you to understand what I'm trying to accomplish here, I am no expert by any stretch of the imagination! Since the DLookUp obviously will not work with a date field I thought I would add a field to my table and form such as "Patient Sequence Number" that the user would enter and base my DLookUp on that... I'm thinking there's a better way... Randy Harris" wrote: "Telobamipada" wrote in message ... While I found previous comments helpful, I still have an issue I can't seem to figure out. Say I have a table with an "ID" field, an "In Date/Time" and "Out Date/Time". I want to calculate the elapsed time between events ("Out Time" of the previous event to the "In Time" of the next event). Everything works fine until I forget to add a record and need to add a record with times which fit between 2 previous records (Based on the In Time). The new record is entered as the last record in the table and the DLookUp is now finding the "Out Time" of the previous record in the table but not the previous event (Based on the "In Time" and "Out Time" fields). What I want is for the DLookUp to find the "Out Time" of the previous event based on the "In Time" and not the "ID" field for cases like this where a record may be entered later. Any help would be greatly appreciated! -- If you can read this thank a teacher... If your reading it in english, thank a veteran! It's difficult to understand what you are trying to do. Suggest you post some sample data to provide a clearer explanation. BTW - your tag line is a nice sentiment. Given its content, however, you should correct the grammatical error in it. (my 2 cents worth for both suggestions) |
#8
|
|||
|
|||
"Telobamipada" wrote in message ... Randy, Let's try it like this (I haven't had time to work on this for awhile) I'm using MS Access 2002 My Table is named "Master Data" I have a query looking at the fields "ID", "Date", "OR", "Procedure", "SUrgeon", "In OR" and "Out OR"... There can be multiple records in this table for each day by "OR" (which is Operating Room). I am trying to set up a report that can show me the elapsed time from the "Out OR" time of each record to the "In OR" time of the next record so I can tell how much time passed between procedures for each OR. I do not want a calculation to be done if the "OR" field is different. In other words, I do not want to compare a time from Operating Room 1 with a time from Operating Room 2. I had my query designed to ask for which "OR" is to be used but it still does the calculation based on the next record by the "ID" field. This is the first time you've mentioned OR. What do you mean by "I had my query designed to ask for which "OR" is to be used but it still does the calculation based on the next record by the "ID" field." It sounds like you are prompting for the OR, then generating a report for that OR. Is that correct? Is this report supposed to be for one OR or multiple ORs? |
#9
|
|||
|
|||
Randy, Yes... your correct. I apologize, I guess I'm not as smart as I
thought I was. I figured if I could create a simple scenario for you, take your logic and make my query work and I can't. To answer your question, yes, it's for multiple OR's but I fugured it may be easier if I set it up to look at one OR at a time, thus the prompt for the OR field. I really wanted to make this report group by OR and show me the elapsed time "between procedures". Obviously, The way I have it set up now is about half baked. I really appreciate your help, thanks. -- If you can read this, thank a Teacher... If your reading it in english, thank a Veteran! "Randy Harris" wrote: "Telobamipada" wrote in message ... Randy, Let's try it like this (I haven't had time to work on this for awhile) I'm using MS Access 2002 My Table is named "Master Data" I have a query looking at the fields "ID", "Date", "OR", "Procedure", "SUrgeon", "In OR" and "Out OR"... There can be multiple records in this table for each day by "OR" (which is Operating Room). I am trying to set up a report that can show me the elapsed time from the "Out OR" time of each record to the "In OR" time of the next record so I can tell how much time passed between procedures for each OR. I do not want a calculation to be done if the "OR" field is different. In other words, I do not want to compare a time from Operating Room 1 with a time from Operating Room 2. I had my query designed to ask for which "OR" is to be used but it still does the calculation based on the next record by the "ID" field. This is the first time you've mentioned OR. What do you mean by "I had my query designed to ask for which "OR" is to be used but it still does the calculation based on the next record by the "ID" field." It sounds like you are prompting for the OR, then generating a report for that OR. Is that correct? Is this report supposed to be for one OR or multiple ORs? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
The pivot tables 101 article says to use the "Create List" comman. | cgnoland03 | New Users | 2 | January 14th, 2005 11:39 PM |
KB Article Q128813 ACC2: How to Create a Stopwatch Form | Milton | Using Forms | 0 | December 8th, 2004 02:47 AM |
Troubleshoot the SendObject method | Nikky_Pickles | General Discussion | 1 | August 30th, 2004 07:40 AM |
Microsoft Knowledge Base Article - 832285Microsoft Knowledge BaseArticle - 832285 | Alain | Setting up and Configuration | 3 | January 18th, 2004 05:54 PM |