Mike375 said:
But you have to handle the 20 categories.
There are basically three sections.
In one table all the prospects are strored and one field has a number from 1 to 20 for the category and another field has a number from 1 to 15 which identifies the salesman with the prospect. Thuis salesman number 4 might have prospect categories 4, 11 and 16.
There is another table which has the various calling and selling goals for each day of the week. There is another table that takes this data for each day so that a running total is obtained. The data is physically appended since that allows calling goals to be changed between a starting date and finishing date. Thus allowance can be made for holidays etc.
There is another table that a new record is created when each call is made.
There is another table that holds summary data of the above table because the above table gets too big. The summary data makes one record for all the calls on a given day for each category.
Then there are 15 tables for a diary for each salesman. Appointments are stored in one table but the 15 tables display appointmenst and allow appointments to be made for the appropriate salesman.
There are 22 queries, 26 forms, 24 macros and 2 tables for each category. All of these replicated 20 times and of course with different names.
The data in each table is all different because the various calling goals are different for different categories of prospect as are the starting and finishing dates.
In addition there are another bunch of queries, tables etc that keep track of prospects being consumed and also replacing the names from a large holding table.
The actuall calling is done from one screen and when a category of prospect is selected then the record source is changed.
Even if you could integrate it into one that would not be successful because quite often a single category is hired out. I make a blank data base and just import the queries, macros etc for one of the categories plus the other objects that do the statistical results.
Mike
Firstly, I 100% concur with Pat, your data IS NOT normalized at all:
Just an insight, but, you don't need 15 diary tables!!!! One flat table will do this job just dandy!
Unique ID: our key for indexing
Salesman: System ID (a number hoopefully)
Date: the date
Time: the time
To do: what to do
Etc.
That would do lovely for every salesperson.
Access doesn't care about the amount of records it needs to look at be it 1 or 100000, just that it's got the smallest number of bytes to look at.
So the above would be
Number
Number
Date/Time
Date/Time
Memo (hopefully just text)
running a query on this type of table will be FAR better than looking at 15.
(BTW I have a huge database which has 1000+ employees on it, apart from the unique identifiers NO information is repeated in any of the 20 tables that record DIFFERENT info on them, the queries do that!)
back to the question:
A DB of 1mb or 100mb will work if the data is properly stored, not repeated, given the smallest type of data to accommodate it (i.e interger is smaller than single, acn iI use an interger? etc.) and indexed correctly.
So, I wouldn't worry about the size of the DB, it's what happens at run time.
If you have 3000 forms, say, this will be a big DB Megabyte-wise. That doesn't matter it's how much memory is being used at runtime.
hope this helps.
