Integrating "Blocks" of Constants

[croy]

GIVEN:

A spreadsheet containing a dozen "blocks" of constants data.

Each block consists of 16 columns and 12 rows of constants,
and each block relates to a named, geographical location
(already present in an existing db).

The columns represent selected hours of the day (from 0500
to 2000), in text format (numbers as text).

The rows represent the twelve months of the year in number
format. The Constants are similar to "11.96", without the
quotation marks.


TASK:

Quickly (what else is new?) make these constants available
for data analysis in an Existing Access database, and set up
a query to average the constants that match a) a certain
"block" of the constants; b) the month of the year; c) given
a Start Time and a Finish Time, whatever constants would be
included where a half-hour or more would be included in the
time span. Like (assume constants block 1 and first month
of the year):

Start Time = 0635
Finis Time = 1125

Then include hours 0700, 0800, 0900, 1000.

Then lookup the constants that match the criteria,
and produce an average (mean) constant for copying
to another (yuk!) spreadsheet that the boss uses to
do further analysis.


MY THOUGHTS:


#1

Probably the pro's would say, put all the constants in a
single table, where:

column 1: Autonumber Id
Column 2: Foreign key to relate to Block
Column 3: Month of year
Column 4: Hour of the day
Column 5: Constant

The first question with this approach is how to get the data
morphed into that configuration? At my skill-level, all I
can think of is manually horsing the data around (blah! And
I'd probably ruin it, in the process).


#2

An alternative method would be to leave the structure of the
blocks as they are, and simply string them all into a single
table.

This would be much quicker to set up, but I'm not sure about
ease of use later on.


#3

Yet another alternative would be to make a separate table
for each block. That would probably be the easiest way to
table the data, but I doubt that would be easy to live with
after making the tables.


YOUR THOUGHTS HERE:

(pretty-please...)

 

[Dale Fye]

Croy,

Access is a database, not a spreadsheet, so you really need to change your
data structure to do what you want. The table you need should have 3 columns
(MonthField, BlockTimeStart, and ConstantValue). If you already have the
data in the 12x16 table in your database, you can create a normalization
query that will reformat it into the appropiate format (since you didn't
provide the names of your columns, I'll make something up). It would look
something like:

SELECT MonthField,
TimeValue("5:00:00") as BlockTimeStart,
[Block-0500] as ConstantValue
FROM yourTable
UNION ALL
SELECT MonthField,
TimeValue("6:00:00") as BlockTimeStart,
[Block-0600] as ConstantValue
FROM yourTable
UNION ALL
SELECT MonthField,
TimeValue("7:00:00") as BlockTimeStart,
[Block-0700] as ConstantValue
FROM yourTable
UNION ALL
....
SELECT MonthField,
TimeValue("22:00:00") as BlockTimeStart,
[Block-2200] as ConstantValue
FROM yourTable

Once you get this working, post back with your field names and we can
continue.

Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[croy]

Croy,

Access is a database, not a spreadsheet, so you really need to change your
data structure to do what you want. The table you need should have 3 columns
(MonthField, BlockTimeStart, and ConstantValue). If you already have the
data in the 12x16 table in your database, you can create a normalization
query that will reformat it into the appropiate format (since you didn't
provide the names of your columns, I'll make something up). It would look
something like:

SELECT MonthField,
TimeValue("5:00:00") as BlockTimeStart,
[Block-0500] as ConstantValue
FROM yourTable
UNION ALL
SELECT MonthField,
TimeValue("6:00:00") as BlockTimeStart,
[Block-0600] as ConstantValue
FROM yourTable
UNION ALL
SELECT MonthField,
TimeValue("7:00:00") as BlockTimeStart,
[Block-0700] as ConstantValue
FROM yourTable
UNION ALL
...
SELECT MonthField,
TimeValue("22:00:00") as BlockTimeStart,
[Block-2200] as ConstantValue
FROM yourTable

Once you get this working, post back with your field names and we can
continue.

Thanks for the reply, Dale.

I'm just now getting back to this after some other putting
out some unrelated fires.

Since I first posted about this, the boss informed me that
there are only four unique blocks of constants, the others
are repetition! Thanks boss.

So I just manually horsed the data around to give me the
three-column table design you recommended.

I've got a "join" table set up to link things up, and just
have to populate that with the proper join information. But
I'm having a very strange problem using an append query to
do the populating. I've posted a message about that over in
the queries group ("Append Query Baffling Me!").

Thanks for your help.


Oh, I have to ask what does, "Don''t forget to rate the post
if it was helpful!" mean? Is this something for
web-posters?

 

[croy]

Croy,

Access is a database, not a spreadsheet, so you really need to change your
data structure to do what you want. The table you need should have 3 columns
(MonthField, BlockTimeStart, and ConstantValue). If you already have the
data in the 12x16 table in your database, you can create a normalization
query that will reformat it into the appropiate format (since you didn't
provide the names of your columns, I'll make something up). It would look
something like:

SELECT MonthField,
TimeValue("5:00:00") as BlockTimeStart,
[Block-0500] as ConstantValue
FROM yourTable
UNION ALL
SELECT MonthField,
TimeValue("6:00:00") as BlockTimeStart,
[Block-0600] as ConstantValue
FROM yourTable
UNION ALL
SELECT MonthField,
TimeValue("7:00:00") as BlockTimeStart,
[Block-0700] as ConstantValue
FROM yourTable
UNION ALL
...
SELECT MonthField,
TimeValue("22:00:00") as BlockTimeStart,
[Block-2200] as ConstantValue
FROM yourTable

Once you get this working, post back with your field names and we can
continue.

Thanks for the reply, Dale.

I'm just now getting back to this after some other putting
out some unrelated fires.

Since I first posted about this, the boss informed me that
there are only four unique blocks of constants, the others
are repetition! Thanks boss.

So I just manually horsed the data around to give me the
three-column table design you recommended.

I've got a "join" table set up to link things up, and just
have to populate that with the proper join information. But
I'm having a very strange problem using an append query to
do the populating. I've posted a message about that over in
the queries group ("Append Query Baffling Me!").

Thanks for your help.


Oh, I have to ask what does, "Don''t forget to rate the post
if it was helpful!" mean? Is this something for
web-posters?

Ok, the query problem is sorted.

Now back to this part of my original post:

set up
a query to average the constants that match a) a certain
"block" of the constants; b) the month of the year; c) given
a Start Time and a Finish Time, whatever constants would be
included where a half-hour or more would be included in the
time span. Like (assume constants block 1 and first month
of the year):

Start Time = 0635
Finis Time = 1125

Then include hours 0700, 0800, 0900, 1000.

Then lookup the constants that match the criteria,
and produce an average (mean) constant for copying
to another (yuk!) spreadsheet that the boss uses to
do further analysis.

 

[Dale Fye]

Oh, I have to ask what does, "Don''t forget to rate the post if it was
helpful!" mean? Is this something for web-posters?

Yes. I have no access to Outlook Express from work, so I use Microsofts Web
based newsgroup site
(https://www.microsoft.com/office/community/en-us/default.mspx). On that
site, they have a place for you (the original poster) to indicate whether a
response answered your question. Also, anyone can indicate whether the post
was helpful. Microsoft keeps track of # of responses and # of questions
answered or helpful posts, and provides a rating once you get to 50+ helpful
responses.


--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Croy,

Access is a database, not a spreadsheet, so you really need to change your
data structure to do what you want. The table you need should have 3 columns
(MonthField, BlockTimeStart, and ConstantValue). If you already have the
data in the 12x16 table in your database, you can create a normalization
query that will reformat it into the appropiate format (since you didn't
provide the names of your columns, I'll make something up). It would look
something like:

SELECT MonthField,
TimeValue("5:00:00") as BlockTimeStart,
[Block-0500] as ConstantValue
FROM yourTable
UNION ALL
SELECT MonthField,
TimeValue("6:00:00") as BlockTimeStart,
[Block-0600] as ConstantValue
FROM yourTable
UNION ALL
SELECT MonthField,
TimeValue("7:00:00") as BlockTimeStart,
[Block-0700] as ConstantValue
FROM yourTable
UNION ALL
...
SELECT MonthField,
TimeValue("22:00:00") as BlockTimeStart,
[Block-2200] as ConstantValue
FROM yourTable

Once you get this working, post back with your field names and we can
continue.

Thanks for the reply, Dale.

I'm just now getting back to this after some other putting
out some unrelated fires.

Since I first posted about this, the boss informed me that
there are only four unique blocks of constants, the others
are repetition! Thanks boss.

So I just manually horsed the data around to give me the
three-column table design you recommended.

I've got a "join" table set up to link things up, and just
have to populate that with the proper join information. But
I'm having a very strange problem using an append query to
do the populating. I've posted a message about that over in
the queries group ("Append Query Baffling Me!").

Thanks for your help.


Oh, I have to ask what does, "Don''t forget to rate the post
if it was helpful!" mean? Is this something for
web-posters?