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! :confused:
Best Regards and Thanks for All Your Input!!
