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
|
|||
|
|||
Querying Dates
Hello,
I am looking for some advice on how to tackle querying some data. I have two tables, table one with around 10,000 records per month and columns that give me (for each record) a start date, start time, end date and end time. Table two details a percentage for each hour and day of the week (so 24 hours * 7 days = 168 records). This has three columns, the day of the week (Mon to Sun), the hour (00:00 to 23:00) and a percentage. What I want to do is sum the total of the percentage column in table 2 where the day of the week and the hour of the week fall within the start date / start time and end date / end time. Any help would be greatly appreciated. Martin |
#2
|
|||
|
|||
Querying Dates
If table two only has day of the week, there can be 4 - 5 Mondays in a month,
how will you know which Monday to use? -- Build a little, test a little. "Martin" wrote: Hello, I am looking for some advice on how to tackle querying some data. I have two tables, table one with around 10,000 records per month and columns that give me (for each record) a start date, start time, end date and end time. Table two details a percentage for each hour and day of the week (so 24 hours * 7 days = 168 records). This has three columns, the day of the week (Mon to Sun), the hour (00:00 to 23:00) and a percentage. What I want to do is sum the total of the percentage column in table 2 where the day of the week and the hour of the week fall within the start date / start time and end date / end time. Any help would be greatly appreciated. Martin |
#3
|
|||
|
|||
Querying Dates
Sounds as if you need a non-equi join and at the same time you need to do some
data manipulation on the fields That are being used in the join. The first thing I would do would be to add another column and store the Day of week number Sun=1 to Sat=7 for each record. Then I would add another column that stores just the NUMBER of the hour. How do you handle partial hours? Ignore them, round them to the nearest whole hour, or use partial hours to get partial credit. Do you have records that overlap days? I assume so since you are recording a start date and end date Do you have records that encompass more than one week - event starts on Friday and runs until Tuesday of the following week? If not the problem becomes a bit easier to solve. Do you have records that encompass multiple weeks? Jan 1 to Jan 31 for instance. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Martin wrote: Hello, I am looking for some advice on how to tackle querying some data. I have two tables, table one with around 10,000 records per month and columns that give me (for each record) a start date, start time, end date and end time. Table two details a percentage for each hour and day of the week (so 24 hours * 7 days = 168 records). This has three columns, the day of the week (Mon to Sun), the hour (00:00 to 23:00) and a percentage. What I want to do is sum the total of the percentage column in table 2 where the day of the week and the hour of the week fall within the start date / start time and end date / end time. Any help would be greatly appreciated. Martin |
#4
|
|||
|
|||
Querying Dates
Thanks for the reply John.
Yes I do have records that could start on a Friday and end on the Tuesday of the next week. Table one with the data of start date/time and end date/time is something I am sent and cannot change however the second table with the percentages is something I have created to try to help overcome the problem but this can be changed if that helps? "John Spencer" wrote: Sounds as if you need a non-equi join and at the same time you need to do some data manipulation on the fields That are being used in the join. The first thing I would do would be to add another column and store the Day of week number Sun=1 to Sat=7 for each record. Then I would add another column that stores just the NUMBER of the hour. How do you handle partial hours? Ignore them, round them to the nearest whole hour, or use partial hours to get partial credit. Do you have records that overlap days? I assume so since you are recording a start date and end date Do you have records that encompass more than one week - event starts on Friday and runs until Tuesday of the following week? If not the problem becomes a bit easier to solve. Do you have records that encompass multiple weeks? Jan 1 to Jan 31 for instance. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Martin wrote: Hello, I am looking for some advice on how to tackle querying some data. I have two tables, table one with around 10,000 records per month and columns that give me (for each record) a start date, start time, end date and end time. Table two details a percentage for each hour and day of the week (so 24 hours * 7 days = 168 records). This has three columns, the day of the week (Mon to Sun), the hour (00:00 to 23:00) and a percentage. What I want to do is sum the total of the percentage column in table 2 where the day of the week and the hour of the week fall within the start date / start time and end date / end time. Any help would be greatly appreciated. Martin . |
#5
|
|||
|
|||
Querying Dates
I wish you had answered all the questions, but that should be enough for me to
start thinking about a solution. I would like to do this using a query, but it may be necessary to create a VBA function. It would certainly be easier to use VBA to solve this, but the speed might not be acceptable. I will try to get back to you by Sunday. I am a little stretched for time at this point. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Martin wrote: Thanks for the reply John. Yes I do have records that could start on a Friday and end on the Tuesday of the next week. Table one with the data of start date/time and end date/time is something I am sent and cannot change however the second table with the percentages is something I have created to try to help overcome the problem but this can be changed if that helps? "John Spencer" wrote: Sounds as if you need a non-equi join and at the same time you need to do some data manipulation on the fields That are being used in the join. The first thing I would do would be to add another column and store the Day of week number Sun=1 to Sat=7 for each record. Then I would add another column that stores just the NUMBER of the hour. How do you handle partial hours? Ignore them, round them to the nearest whole hour, or use partial hours to get partial credit. Do you have records that overlap days? I assume so since you are recording a start date and end date Do you have records that encompass more than one week - event starts on Friday and runs until Tuesday of the following week? If not the problem becomes a bit easier to solve. Do you have records that encompass multiple weeks? Jan 1 to Jan 31 for instance. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Martin wrote: Hello, I am looking for some advice on how to tackle querying some data. I have two tables, table one with around 10,000 records per month and columns that give me (for each record) a start date, start time, end date and end time. Table two details a percentage for each hour and day of the week (so 24 hours * 7 days = 168 records). This has three columns, the day of the week (Mon to Sun), the hour (00:00 to 23:00) and a percentage. What I want to do is sum the total of the percentage column in table 2 where the day of the week and the hour of the week fall within the start date / start time and end date / end time. Any help would be greatly appreciated. Martin . |
#6
|
|||
|
|||
Querying Dates
I haven't come up with a good way to do this with just a query. I've put
together a function and tested it minimally. This assumes that your lookup table has a weekday number (1 to 7) and an hour number (0 to 23). Also this gives full credit for partial hours. '================= Minimally tested code follows ================ Function fCalcSum(dStart, tStart, dEnd, tEnd) Dim dblResult As Double Dim StrSQL As String Static WeekTotal As Double If WeekTotal = 0 Then WeekTotal = dSum("TheValue", "ValuesTable") End If If IsDate(dStart) And IsDate(tStart) _ And IsDate(dEnd) And IsDate(tEnd) Then If Weekday(dStart) = Weekday(dEnd) Then StrSQL = " SELECT SUM(TheValue)" & _ " FROM ValuesTable" & _ " WHERE DayNumber*100 + HourNumber = " & _ Weekday(dStart) * 100 + Hour(tStart) & _ " AND DayNumber*100 + HourNumber = " & _ Weekday(dEnd) * 100 + Hour(tEnd) ElseIf Weekday(dStart) Weekday(dEnd) Then StrSQL = " SELECT SUM(TheValue)" & _ " FROM ValuesTable" & _ " WHERE DayNumber*100 + HourNumber = " & _ Weekday(dEnd) * 100 + Hour(tEnd) & _ " OR DayNumber*100 + HourNumber = " & _ Weekday(dStart) * 100 + Hour(tStart) End If 'Get the hours that are not entire weeks dblResult = Nz(CurrentDb().OpenRecordset(StrSQL).Fields(0), 0) 'Get hours for entire week(s) dblResult = dblResult + (DateDiff("d", dStart, dEnd) \ 7) * WeekTotal 'Adjust the total for counting the same hour twice in the above SQL dblResult = dblResult - 1 'Return the resulting calculation fCalcSum = dblResult Else fCalcSum = Null End If End Function John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County John Spencer wrote: I wish you had answered all the questions, but that should be enough for me to start thinking about a solution. I would like to do this using a query, but it may be necessary to create a VBA function. It would certainly be easier to use VBA to solve this, but the speed might not be acceptable. I will try to get back to you by Sunday. I am a little stretched for time at this point. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Martin wrote: Thanks for the reply John. Yes I do have records that could start on a Friday and end on the Tuesday of the next week. Table one with the data of start date/time and end date/time is something I am sent and cannot change however the second table with the percentages is something I have created to try to help overcome the problem but this can be changed if that helps? "John Spencer" wrote: Sounds as if you need a non-equi join and at the same time you need to do some data manipulation on the fields That are being used in the join. The first thing I would do would be to add another column and store the Day of week number Sun=1 to Sat=7 for each record. Then I would add another column that stores just the NUMBER of the hour. How do you handle partial hours? Ignore them, round them to the nearest whole hour, or use partial hours to get partial credit. Do you have records that overlap days? I assume so since you are recording a start date and end date Do you have records that encompass more than one week - event starts on Friday and runs until Tuesday of the following week? If not the problem becomes a bit easier to solve. Do you have records that encompass multiple weeks? Jan 1 to Jan 31 for instance. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Martin wrote: Hello, I am looking for some advice on how to tackle querying some data. I have two tables, table one with around 10,000 records per month and columns that give me (for each record) a start date, start time, end date and end time. Table two details a percentage for each hour and day of the week (so 24 hours * 7 days = 168 records). This has three columns, the day of the week (Mon to Sun), the hour (00:00 to 23:00) and a percentage. What I want to do is sum the total of the percentage column in table 2 where the day of the week and the hour of the week fall within the start date / start time and end date / end time. Any help would be greatly appreciated. Martin . |
Thread Tools | |
Display Modes | |
|
|