I have a table that captures the occurence of an event by date and hour in a
24 hour period. Columns are named
[0100], [0200],[0300]....[2400].

I want to capture the sum of the events by days, evenings and nights where
"days" = ([0800]+[0900}...+[1500])
"evenings" = ([1700]+[1800]...+[2400])

I'm trying to create a function for each of "days", "evenings" etc,
I didn't want to have to declare each variable everytime I invoke the
function from a query, thinking an array is the way to go? I'm hopelessly
lost...can an array work with columns or only rows? How do I reference the
column I want in the array?

Function DaySum() As Double
Dim Days
Days = Array("[0800]", "[0900]"...."[1500]")
DaySum = Days(1) + Days(2)....+ Days(8)
End Function

If you're not already rolling on the floor laughing at this poor
attempt....thanks for any help or pointers.

Is there any chance that you could normalize your table structure? Having
times as column names isn't ideal for querying data.

--
Duane Hookom
MS Access MVP


"Dale" <(E-Mail Removed)> wrote in message
news:O7xgZ%(E-Mail Removed)...
>I have a table that captures the occurence of an event by date and hour in
>a 24 hour period. Columns are named
> [0100], [0200],[0300]....[2400].
>
> I want to capture the sum of the events by days, evenings and nights where
> "days" = ([0800]+[0900}...+[1500])
> "evenings" = ([1700]+[1800]...+[2400])
>
> I'm trying to create a function for each of "days", "evenings" etc,
> I didn't want to have to declare each variable everytime I invoke the
> function from a query, thinking an array is the way to go? I'm hopelessly
> lost...can an array work with columns or only rows? How do I reference
> the column I want in the array?
>
> Function DaySum() As Double
> Dim Days
> Days = Array("[0800]", "[0900]"...."[1500]")
> DaySum = Days(1) + Days(2)....+ Days(8)
> End Function
>
> If you're not already rolling on the floor laughing at this poor
> attempt....thanks for any help or pointers.
>
>
>
>
>

I knew you were going to say that!! Unfortunately this is the way the data
is rec'd as an extract from another source.
I take it that means an array will not work with the data in this format.


"Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message
news:%(E-Mail Removed)...
> Is there any chance that you could normalize your table structure? Having
> times as column names isn't ideal for querying data.
>
> --
> Duane Hookom
> MS Access MVP
>
>
> "Dale" <(E-Mail Removed)> wrote in message
> news:O7xgZ%(E-Mail Removed)...
>>I have a table that captures the occurence of an event by date and hour in
>>a 24 hour period. Columns are named
>> [0100], [0200],[0300]....[2400].
>>
>> I want to capture the sum of the events by days, evenings and nights
>> where
>> "days" = ([0800]+[0900}...+[1500])
>> "evenings" = ([1700]+[1800]...+[2400])
>>
>> I'm trying to create a function for each of "days", "evenings" etc,
>> I didn't want to have to declare each variable everytime I invoke the
>> function from a query, thinking an array is the way to go? I'm
>> hopelessly lost...can an array work with columns or only rows? How do I
>> reference the column I want in the array?
>>
>> Function DaySum() As Double
>> Dim Days
>> Days = Array("[0800]", "[0900]"...."[1500]")
>> DaySum = Days(1) + Days(2)....+ Days(8)
>> End Function
>>
>> If you're not already rolling on the floor laughing at this poor
>> attempt....thanks for any help or pointers.
>>
>>
>>
>>
>>
>
>

Dale,

An array will not work. You need to do two things:
1. Create a new table that looks like:
TblEventOccurence
EventOccurenceID
OccurenceDate
OccurrenceTime
Then you need to append your existing data one column at a time into this
table. You will then be able to easily do the query you want.
2. Create an intermediary database between the source of your data and your
database. You can then import your data into this database in the form of
each hour in a separate field like you now have in your database. Then you
need to create an export routine in this database that exports the data one
column at a time into the new table in your database.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(E-Mail Removed)
www.pcdatasheet.com

"Dale" <(E-Mail Removed)> wrote in message
news:O7xgZ#(E-Mail Removed)...
> I have a table that captures the occurence of an event by date and hour in
a
> 24 hour period. Columns are named
> [0100], [0200],[0300]....[2400].
>
> I want to capture the sum of the events by days, evenings and nights where
> "days" = ([0800]+[0900}...+[1500])
> "evenings" = ([1700]+[1800]...+[2400])
>
> I'm trying to create a function for each of "days", "evenings" etc,
> I didn't want to have to declare each variable everytime I invoke the
> function from a query, thinking an array is the way to go? I'm hopelessly
> lost...can an array work with columns or only rows? How do I reference
the
> column I want in the array?
>
> Function DaySum() As Double
> Dim Days
> Days = Array("[0800]", "[0900]"...."[1500]")
> DaySum = Days(1) + Days(2)....+ Days(8)
> End Function
>
> If you're not already rolling on the floor laughing at this poor
> attempt....thanks for any help or pointers.
>
>
>
>
>

I would normalize the data into tables. It shouldn't make any difference how
the other source is structured.

--
Duane Hookom
MS Access MVP


"Dale" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I knew you were going to say that!! Unfortunately this is the way the data
>is rec'd as an extract from another source.
> I take it that means an array will not work with the data in this format.
>
>
> "Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message
> news:%(E-Mail Removed)...
>> Is there any chance that you could normalize your table structure? Having
>> times as column names isn't ideal for querying data.
>>
>> --
>> Duane Hookom
>> MS Access MVP
>>
>>
>> "Dale" <(E-Mail Removed)> wrote in message
>> news:O7xgZ%(E-Mail Removed)...
>>>I have a table that captures the occurence of an event by date and hour
>>>in a 24 hour period. Columns are named
>>> [0100], [0200],[0300]....[2400].
>>>
>>> I want to capture the sum of the events by days, evenings and nights
>>> where
>>> "days" = ([0800]+[0900}...+[1500])
>>> "evenings" = ([1700]+[1800]...+[2400])
>>>
>>> I'm trying to create a function for each of "days", "evenings" etc,
>>> I didn't want to have to declare each variable everytime I invoke the
>>> function from a query, thinking an array is the way to go? I'm
>>> hopelessly lost...can an array work with columns or only rows? How do I
>>> reference the column I want in the array?
>>>
>>> Function DaySum() As Double
>>> Dim Days
>>> Days = Array("[0800]", "[0900]"...."[1500]")
>>> DaySum = Days(1) + Days(2)....+ Days(8)
>>> End Function
>>>
>>> If you're not already rolling on the floor laughing at this poor
>>> attempt....thanks for any help or pointers.
>>>
>>>
>>>
>>>
>>>
>>
>>
>
>