Addition Operation with Access DB Column
-
I’ve got a need to “add” an entire column of times (2 hrs, 30 min, etc.) and curious as to what the best starting point is. I want to calculate the total time of a column by means of a Button Click. I will need to do this per three different criteria for testing time. I appreciate the advice.
-
I’ve got a need to “add” an entire column of times (2 hrs, 30 min, etc.) and curious as to what the best starting point is. I want to calculate the total time of a column by means of a Button Click. I will need to do this per three different criteria for testing time. I appreciate the advice.
You could just store the data in the column as an numeric value. For example, you can format the current time like this:
CInt(Now.ToString("hhmm"))and now you have an integer value. There are also many other options for formating the time (You will want to research that, because I can't remember if hh or HH is military time and that will make a difference when you are converting the integer back into a DateTime object) But I don't understand what you mean by calculating the total time of a column. You must mean that you want to get the time spans between the times right? Remember that there is a difference between a TimeSpan and a DateTime object. Please provide more information if you need further help. I hope this helps. -
You could just store the data in the column as an numeric value. For example, you can format the current time like this:
CInt(Now.ToString("hhmm"))and now you have an integer value. There are also many other options for formating the time (You will want to research that, because I can't remember if hh or HH is military time and that will make a difference when you are converting the integer back into a DateTime object) But I don't understand what you mean by calculating the total time of a column. You must mean that you want to get the time spans between the times right? Remember that there is a difference between a TimeSpan and a DateTime object. Please provide more information if you need further help. I hope this helps.I knew what "I" was talking about and wanted; I didn't convey the actuals. Sorry. I'm thinking time as in duration or test period. I apologize for the confusion on my part. I have a column in an Access Db that states how long a test is running, 2 hours or 30 minutes and so on for 3 three different tests. Test A,B,C are in column 1 with the test duration in column 6. Example: Test 1 TestA 2.5 Hrs TestA 30 Min Test 2 TestB 20 Min TestB 1.5 Hrs Test 3 TestC 1 Hr TestC 45 min I need a way to calculate the entire testing times for Test 1, Test 2 and Test 3 individually. I am planning on having a ComboBox with these Tests 1,2,3 and a Button to Calculate the times associated with each test that is driven from the Access Db, per test. Hope that makes better sense.
-
I knew what "I" was talking about and wanted; I didn't convey the actuals. Sorry. I'm thinking time as in duration or test period. I apologize for the confusion on my part. I have a column in an Access Db that states how long a test is running, 2 hours or 30 minutes and so on for 3 three different tests. Test A,B,C are in column 1 with the test duration in column 6. Example: Test 1 TestA 2.5 Hrs TestA 30 Min Test 2 TestB 20 Min TestB 1.5 Hrs Test 3 TestC 1 Hr TestC 45 min I need a way to calculate the entire testing times for Test 1, Test 2 and Test 3 individually. I am planning on having a ComboBox with these Tests 1,2,3 and a Button to Calculate the times associated with each test that is driven from the Access Db, per test. Hope that makes better sense.
I'm still a bit confused. What is the data in your table going to look like? I think I would suggest to store the testing durations as an integer value of minutes. So 2.5 hours would be stored as 150. Then you can convert them to display in whatever format you want later. So, your initial question is still escaping me. If you are asking how to get the sum of the durations for each Test...it would be like this:
SELECT testField, SUM(duration) FROM table GROUP BY testFieldIf you are asking how to get the individual durations for a specific test it would be like this:SELECT durationField FROM table WHERE testField='Test1'If this is not what you are asking for, could you please explain the situation a little better. What is the difference between Test 1,2,3 and Test A,B,C?...what will your table look like with data in it?...what is it that your program is actually supposed to accomplish? Hope this helps. -
I'm still a bit confused. What is the data in your table going to look like? I think I would suggest to store the testing durations as an integer value of minutes. So 2.5 hours would be stored as 150. Then you can convert them to display in whatever format you want later. So, your initial question is still escaping me. If you are asking how to get the sum of the durations for each Test...it would be like this:
SELECT testField, SUM(duration) FROM table GROUP BY testFieldIf you are asking how to get the individual durations for a specific test it would be like this:SELECT durationField FROM table WHERE testField='Test1'If this is not what you are asking for, could you please explain the situation a little better. What is the difference between Test 1,2,3 and Test A,B,C?...what will your table look like with data in it?...what is it that your program is actually supposed to accomplish? Hope this helps.Sorry if I’m not explaining this very well. I have under TestA, several test variations that each have a certain duration allotted to them. My need is to somehow calculate the entire time or duration for all tests in Test A and so on. Example: Test A - #1 takes 1 hr. Test A - #2 takes 2 hrs. Test A - #3 takes 3 hrs. Button Click to add the entire Test A test times would equal 6 hrs. The differences in Test A,B,C are all different tests. The 1,2,3 are tests performed for each Test A,B,C. Does that make sense? This app is supposed to calculate the total duration of all testing to allocate personnel and resources effectively. Doesn’t seem logical to me, but evidently there’s a need for it.
-
Sorry if I’m not explaining this very well. I have under TestA, several test variations that each have a certain duration allotted to them. My need is to somehow calculate the entire time or duration for all tests in Test A and so on. Example: Test A - #1 takes 1 hr. Test A - #2 takes 2 hrs. Test A - #3 takes 3 hrs. Button Click to add the entire Test A test times would equal 6 hrs. The differences in Test A,B,C are all different tests. The 1,2,3 are tests performed for each Test A,B,C. Does that make sense? This app is supposed to calculate the total duration of all testing to allocate personnel and resources effectively. Doesn’t seem logical to me, but evidently there’s a need for it.
That's okay...It's always hard to know what information is important when asking a question. But it seems to make more sense to me now. I think this is what you are looking for:
SELECT SUM(durationField) FROM table WHERE testField='TestA'This will return the sum of durations for TestA. Hope this works for you! -
That's okay...It's always hard to know what information is important when asking a question. But it seems to make more sense to me now. I think this is what you are looking for:
SELECT SUM(durationField) FROM table WHERE testField='TestA'This will return the sum of durations for TestA. Hope this works for you!I'll give that a try and post a verdict. Thanks again for helping me and working through my information. I appreciate the help. Thanks again.