table set up - cell phone usage db for business

[mollycase]

hi -

i manage our company's cell phone plan.

right now i have a massive spreadsheet which was developed by someone else many years ago. i do not have anyone who is able to help me who has db dev knowledge.

i can get all the basics set up and normalized, but when it comes to usage, i don't know how to set it up. the usage will change every month, obviously. also, other than minutes used, not everyone will have the same the type of other monthly charges (text msg plan, data plan, games downloaded). some charges are recurring each month, like a text or data plan, but others are not - overages on the text or data plan, games or ringtones downloaded, etc.

also, employees are responsible for a portion of their charges, which we deduct from their paychecks. so i need a way to store 2 deduction dates and amounts per invoice date (if they have a large overage, we allow them to deduct over 2 pays instead of 1).

but this not all just calculated fields. i need a way to exclude text messaging overages because they are not always the employee's responsibility (i can't just go by amount, because if someone has a plan, and goes over, they are responsible, but if someone doesn't have a plan and starts using txting, i need to keep track of the charges per month so that i can determine if it's worth it to get a plan for them over time).

i imported data from the excel sheet and then used the table analyzer to sort it into tables.

i've attached it (all the names and cell numbers are fictitious).

i'm just at a loss as to where to go from here.

"table 1" just contains the leftover fields from the excel sheet that i didn't know what to do with.

i know there's probably some little errors here and there, but overall, i'm just trying to figure out the right table structure.

i would be entering data each month.

thanks!

 

[wilpeter]

If nothing else, this demonstrates the huge difference between Excel and Access. Data Types in the various tables, for example, are Text instead of logical. Field Names have spaces between words and are too lengthy. You need a starting point...a form that represents a transaction. The Excel spreadsheet shows the result of activity rather than the storable data. Let's hope someone knows of the existence of an existing Verizon type database.

 

[mollycase]

yes - i know that there are issues with field names, types etc.

i have just been trying to work out what fields should go where and how they would relate to each other for a few months now. it makes more sense to me to figure out how everything would go together first and then go back in and fix all the field names, types, etc later. i guess i'm trying to lay out all my index cards in piles before i go through and make each card written neatly without mistakes, if you get what i mean

i've looked and looked for about a year now for a way to do this via Verizon's extremely complicated and anti-intuitive reporting features in their online billing app.

i've also looked for a similar data base template and found nothing that would work. directories, yes. what i need, no.

i don't even know where to start with a form because that all depends on how the tables are set up. i had worked on another version of this and gotten to the forms and everything just ended up being a huge mess because i couldn't get the forms to work the way i wanted because i don't think i have the tables normalized correctly.

i can do a mock up of how i want the form to look and post that it anyone thinks that would help.

 

[wilpeter]

Speaking only for myself, yes, a mockup was what I had in mind. So that I can see what data would normally be recorded on a frequent basis. I always start by preparing the tables, but intuitively I know what the transaction is in my mind. From my lack of knowledge here, I am guessing that you receive billing from Verizon and that is a transaction (expense) and then you allocate this expense to different individuals according to various criteria. That would imply that one table contains the expense transactions - try starting with a form mockup that includes everything from that source.

 

[wilpeter]

Looking at the tables created by the Wizard,
tblEmpInfo is a major player: one record per employee (but I would make the Cell No primary key) assuming it is unique to an employee. Then if an EE had 2 phones, he'd receive two records and two billings. It seems this table should also contain a set of Yes/No fields representing the privileges or features.
tblAddRemoveDates to what do these dates apply? Cell # issuance?
tblMsgPlan; tblDataPlan; tblVoicePlan It would seem that "Allowed Messaging" belongs with privileges/features. The dollar amounts, however, are either allocations or actual Verizon billed amounts?
tblEquipCharges This could be different by Cell#. Can't tell if this is the Verizon amount or the allocation to a Cell#.
tblMsgUsage; tblDataUsage; tblVoiceUsage These sound like Verizon billing breakdowns (by Cell #?) that can be allocated.
tblOtherMonthlyFixed; tblOtherMonthlyVariable; tblOtherMonthly These sound like Verizon incoming amounts, but can't tell if they can be allocated by Cell privilege or as a percentage split.