Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    New Lounger
    Join Date
    Dec 2011
    Location
    Calgary, Alberta, Canada
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Best Structure for database, Your Ideas, Please?

    This is a new database in Access 2003 but doesn't have to stay in 2003, it could be created in 2007 or 2010. A group of people/controllers oversee a group of agents. (Controllers in one table, Table1-1.) The agents are divided by offices. (Offices in one table, Table1.) Each office and every agent has a unique identifier other than the primary key added to each record. (Agents are in two tables, 2 and 2-1.) The agent table (Table 2,) has another table for extra details and 'controls' related to that agent (Table2-1). In the agent details/extra table (2-1) the controller defines which form of communication is used for the agent. The activities/listings of the agents are in another table. (Located in table 3.) Each activity/listing has a unique identifier. An additional table holds details about the activity/listing. (Table 4.) The last table is a scrub list for email addresses and cell numbers. (Table 5.)

    When an activity/listing is added to the database (in table 3,) it is given a creation date within the details table(4). The activity/listing has a life span of 240 days. I would like the database to generate a report to say which agent needs communication for which activity/listing based on Day1, Day15, Day32, Day63, Day95, Day129, Day161, Dayetc. The report to generate each day needs to break into which agent, related activity/listing and form(s) of communication. The report needs to be exported to an Excel book, one sheet for each form of communication and containing a list of agents for each controller, so the Excel book contains six sheets for each controller. Or exported reports are one book for each controller with six sheets of communication.

    After spending a week reading sites across the Internet, my wheels are spinning without a proper direction to go in.

    One page I read today says to put all the zip codes of the offices into one table for an easy search. I do know having to search the database by zip codes will be important, searching either by office zip and display offices with agents or searching agents by zip to display the agents activities/listings. I know the controllers will need to add details to offices, agents or activities will be important. I know the controllers will add 'riders' to activities/listings and the details for each 'rider' will exist in yet another table.

    This is one reason my list of questions grows longer and longer. section-1 Do the tables hold enough information or should it be broken down more? What is the best way to join the tables together? Do I create the relationships first? From which table to which table and which columns to which columns? section-2 How to create the 'timeliine' for the activities/listings? section-3 Do I create tables (like a table of only zip codes across the US?) to assist with the queries? Should queries start/be created and let MS Access create the relationships? The report I'm looking for is a query from the timeline table, what should that table look like?

    Trying to provide all the necessary information and details for this project means, the activities/listings are added from a csv file, upto 250 lines. The additional activities/listing details are added by copying a webpage to an excel sheet then pasting a row from the sheet to the database (the row copied mostly moves the information from several parts of the sheet to one row). The same for the agent details from a webpage to excel to database. Removing the excel part would be a nice bonus. (I have designed and tested extrapolating the data from the webpages to excel into new record several times. I have tested adding the csv file several times.)

    Doing a search with zip codes seperated by commas would be incredible!! Having a report be ready to email to each controller each day in the form of a workbook sitting in a folder is the goal. The report is one workbook for each controller containing six worksheets. Each sheet is a method of communication (email, sms, facebook, twitter, voicemail and im ) to agent with details pertaining to the agent or agent activity/listing or information to agent pertaining to a 'rider'.

    The office list is benign, rarely changes and the smallest of the tables, it could hold around 20,000 entries. The agent list is by nature larger than the office list as there can be 1000 agents in one office and the agents move from office to office, so the agent table could be 800,000 records with coresponding detail records for each agent. The activities/listing table will be the largest table since the agents can have anywhere from 10-100 activities/listing during any period of time and hence could have 2,000,000 records contained within. Another reason to add a time period to the record so it can be moved into an "archive table" at the end of 240 days. This puts another wrinkle into the equation; this is being started in Houston and within a small set of zip codes are 16,000 agents, as more cities are added will the database handle all the info? Should there be tables for states, cities and zips? Or should there be 'east coast' and 'west coast' databases?

    I think, finally, there is the issue of controllers using the database because they are in different parts of the country, do they 'log in' to the database? How to give each controller a portion of the database, (as it relates to their area/zip codes) for editing or generating reports. Example of reports are: most active/inactive agents, areas/zips with no activity, list of 'riders' on activities/listing, lists of agents by zip or office. Eventually, the controllers have to be handled by supervisors and building now, or building that in later on? Which means levels of authority assigned to the person logging into the database. Would each level be assigned its own control panel or would the control panel have 'active and inactive' buttons? If logging into the database from different areas of the country, what is the best method to keep the database available to multiple users?

    Sounds like a job for Superman? I don't know him and turned to something far better than Superman...the WindowsSecrets Lounge! Happily I will do my best to answer any questions, although this is being posted by a noob so the answers may not be in a language you understand!

    Best Regards and Thanks for All Your Input!!
    Attached Files Attached Files
    Last edited by JackW409; 2011-12-29 at 10:59. Reason: Database attached as zip file

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I have downloaded a copy of your database (and attached a zipped copy to your original post) and later today I will try to have a look at it.
    Regards
    John



  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Sorry, but In have run out of time to look at this. I will be off-line for the next couple of weeks now.
    Hope someone else can help you.
    Regards
    John



  4. #4
    New Lounger
    Join Date
    Dec 2011
    Location
    Calgary, Alberta, Canada
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Me too! I really hoped putting my dilemma posted here would get quick results.

    Hey everyone! Batter's box is open!

  5. #5
    New Lounger
    Join Date
    Jun 2010
    Location
    Gurnee, IL, USA
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Normalization

    Hi, I took a quick look at the mdb you posted and noticed that the data needs to be normalized. You can get some good information by searching for Data Normalization on the Inet. As an example, I am attaching some modifications I did to your database structure. I dealt with only the agents and all offices tables. I noticed that office phone numbers were in both tables, i.e., the data was being repeated in multiple tables. So, I removed the office-related fields from the agents table, leaving only the officeID. I then set up a One to many relationship between the office table and the agents table, linking them by the officeID. With this setup, an office can have one or many agents assigned to it. If your agents can be associated with multiple offices, then the setup would be different. I think you will need to do quite a bit of normalization to make this data interact correctly. Sorry, there isn't a quicker fix. To get you started, here are some tips:
    1. Think of each table as a collection of discrete entities (entity = record) with a set of attributes (fields) that apply only to that individual entity. For example, if the entity is an agent, and there are multiple agents in an office, then the address of the office does not really apply uniquely to any one agent. Instead, use the OfficeID to link an agent to an office record in another table.
    2. Eliminate redundant data. In the original tables, the office addresses were repeated in the agents table and in the office table. They really only need to be in one. Makes it much simpler and more efficient to update the information.
    Hope this helps a little. Normalizing data is not something you learn overnight, at least not most of us. A good way to get started, after doing some online research, is to take a look at the sample Northwinds database that MS supplies with Access. It helps immensely to see a working example. Anyway, that's all I've got for now. Good luck.
    Kirk
    Attached Files Attached Files

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Just a cursory look leads me to believe it is not normalized. I'd expect to see an Agents table (with just Agent Information), an Offices table (with just Office information), and an AgentsOffice table which would show which Agent was in which office. I don't know your situation, so I can't really go much beyond that. For example, in Table2_Agents_test1 you have OfficePhone and Office FAX. I don't really know if this is the Phone# of the Office (shared by several agents), or if this is a unique # of each agent.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    New Lounger
    Join Date
    Dec 2011
    Location
    Calgary, Alberta, Canada
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Kirk, Thanks for your input!

    I've been spending time reading about normalization and understand your concern about repeat data. Time is the issue...how I wish there were more time in the day and this brings me to the big question.

    Can the database have a 'timeline' added? Where some information entered is 'static' (Office info, agent info could be considered 'static' and rarely changes.) It is when activities are added to each agent that requires actions after certain amount of days and the agent needs to be 'action-ed' with notifications, (the whole purpose of the database is to print out notifications and forms of notifying the agents.)

    From the original post...

    When an activity/listing is added to the database (in table 3,) it is given a creation date within the details table(4). The activity/listing has a life span of 240 days. I would like the database to generate a report to say which agent needs communication for which activity/listing based on Day1, Day15, Day32, Day63, Day95, Day129, Day161, Dayetc. The report to generate each day needs to break into which agent, related activity/listing and form(s) of communication. The report needs to be exported to an Excel book, one sheet for each form of communication...


    If this can be done with the database, its creation is worth pursuing.

    Kirk, Thanks for your time!!

  8. #8
    New Lounger
    Join Date
    Dec 2011
    Location
    Calgary, Alberta, Canada
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Mark, The offices are centers for the agents and many agents are only 'associated' with an office. To solve the normalization I could relabel the columns in the different tables. In many instances the information may appear to be the same, office info may appear twice in the output, and in many other instances the agents 'office info' would be actually different since the agent works out of their homes.

    Mark, many thanks for responding! Try this one...Can a 'timeline' be created for the database to tell us when things need to happen?

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jack,

    Exactly! Your "static" data would be in one table where the "dynamic" data would be in a second table. These tables would be joined on an item like AgentID in a one-to-many relationship. Then to assign a "dynamic" activity to an agent you merely add their AgentID as a foreign key in the activity record {if you've been reading about normalization this should make sense". The queries you set up can do the date math to pull the correct records and the query table is easily exported to an Excel worksheet.

    On the comment about time it's been my experience over 35+ years in this field is that there is never enough time to do a task but there is always time to do it over. In the end doing it right the 1st time is always more efficient, in terms of time, than doing it over and over and over. If you see this being used for more than a year take the time to do it right the 1st time.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    New Lounger
    Join Date
    Dec 2011
    Location
    Calgary, Alberta, Canada
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ahhh RG, You are so right about doing it correctly the first time. The sole reason I brought this question to this lounge...get expert advice and how-tos for doing it once and doing it the right way!! Since this is a business concept, yes, the database would be in use (hopefully!) for many years to come! It only seems daunting to get this database up and running because of my limited knowledge of MS Access and limited amount of time to learn how to do it!

  11. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jack,

    It may be in your best interest to hire someone to do this for you. If you can find a good local consultant they can help you get it setup and teach you along the way so you can take over maintenance and additions. Be wary of anyone who is not willing to do this. You don't want to get locked into a perpetual consulting situation since this would appear not to be a fortune 500 company.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #12
    New Lounger
    Join Date
    Dec 2011
    Location
    Calgary, Alberta, Canada
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    RG, On the comment of hiring someone... What amount of money do you advise as being ample to have the work done? As a person who lives from project to project creating web sites and being a virtual assistant, imagine, please, the funds are tight!! I do believe paying adequately for work is paramount and worth the while. (Really starting to enjoy this frustration over time.) lol

    Any suggestions about who could be hired to do this?

  13. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jack,

    Unfortunately, that is very hard to say. To get a reasonable cost estimate you need to have a fairly complete set of requirements, and you're no where near that. It will also depend on your geographic location as rates vary a dead minimum would be $50.00 per hour. What I'd suggest is to make contact with any IT personnel you may know to get a feeling for appropriate rates in your area and if they might know someone who is in need of work or moonlighting. In any case an initial, no cost, interview of anyone you are considering would be in order you need to be comfortable with the person you will be working with as you most defnately will be working WITH them. If they try to snow you under with a lot of jargon and can't explain things in plain easily understandable english...run dont walk away.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. #14
    New Lounger
    Join Date
    Dec 2011
    Location
    Calgary, Alberta, Canada
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well that was a deflating answer, lol "no where near that"? Oh dear!

    Would you mind posing some questions to get me closer to what I need for requirements?

    Your time is appreciated!!!

  15. #15
    New Lounger
    Join Date
    Jun 2010
    Location
    Gurnee, IL, USA
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Using a query for determining age of listing

    Quote Originally Posted by JackW409 View Post


    When an activity/listing is added to the database (in table 3,) it is given a creation date within the details table(4). The activity/listing has a life span of 240 days. I would like the database to generate a report to say which agent needs communication for which activity/listing based on Day1, Day15, Day32, Day63, Day95, Day129, Day161, Dayetc. The report to generate each day needs to break into which agent, related activity/listing and form(s) of communication. The report needs to be exported to an Excel book, one sheet for each form of communication...


    If this can be done with the database, its creation is worth pursuing.

    Kirk, Thanks for your time!!
    Hi, Jack. Once you have things normalized, it would be relatively simple to keep track of the age of a listing by using a query with a calculated field (let's call it Age) that is the CurrentDate - ListingDate. You can use the query as a source for reports, using whatever filter you like on the Age. You can even use it for whatever forms you might build, and use a filter just to see "current" listings, i.e., those that are no more than 240 days old. That way, you are using only one table for all listings, regardless of whether they are active or not. This is conceptually simpler. Of course, you might run into speed problems when you have very many old listings and that is where the second table of only old listings might come into play, but, just my opinion, it is better to use one table.
    Another tip: If you will have multiple users accessing the data, give thought to using SQL server on the back end and MSAccess for the user interface. In my experience, when you have more than about 5 users all hitting an Access database, it will trash itself about once every few months. Then again, maybe I am just unlucky. All of the stuff I have done for the past 10 years has been with Access for the UI and SQL for the data backend, and the phone calls for "corrupted data" have dropped to zero. I know it is another layer of complexity, but, compared to normalization, it is pretty simple.
    Enjoy!
    Kirk

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •