Table setup

  • Thread starter Thread starter LyndsyJo
  • Start date Start date
L

LyndsyJo

I can't seem to wrap my brain around this one, though I suspect the answer is
simple.

I need to create a database for tracking inbound call info. I have to keep
track of 5 types of call data for each hourly period from 8 AM to midnight,
for every day (specified by date and day name). The end result needs to have
an easy to use form to enter daily data, as well as a report that can display
and summarize the data for any given date range or day of the week (i.e.,
Monday averages). I don't think I'll have any trouble creating the form or
report, but I'm having a hard time setting up the data in tables to relate
properly.

I'm visualizing the data like this (and maybe this is my problem):

Date
Data Type
Hour
Number

[For each date, there are 5 data types. For each data type, there are 16
hour periods. For each hour period, I need to enter one number.]

I tried creating three tables: One for the dates, one for the hours, and one
for the 5 types of data. But I can't figure out how to connect the three.
Does it seem like I'm heading the right direction by making these three
tables, or is there a better way to set these up?

Please let me know if I need to provide more info. It's been awhile since I
had to create a database and I'm feeling a little rusty.
 
I'm not clear whether you will use this database to record EACH call, or you
are looking for a way to summarize all calls (aggregated) "for each hourly
period".

If the latter, since you have (obviously) already captured and summarized
the data elsewhere, why not use a spreadsheet?

If the former, then each call would probably have something like:

CallStart (a date/time field)
CallEnd (a date/time field)
CallType (a foreign key field pointing to your tlkpCallType)
Comments (maybe)

Unless I'm missing something, you would be able to use this data to generate
reports about time-of-day, and/or day-of-week and/or call-type and/or
call-duration.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Good point. It is a summary of calls (i.e., Split 1 had 32 calls in the
11:00 hour on 12/4/07). The data is currently recorded in a spreadsheet.
However, my boss wants it recorded in a database so that she can just plug in
a start date, an end date, and what data she wants and doesn't have to mess
with formulas in Excel. The only data recorded is date, time, type, and a
number.

Any further thoughts on the best way to do this?



Jeff Boyce said:
I'm not clear whether you will use this database to record EACH call, or you
are looking for a way to summarize all calls (aggregated) "for each hourly
period".

If the latter, since you have (obviously) already captured and summarized
the data elsewhere, why not use a spreadsheet?

If the former, then each call would probably have something like:

CallStart (a date/time field)
CallEnd (a date/time field)
CallType (a foreign key field pointing to your tlkpCallType)
Comments (maybe)

Unless I'm missing something, you would be able to use this data to generate
reports about time-of-day, and/or day-of-week and/or call-type and/or
call-duration.

Regards

Jeff Boyce
Microsoft Office/Access MVP

LyndsyJo said:
I can't seem to wrap my brain around this one, though I suspect the answer
is
simple.

I need to create a database for tracking inbound call info. I have to
keep
track of 5 types of call data for each hourly period from 8 AM to
midnight,
for every day (specified by date and day name). The end result needs to
have
an easy to use form to enter daily data, as well as a report that can
display
and summarize the data for any given date range or day of the week (i.e.,
Monday averages). I don't think I'll have any trouble creating the form
or
report, but I'm having a hard time setting up the data in tables to relate
properly.

I'm visualizing the data like this (and maybe this is my problem):

Date
Data Type
Hour
Number

[For each date, there are 5 data types. For each data type, there are 16
hour periods. For each hour period, I need to enter one number.]

I tried creating three tables: One for the dates, one for the hours, and
one
for the 5 types of data. But I can't figure out how to connect the three.
Does it seem like I'm heading the right direction by making these three
tables, or is there a better way to set these up?

Please let me know if I need to provide more info. It's been awhile since
I
had to create a database and I'm feeling a little rusty.
Click to expand...
Click to expand...
 
Have your boss learn about Pivot Tables in Excel.

If you want to use the database to record the "raw" data, take another look
at my earlier response.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

LyndsyJo said:
Good point. It is a summary of calls (i.e., Split 1 had 32 calls in the
11:00 hour on 12/4/07). The data is currently recorded in a spreadsheet.
However, my boss wants it recorded in a database so that she can just plug
in
a start date, an end date, and what data she wants and doesn't have to
mess
with formulas in Excel. The only data recorded is date, time, type, and a
number.

Any further thoughts on the best way to do this?



Jeff Boyce said:
I'm not clear whether you will use this database to record EACH call, or
you
are looking for a way to summarize all calls (aggregated) "for each
hourly
period".

If the latter, since you have (obviously) already captured and summarized
the data elsewhere, why not use a spreadsheet?

If the former, then each call would probably have something like:

CallStart (a date/time field)
CallEnd (a date/time field)
CallType (a foreign key field pointing to your tlkpCallType)
Comments (maybe)

Unless I'm missing something, you would be able to use this data to
generate
reports about time-of-day, and/or day-of-week and/or call-type and/or
call-duration.

Regards

Jeff Boyce
Microsoft Office/Access MVP

LyndsyJo said:
I can't seem to wrap my brain around this one, though I suspect the
answer
is
simple.

I need to create a database for tracking inbound call info. I have to
keep
track of 5 types of call data for each hourly period from 8 AM to
midnight,
for every day (specified by date and day name). The end result needs
to
have
an easy to use form to enter daily data, as well as a report that can
display
and summarize the data for any given date range or day of the week
(i.e.,
Monday averages). I don't think I'll have any trouble creating the
form
or
report, but I'm having a hard time setting up the data in tables to
relate
properly.

I'm visualizing the data like this (and maybe this is my problem):

Date
Data Type
Hour
Number

[For each date, there are 5 data types. For each data type, there are
16
hour periods. For each hour period, I need to enter one number.]

I tried creating three tables: One for the dates, one for the hours,
and
one
for the 5 types of data. But I can't figure out how to connect the
three.
Does it seem like I'm heading the right direction by making these three
tables, or is there a better way to set these up?

Please let me know if I need to provide more info. It's been awhile
since
I
had to create a database and I'm feeling a little rusty.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top