DB Tables

[mama16]

I'm trying to create thi DB on some statistics. I have two tables but I think I can only do it with just one table. I've already imported the data from excel. This is what I have,


Table1 MMDDYY (PK)

Table2

MMDDYY (PK)
Hour0
Hour1
Hour2
Hour3
Hour4
Hour5
Hour6
Hour7
Hour8
Hour9
Hour10
Hour11
Hour12
Hour13
Hour14
Hour15
Hour16
Hour17
Hour18
Hour19
Hour20
Hour21
Hour22
Hour23
Datacalltotals
TotalRequests
Primarycalls
Sharedcalls
SharedDuration
Monthlyminutes
Peakcallstotal

The MMDDYY is unique because it will not repeat. Data is going to be entered on a daily basis. Am I going on the right track?

Any ideas,

Thanks

 

[SkipVought]

mama16,

Are these all fields???
Hour0
Hour1
Hour2
Hour3
Hour4
Hour5
Hour6
Hour7
Hour8
Hour9
Hour10
Hour11
Hour12
Hour13
Hour14
Hour15
Hour16
Hour17
Hour18
Hour19
Hour20
Hour21
Hour22
Hour23

Your table is not Normalized and you're gonna have problems from here to tomorrow getting data out of it. Check out Fundamentals Of Relational Database Design

Mabe a better table design might be something like...
[tt]
MMDDYY (PK)
CallType
CallStart
CallFinish
[/tt]
Not knowing how these values are calculated, limits my guess. Should not store aggregate totals in a table. That's what you do when you query.


Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[mama16]

Yeah, that's how it appears on the spreadsheet

Date Hour0 Hour1 Hour2....... Hour23
11-01-04 0 21 32 121
11-02-04 53 44 85 0
11-03-04 26 0 8 35
11-04-04 3 6 5 9
11-05-04 8 9 15 8
11-06-04 3 9 0 0
etc

This is how the data looks on the spreadsheet

 

[SkipVought]

Am I going on the right track?

[red]No![/red]

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[mama16]

Should I add all those fields into one field
Hour0......+Hour23 = OneField
Will that be more acceptable?

 

[SkipVought]

Please explain what data this table stores and how it is going to be used.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[mama16]

This table stores number of calls,
Hour0, there were this n calls
Hour1 x calls
Hour2 y calls
All the way to hour23
Plus additional fields.
Like Data call totals, will have the total of calls for that day
The data is in a Excel spread sheet from 01-01-04 till Present.

 

[SkipVought]

So you have a call as an entity that you count.
Is there a call duration?
does the call get counted in the hour that it starts or ends?


Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[willir]

Mama16

I know you want to report summary data.
...but are you capturing the data as summary data or in detail?

If you are working with summary data, would you be better off using a spreadsheet program?

If you work with "raw" or detail data, then you only need to capture the time of the call. A query can then break the data by hour-of-day.

Real simple example...
tblCall
CallID - pk (and NOT the date)
CallDate - date field
CallTime - time field
...then what every statistical grouping category you want to itemize. For example...
CallType - eg. primary, seconday, shared
CallDuration - integer representing minutes

You can then run your queries for obtain summaries, and even export the data to a spreadsheet for more anlysis.

By-the-way, you have
Monthlyminutes
Peakcallstotal
...which are more on the analysis -- in general, calculated data are not stored but are rather are calculated as required.