Trending

[Michael]

Hello,

I have a table with a list of account numbers. Each account number is a
cell phone account.

Each month, I will manually enter the minutes used and then create reports
to trend the data, but I only need a 6 month window. Can someone please
recommend the best way to create a table to do this.

Thanks in advance.

Michael

 

[Jeff Boyce]

Don't! You don't need a separate table for each 6-month period.

That's what queries are for. Keep all your data in the original table and
use a query to grab out your "6-month" set.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Michael]

Jeff,

Thanks for the reply. Actually, the table only has approx 200 rows with one
field, the tele number. Each month, I will take the bill and manually enter
the minutes used for each number. Unfortunately, I have no way of getting
the data in there except for manual entry. Do you suggest I create 12
additional fields per record, such as M1, M2, M3, etc, populate the data and
then run queries and report?

Thank you for your time.

 

[Jeff Boyce]

Michael

Absolutely ... NOT! That would make it a spreadsheet, not a table.

Part of the learning curve that comes with making good use of Access is
learning about relational database design and normalization.

I'm quite sure I don't understand many of the details of your situation, so
take what follows as an under-informed general notion...

If I had [PhoneNumbers] that have [UsageMinutes] associated with them each
month, I might set up the following table structure:

tblPhoneNumber
PhoneNumberID
PhoneNumber
DateAdded
... (other phone number specific info)

trelUsage
UsageID
PhoneNumberID (a foreign key value, pointing back to the tblPhoneNumber
record)
UsageDate (the date the usage was recorded)
Usage (the 'minutes')

Then, when I needed to find all the usage for a given phone number during
March, 2008, I'd use a query to join the two tables, and use the Month() and
Year() functions against the UsageDate field.

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Michael]

Jeff,

This is great and I think it might be enough to learn from. I appreciate
your help.

-M-

Michael

Absolutely ... NOT! That would make it a spreadsheet, not a table.

Part of the learning curve that comes with making good use of Access is
learning about relational database design and normalization.

I'm quite sure I don't understand many of the details of your situation,
so take what follows as an under-informed general notion...

If I had [PhoneNumbers] that have [UsageMinutes] associated with them each
month, I might set up the following table structure:

tblPhoneNumber
PhoneNumberID
PhoneNumber
DateAdded
... (other phone number specific info)

trelUsage
UsageID
PhoneNumberID (a foreign key value, pointing back to the tblPhoneNumber
record)
UsageDate (the date the usage was recorded)
Usage (the 'minutes')

Then, when I needed to find all the usage for a given phone number during
March, 2008, I'd use a query to join the two tables, and use the Month()
and Year() functions against the UsageDate field.

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for the reply. Actually, the table only has approx 200 rows with
one field, the tele number. Each month, I will take the bill and
manually enter the minutes used for each number. Unfortunately, I have
no way of getting the data in there except for manual entry. Do you
suggest I create 12 additional fields per record, such as M1, M2, M3,
etc, populate the data and then run queries and report?

Thank you for your time.