Prev: getting data from date range
Next: Action Query (Append) didn't add records due to key violations.
From: Martin on 18 Mar 2010 12:09
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
From: KARL DEWEY on 18 Mar 2010 13:12
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
From: John Spencer on 18 Mar 2010 19:59
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
From: Martin on 19 Mar 2010 04:11
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
> .
>
From: John Spencer on 19 Mar 2010 13:25
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
>> .
>>
 |  Next  |  Last
Pages: 1 2
Prev: getting data from date range
Next: Action Query (Append) didn't add records due to key violations.