 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / April 2005MSAccess Table: subtract 2 rows (get time difference [minutes] be.
Using Access XP on Win2k. Note: I am NOT a programmer!!! I am mildly familiar with programming (trace memories of java from 4 years ago) and not an avid user of Access. My query is simple- I want an access TABLE to automatically calculate the time difference between 2 times. ID Time Start Time End Elapsed Time 1 1:30 2:10 ?????<--- 2 12:24 12:49 3 10:46 11:55 ?????: This is where I want access to automatically fill in the difference in minutes. Is this possble to have in a Table? Or will it have to be in a form? I am still new to Access having only used tables, so I do not really know the difference between the 2 yet. Currently the table design is as such: Field Name Data Type Date Date/Time Time Start Date/Time Time End Date/Time and my other data follows... Thank you for your time. What you're asking for isn't possible using an Access table, but then, it isn't required. (Relational Database Theory explicitly states that no field in a table should be completely derivable from other fields in the same row in the table). What you can do, though, is just store Time Start and Time End in your table. Then, create a query that has the three fields in it, plus add a fourth computed field to the query. You do this by typing in an empty column on the row labeled "Fields" in the query builder. In your case, you'd want to type: ElapsedTime: DateDiff("m", [Time Start], [Time End]) Save the query, and use it wherever you would otherwise have used the table.  SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Using Access XP on Win2k. > Note: I am NOT a programmer!!! I am mildly familiar with programming [quoted text clipped - 23 lines] > > Thank you for your time. If I may ask a related question Douglas, is it legit to then write that computed value back to an empty field (reserved for the job of course) in the same table, for the sake of posterity or should every subsequent report calculate it each run? To be honest I don't see why derivation from within would always be a bad thing although I can see some cases where it could be, is there a simple explanation for that? I'm thinking of say joining firstname and lastname into one field for indexing, allowing duplicates of course in case there are two charlietames or whatever. Of course I suppose one should do this in the entry form if at all, not in the tables. Did I just answer my own question? Charlie > What you're asking for isn't possible using an Access table, but then, it > isn't required. (Relational Database Theory explicitly states that no [quoted text clipped - 40 lines] >> >> Thank you for your time. You can index on multiple fields, eg Firstname + Lastname without having a specific field (FirstnameLastname) > If I may ask a related question Douglas, is it legit to then write that > computed value back to an empty field (reserved for the job of course) in [quoted text clipped - 54 lines] > >> > >> Thank you for your time. It's not recommended that you store values that can be easily computed unless there's a legitimate need for them to be calculated (i.e.: that you have a need to be able to show what the value calculated at the time was, in case it changes). I don't see this example falling into that category. It's generally faster to do an arithmetic calculation than it is to retrieve the field from the database. The real reason for not storing the value, in my opinion, though, is to ensure that you don't accidentally change one of the numbers involved in the calculation and forget to recalculate the other value(s). In that case, how do you know which number is correct if, for example, you'd changed Time End on row 1 from 2:10 to 2:20, but Elapsed Time is still 40 minutes? And, as was pointed out elsewhere in this thread, there's definitely no need to join fields for the purposes of indexing. An index in Access can contain up to 10 separate fields. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > If I may ask a related question Douglas, is it legit to then write that > computed value back to an empty field (reserved for the job of course) in [quoted text clipped - 56 lines] >>> >>> Thank you for your time. Well that's a bummer. Thank you for such a quick response. I"m wondering, then, is this something that can be done w/ Excel? I tried adding/subtracting times and it didn't quite work out.... Thanks much. Cheers, Henry > It's not recommended that you store values that can be easily computed > unless there's a legitimate need for them to be calculated (i.e.: that you [quoted text clipped - 73 lines] > >>> > >>> Thank you for your time. Why not create a query as I suggested? SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Well that's a bummer. Thank you for such a quick response. I"m > wondering, [quoted text clipped - 101 lines] >> >>> >> >>> Thank you for your time. Answering your post Douglas but thanks to DL as well. Yes I do see the point however with systems I have used before the indexing situation would have been valid as it kept a separate index file for each field indexed, but this was back when 25MHz was considered fast enough and the whole of Windows would fit in what is now considered an "Update" :) Thanks again, these common knowledge things (to you guys) are invaluable to anyone not familiar with programs. Charlie > It's not recommended that you store values that can be easily computed > unless there's a legitimate need for them to be calculated (i.e.: that you [quoted text clipped - 74 lines] >>>> >>>> Thank you for your time. While you received a lot of valuable instruction in database development, I don't think that your original question was actually ever answered fully. You can do what you are asking simply by creating a query based on your table. In your query put all the fields you want plus one for your Elapsed time. Use the Datediff() formula for it. Then you can base your form off of this query. Now you have your elapsed time AND you're obeying all database development rules. > Using Access XP on Win2k. > Note: I am NOT a programmer!!! I am mildly familiar with programming (trace [quoted text clipped - 21 lines] > > Thank you for your time. Isn't that what I said in my post? SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > While you received a lot of valuable instruction in database development, > I [quoted text clipped - 37 lines] >> >> Thank you for your time. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.