Array help...not even sure what to ask!

  • Thread starter Thread starter Dale
  • Start date Start date
D

Dale

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.
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
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.
 
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 said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top