I need to create a table in Access database for storing daily and hourly
information on response rates per 399 transaction types.

Option 1 - This table would have 399 (J type transactions) x 24 (number of
hours per day) x 40 bytes (field size) = 383,040 bytes.
TRAN HH EX COUNT IO COUNT TOT RESP
J001 00 999999999 999999999 999999999
J001 01 999999999 999999999 999999999
J001 02 999999999 999999999 999999999
J002 00 999999999 999999999 999999999
J002 01 999999999 999999999 999999999
J002 03 999999999 999999999 999999999

Option 2 - Jamming all 24 hours into one record = 399 (J type transactions)
x 800 bytes (field size) = 319,200 bytes
TRAN HH EX COUNT IO COUNT TOT RESP HH EX COUNT IO COUNT TOT RESP
J001 00 999999999 999999999 999999999 01 999999999 999999999 999999999

For reporting purposes, Option 2 seems more appropriate. However, for
future data trending and statistical reporting, I am leaning more towards
Option 1.

I would like to get feedback from experts on which option is better.

Option 2 would actually violate database normalization principles.

Option 1 is definitely the correct approach.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Crystal" <(E-Mail Removed)> wrote in message
news:7597BBB1-A0FC-4B17-BB42-(E-Mail Removed)...
>I need to create a table in Access database for storing daily and hourly
> information on response rates per 399 transaction types.
>
> Option 1 - This table would have 399 (J type transactions) x 24 (number of
> hours per day) x 40 bytes (field size) = 383,040 bytes.
> TRAN HH EX COUNT IO COUNT TOT RESP
> J001 00 999999999 999999999 999999999
> J001 01 999999999 999999999 999999999
> J001 02 999999999 999999999 999999999
> J002 00 999999999 999999999 999999999
> J002 01 999999999 999999999 999999999
> J002 03 999999999 999999999 999999999
>
> Option 2 - Jamming all 24 hours into one record = 399 (J type
> transactions)
> x 800 bytes (field size) = 319,200 bytes
> TRAN HH EX COUNT IO COUNT TOT RESP HH EX COUNT IO COUNT TOT RESP
> J001 00 999999999 999999999 999999999 01 999999999 999999999 999999999
>
> For reporting purposes, Option 2 seems more appropriate. However, for
> future data trending and statistical reporting, I am leaning more towards
> Option 1.
>
> I would like to get feedback from experts on which option is better.

Thank you for your response. I am obviously a newbie and just wanted
confirmation that I am heading in the right direction. Have a good day!

"Douglas J. Steele" wrote:

> Option 2 would actually violate database normalization principles.
>
> Option 1 is definitely the correct approach.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Crystal" <(E-Mail Removed)> wrote in message
> news:7597BBB1-A0FC-4B17-BB42-(E-Mail Removed)...
> >I need to create a table in Access database for storing daily and hourly
> > information on response rates per 399 transaction types.
> >
> > Option 1 - This table would have 399 (J type transactions) x 24 (number of
> > hours per day) x 40 bytes (field size) = 383,040 bytes.
> > TRAN HH EX COUNT IO COUNT TOT RESP
> > J001 00 999999999 999999999 999999999
> > J001 01 999999999 999999999 999999999
> > J001 02 999999999 999999999 999999999
> > J002 00 999999999 999999999 999999999
> > J002 01 999999999 999999999 999999999
> > J002 03 999999999 999999999 999999999
> >
> > Option 2 - Jamming all 24 hours into one record = 399 (J type
> > transactions)
> > x 800 bytes (field size) = 319,200 bytes
> > TRAN HH EX COUNT IO COUNT TOT RESP HH EX COUNT IO COUNT TOT RESP
> > J001 00 999999999 999999999 999999999 01 999999999 999999999 999999999
> >
> > For reporting purposes, Option 2 seems more appropriate. However, for
> > future data trending and statistical reporting, I am leaning more towards
> > Option 1.
> >
> > I would like to get feedback from experts on which option is better.
>
>
>

On Thu, 9 Nov 2006 13:10:02 -0800, Crystal
<(E-Mail Removed)> wrote:

>For reporting purposes, Option 2 seems more appropriate. However, for
>future data trending and statistical reporting, I am leaning more towards
>Option 1.
>
>I would like to get feedback from experts on which option is better.

Option 1, no question at all.

One reason (among many): what happens when the Powers That Be decide
that they want every fifteen minutes instead of every hour? Redesign
all your tables, queries, forms, and reports?

Note that you can use a Crosstab query to generate the wide-flat view
for reporting purposes, if that's desirable - *even if you go to finer
granularity*.

John W. Vinson[MVP]

Understood and thanks for your response.

Here is a follow-up question on same example:
I will need to calculate the Average Response Time per hour. Formula would
be TOT RESP/EX COUNT.

Several folks have indicated in prior postings that calculated data should
not be stored in a table field. Is there an exception to that knowing that I
would be calculating at least 399X24 = 9576 times per day? (I am not sure
how slow/fast this can be processed).

If the Average Response Time can be calculated from another system, would it
make more sense to have it calculated there and included as another field in
the data that I will be importing daily?

On Thu, 9 Nov 2006 14:38:01 -0800, Crystal
<(E-Mail Removed)> wrote:

>If the Average Response Time can be calculated from another system, would it
>make more sense to have it calculated there and included as another field in
>the data that I will be importing daily?

Only if a) you have a DEMONSTRATED - not hypothetical - performance
problem and b) can be absolutely certain that none of the data will
ever need to be corrected (possibly invalidating the calculation).

John W. Vinson[MVP]