Question Regarding Date Sensitive Tables

[Greg Jesky]

Mr. Vinson,
Thank you,
I want to make sure I understand what you are explaining.

The Account Table would have: Account No (Primary Key), Service Provider,
Billing date/Billing Period, Church Name.[Only one record for Verizon]

The Bill Table would have: Account No.(Foreign Key), Bill Date, Church Name,
Invoice Number, Previous Balance, Payments Made, Total Current Charges,
Total Long Distance, Total Overage Charges, Total Taxes, Total FCC charges,
etc[one record per month max 24]

The Bill Unit table would have Account No., Bill Date, Telephone No. of Cell
Phone 1, Overage Charges this cell phone, Long Distance this phone, Taxes
this cell phone, Usage Minutes this Cell phone, Overage Minutes this Cell
Phone, ETC[one record per unit per month max. 24]

Our objective is to be able to compare cost this month to last year, last
month, or last three months at the unit (Cell Phone ) level. We are
attempting to answer questions like do we have the best "Wireless Plan" for
our cell Phones. Are we consistently running overtime minutes on certain
cell phones or just occasionally. Are we paying too much for Rate Plans
based on Usage Minutes.

I hope I am explaining this correctly to you.

Thank you
Greg Jesky

----- Original Message -----
From: "John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com>
Newsgroups: microsoft.public.access.forms
Sent: Monday, March 14, 2005 1:40 AM
Subject: Re: Question Regarding Date Sensitive Tables

 

[John Vinson]

Mr. Vinson,
Thank you,
I want to make sure I understand what you are explaining.

The Account Table would have: Account No (Primary Key), Service Provider,
Yes...

Billing date/Billing Period, Church Name.[Only one record for Verizon]

NO.

If you have one account, that account will have MULTIPLE billing
dates. Unless I'm misunderstanding, the Account table should *not*
have a Billing Date field; that should be in the billing table.

The Bill Table would have: Account No.(Foreign Key), Bill Date, Church Name,
Invoice Number, Previous Balance, Payments Made, Total Current Charges,
Total Long Distance, Total Overage Charges, Total Taxes, Total FCC charges,
etc[one record per month max 24]

Close - but a couple of issues. For one, the Account No. and Bill Date
should be a joint two-field Primary Key.

If the Account pertains to a church, then the church name should be in
the Account table. If you need to see it in conjunction with a bill,
then you'll create a query joining the two.

And the Bill table should contain information ONLY pertaining to a
single bill. Previous Balance and Payments Made (unless they come
printed on the bill and you want to verify them against your records)
should *NOT* be stored, since they can be looked up using a query.

I don't get what you mean by "max 24" - after two years of billing,
are you going to shut down the database?

The Bill Unit table would have Account No., Bill Date, Telephone No. of Cell
Phone 1, Overage Charges this cell phone, Long Distance this phone, Taxes
this cell phone, Usage Minutes this Cell phone, Overage Minutes this Cell
Phone, ETC[one record per unit per month max. 24]

Rather than [Telephone No. Of Cell Phone 1] I'd just use [PhoneNo] -
if a bill covers six phones, you'ld have six records in the table.
Again - I have NO idea what you mean by "max 24".

Our objective is to be able to compare cost this month to last year, last
month, or last three months at the unit (Cell Phone ) level. We are
attempting to answer questions like do we have the best "Wireless Plan" for
our cell Phones. Are we consistently running overtime minutes on certain
cell phones or just occasionally. Are we paying too much for Rate Plans
based on Usage Minutes.

I hope I am explaining this correctly to you.

I think we're coming a bit closer... <g>

John W. Vinson[MVP]

 

[Greg Jesky]

Mr. Vinson

Thanks Again,

I am going to proceed as you suggested.

My tables:

Account Table::
Account Number (Primary key)
Account Name
Service Provider
[ One Record]

Bill Table::
Account No.(Foreign Key), (Joint Primary Key)
Bill Date, (Joint Primary Key)
Invoice Number,
Previous Balance,
Payments Made,
Total Current Charges,
Total Long Distance,
Total Overage Charges,
Total Taxes,
Total FCC charges,
etc.
[one record per month with all values coming from the monthly Verizon bill.]
{By "max 24" I mean I would like to keep 24 months of history, rolling OFF
the oldest and adding the newest- Also, I don't know how to do this yet but
at my age it may become someone else's problem}

Bill Unit Table ::
Account No., (Joint Primary Key)
Bill Date, (Joint Primary Key)
Telephone No. ,
Overage Charges for this cell phone,
Long Distance Charges for this cell phone,
Taxes for this cell phone,
Usage Minutes this Cell phone,
Overage Minutes this Cell Phone,
ETC.
[one record per unit per month ]

1. Account refers to the name on the Verizon bill for example "CHURCH".
CHURCH would be the value in the Account field
2. The Account Number is the Verizon generated number used by customer
service to access a bill. It is always listed on the Bill/Invoice
3. None of the fields above are calculated they will come from the
Bill/Invoice.

Questions:
How are the relationships established for "Joint Primary Keys" ? Do the
fields that make up a Joint Primary Key have to be adjacent in the record?
I experimented by putting one record into the Account Table, two records,
Jan and Feb into the Bill Table and two records into the Bill Unit Table one
phone number with Jan and Feb cost/usage. I then did a select query pulling
Service Provider(Verizon) from the Account table and all fields from the
Bill Unit Table. I expected 2 records and got 4 something about the Join and
the lack of relationships??

I hope your patience does not wear out on me.

Thank You,
Greg Jesky

Mr. Vinson,
Thank you,
I want to make sure I understand what you are explaining.

The Account Table would have: Account No (Primary Key), Service Provider,
Yes...

Billing date/Billing Period, Church Name.[Only one record for Verizon]

NO.

If you have one account, that account will have MULTIPLE billing
dates. Unless I'm misunderstanding, the Account table should *not*
have a Billing Date field; that should be in the billing table.

The Bill Table would have: Account No.(Foreign Key), Bill Date, Church Name,
Invoice Number, Previous Balance, Payments Made, Total Current Charges,
Total Long Distance, Total Overage Charges, Total Taxes, Total FCC charges,
etc[one record per month max 24]

Close - but a couple of issues. For one, the Account No. and Bill Date
should be a joint two-field Primary Key.

If the Account pertains to a church, then the church name should be in
the Account table. If you need to see it in conjunction with a bill,
then you'll create a query joining the two.

And the Bill table should contain information ONLY pertaining to a
single bill. Previous Balance and Payments Made (unless they come
printed on the bill and you want to verify them against your records)
should *NOT* be stored, since they can be looked up using a query.

I don't get what you mean by "max 24" - after two years of billing,
are you going to shut down the database?

The Bill Unit table would have Account No., Bill Date, Telephone No. of Cell
Phone 1, Overage Charges this cell phone, Long Distance this phone, Taxes
this cell phone, Usage Minutes this Cell phone, Overage Minutes this Cell
Phone, ETC[one record per unit per month max. 24]

Rather than [Telephone No. Of Cell Phone 1] I'd just use [PhoneNo] -
if a bill covers six phones, you'ld have six records in the table.
Again - I have NO idea what you mean by "max 24".

Our objective is to be able to compare cost this month to last year, last
month, or last three months at the unit (Cell Phone ) level. We are
attempting to answer questions like do we have the best "Wireless Plan" for
our cell Phones. Are we consistently running overtime minutes on certain
cell phones or just occasionally. Are we paying too much for Rate Plans
based on Usage Minutes.

I hope I am explaining this correctly to you.

I think we're coming a bit closer... <g>

John W. Vinson[MVP]

 

[Greg Jesky]

Mr. Vinson

Thanks Again,

I am going to proceed as you suggested.

My tables:

Account Table::
Account Number (Primary key)
Account Name
Service Provider
[ One Record]

Bill Table::
Account No.(Foreign Key), (Joint Primary Key)
Bill Date, (Joint Primary Key)
Invoice Number,
Previous Balance,
Payments Made,
Total Current Charges,
Total Long Distance,
Total Overage Charges,
Total Taxes,
Total FCC charges,
etc.
[one record per month with all values coming from the monthly Verizon bill.]
{By "max 24" I mean I would like to keep 24 months of history, rolling OFF
the oldest and adding the newest- Also, I don't know how to do this yet but
at my age it may become someone else's problem}

Bill Unit Table ::
Account No., (Joint Primary Key)
Bill Date, (Joint Primary Key)
Telephone No. ,
Overage Charges for this cell phone,
Long Distance Charges for this cell phone,
Taxes for this cell phone,
Usage Minutes this Cell phone,
Overage Minutes this Cell Phone,
ETC.
[one record per unit per month ]

1. Account refers to the name on the Verizon bill for example "CHURCH".
CHURCH would be the value in the Account field
2. The Account Number is the Verizon generated number used by customer
service to access a bill. It is always listed on the Bill/Invoice
3. None of the fields above are calculated they will come from the
Bill/Invoice.

Questions:
How are the relationships established for "Joint Primary Keys" ? Do the
fields that make up a Joint Primary Key have to be adjacent in the record?
I experimented by putting one record into the Account Table, two records,
Jan and Feb into the Bill Table and two records into the Bill Unit Table one
phone number with Jan and Feb cost/usage. I then did a select query pulling
Service Provider(Verizon) from the Account table and all fields from the
Bill Unit Table. I expected 2 records and got 4 something about the Join and
the lack of relationships??

I hope your patience does not wear out on me.

Thank You,
Greg Jesky

Mr. Vinson,
Thank you,
I want to make sure I understand what you are explaining.

The Account Table would have: Account No (Primary Key), Service

Provider,

Yes...

Billing date/Billing Period, Church Name.[Only one record for Verizon]

NO.

If you have one account, that account will have MULTIPLE billing
dates. Unless I'm misunderstanding, the Account table should *not*
have a Billing Date field; that should be in the billing table.

The Bill Table would have: Account No.(Foreign Key), Bill Date, Church Name,
Invoice Number, Previous Balance, Payments Made, Total Current Charges,
Total Long Distance, Total Overage Charges, Total Taxes, Total FCC charges,
etc[one record per month max 24]

Close - but a couple of issues. For one, the Account No. and Bill Date
should be a joint two-field Primary Key.

If the Account pertains to a church, then the church name should be in
the Account table. If you need to see it in conjunction with a bill,
then you'll create a query joining the two.

And the Bill table should contain information ONLY pertaining to a
single bill. Previous Balance and Payments Made (unless they come
printed on the bill and you want to verify them against your records)
should *NOT* be stored, since they can be looked up using a query.

I don't get what you mean by "max 24" - after two years of billing,
are you going to shut down the database?

The Bill Unit table would have Account No., Bill Date, Telephone No. of Cell
Phone 1, Overage Charges this cell phone, Long Distance this phone, Taxes
this cell phone, Usage Minutes this Cell phone, Overage Minutes this Cell
Phone, ETC[one record per unit per month max. 24]

Rather than [Telephone No. Of Cell Phone 1] I'd just use [PhoneNo] -
if a bill covers six phones, you'ld have six records in the table.
Again - I have NO idea what you mean by "max 24".

Our objective is to be able to compare cost this month to last year, last
month, or last three months at the unit (Cell Phone ) level. We are
attempting to answer questions like do we have the best "Wireless Plan" for
our cell Phones. Are we consistently running overtime minutes on certain
cell phones or just occasionally. Are we paying too much for Rate Plans
based on Usage Minutes.

I hope I am explaining this correctly to you.

I think we're coming a bit closer... <g>

John W. Vinson[MVP]