DB size?

[Mike375]

Ian,

Perhaps if I elaborate you will see why I use 15 tables, BUT if there is a better way and I gain then I change. Of course one must always consider the time and effort to impliment the changes.

Each of the 15 tables are identical except for the salesman number. There is one query for each table and each query simply has one calulated field.

Each table has 365 days entered. Each table has 20 fields for appt slots. 10 for the appointment time and 10 for the expected finising time.

However, the actual appointments and details are stored in one table. A double click on one of the fields that has the 20 appt slots will open a form for the corresponding entry in the appointment table.

When I click OpenDiary on the cold calling form then the diary form opens and the record source is set for the salesman associated with that prospect.

If I place all 15 tables into one table I finish with 5475 records. However, for some type of calling we stick 2 years of dates in a table. It is just so much simpler to do that in the specific table and doubly so when I am getting someone else to do it over the phone. I can also tell you that on older slow machines the diary is too slow to open with 5475 records. Also, I often hire out part of the data base for telemarketing and that might involve only 1 or 2 categories. All so much simpler when it is split up like I have it done.

It is also much easier with 15 separate tables for the caller to email me that table. I just have some macros made with names of Salesman 1, Salesman 2 etc and those macros export the table in question to .mdb file with nothing else in it and the caller emails to me that .mdb file.

Mike

 

[Mike375]

Pat,

From your posting:

Mike, I'm really not trying to pick on you but have you noticed how much time you spend defending this database?

I defend it because it works and you and others attack it. If you continue to attack I will continue to defend and because it works. It is developers from insurance companies and consultants to insurance companies that come and see me about "what is the attraction to this DB"...so I must be doing something right. Comments like Rich made about "use a cross tab query" to sort across the row"...well I would like to see that done for what is needed. But you professional developers disagree. Not long ago I posted up a question on how to sort across a row and you responed with, as near as I can remember, Access is not a spreadsheet and I would be on my own. I asked the question because I need to make another version (totally unrelated to the diary) and there may have been another way. Rich says it is easy, just do a cross tab query.

I don't believe that there is an experienced developer here who would agree with your approach.

That has been demonstrated and is no suprise to me.

I believe that it is a disaster waiting to happen but you understand it and are happy with it so, peace be with you.

What disaster will happen.


You don't really want to hear what we have to say. Every time someone offers a suggestion, you say "yes, but".

I have to "Yes but" when that is the case. I did not do that when Raskew (I think that is the handle, name is Bob) put up some wonderful stuff on dates. I did not do that when someone on dBForums showed me how to convert a mixture of

7032004
23032004

into 7/3/04 and 23/03//2004 with one calulate field as opposed to my Count filed and then the IIF fields with a Mid fucntion and then a join.

Much better than what I was doing. Have I made the change...No. Why? because it make no difference to how the thing works and it would take some time to make the changes. But I kept it and when I next have do something I will use it. Same as Raskews way of getting "age" as compared to how I do it.

So, OK, don't change anything. You just stand firm with your methods and keep making 20 of everything and we'll stand firm with ours and make one of everything and control data display with parameter queries.

I live on parameter queries with reference to the form. No argument there.

But let's get down to the nitty gritty. Ian says I should have the 15 tables that show diary appointments in one table. We now have a query based on a table with a minimum of near 5500 records. If we have 30 sales people it will be 11000 records and if we put 2 years of dates in there we could have 22000 records. In my case it would take me all of 5 minutes to add another 5 tables and them do Replace to change the salesman number. It would take me another 2 minutes to go to the macro that opens the diary and copy the last 5 macro actions and then change the conditions to the new salesman numbers such as 16, 17, 18, 19 and 20 and also change the Expression in the macros for RecordSource from 15Query61 to 16Query61, 17Query61 etc.

I already know that a macro can run down piles of lines and each line has a condition and the macro will fly throught that. But if I used Ian's suggestion of "all in one table" then things would get vey slow on a slow machine.

None of us has to add a complete set of objects when we add a new sales person or product. In our databases, the user just adds a new row to a table. No developer needs to be involved.

I would love to see how you could do that by adding another row in relation to the diary system.

If you can show me a gain then I will embrace it.

What I have to happen is that when the diary opens it opens for that salesman and the first line showing is the current day. It needs to show his appointment slots for the next 365 days. It also needs to show me the total number of appointments made.

If the prospect agrees to an appointment at 2.30pm on June 17 then it needs to sort that row after the appointment is entered.

As best as I can see I either have a table for each salesman (as I have) or a huge table as Ian suggested. Again, the actual appointment details are all in one table.

When the diary form opens there are a lot of unbound text boxes in the header for name, address etc that have their values set from the cold calling form. If some of those are wrong ( afault with our prospecting), then changes are made there and when the diary form is closed the values in the calling form a set by those values. The header also has an unbound text box where different details about the appointment are entered. This all goes into a new record for the table that holds all the appointment details.

But I am keen to know how an extra salesman's appointments can be displayed for the next 12 months by adding one row.

Sure, one row can call up some something, but what will it call up. How do I get 365 days of appointments display for the extra salesman off one row.

Mike

 

[Mike375]

Pat,

Two things you might think about.

The starter of this thread, Franknstuff, has stated that I was the only one that responded to his/her question. Professonal Develpers don't listen.

Secondly, I have had more Professional Developers look at this data base than there are grasshoppers in a wheat field. I have done that because my thinking has been I may sell it one day. BUT, they all run to water.

For example, the coding does not do the counterpart of the real long running macros. We have things close down. Coding seems to be very sensitive.

In short, as a "Professional Developer" you would increase your knowledge by listening to some amateurs rather than expending energy on condemning them.

You are obviously an intellignet man and as such you must surely realise a data base like mine that has evolved from 1996 has had a fair bit of input....especially given that I am in the insurance business and there is no shortage of programmers in the insurance companies and banks. Just a thought for you.

Something else you might consider is that this is a discussion forum. I am sure some different people on the forum will gain more by me posting "Yes but"

Mike

 

[Rich]

Your logic is flawed. You don't need tables full of redundant dates, Access has a cal control to select dates from.

Since you had a95 that version had a PIM example db. If you search here you might find it, that's assuming you want to see a more correctly structured example.

Pat's a she by the way
Oh! and I never said it was "easy"

 

[Mike375]

Your logic is flawed. You don't need tables full of redundant dates, Access has a cal control to select dates from.

Since you had a95 that version had a PIM example db. If you search here you might find it, that's assuming you want to see a more correctly structured example.

Pat's a she by the way
Oh! and I never said it was "easy"

But instead of some one line answers how would you replace what I do with the 15 tables and also, how would your cross tab query do the sort across the row....and I might add with a permanent situation that is back in the table. You are saying that your solution is all better etc. but you have done no more than some one and two line answers.

And Oh! and I never said it was "easy" ....then why not have it easy. But let's see your solution. One table instead of 15. And sort across the row with the cross tab.

And I can't wait to see Pat's "one extra row" for the extra salesman. I just want know how that works

By the way, I did no more than respond to Franknstuff's question....others decided to attack

Mike

 

[The Stoat]

Hi,

Tell you what Mike you right me a plain English description of what it is your trying to do. I will design you a full normalized, spanking ass database.
We'll work some way out of transfering your data into it - god's that will be a nightmare . I will get my solicitor to draw up a contract and you can flog it to these interested parties. I'll take 30%. Serious no BS. I've written plenty of DB's for all sorts of people. I'll have to do it in my spare time but what the hell. I'm getting married in Oz in November up at Cairns, i'll be going to Sydney on the way so i'll pop in and see you with it if you like.

TS

 

[Rich]

You've already been given examples and if you searched here for the PIM example db as suggested you could see for yourself how to approach your problems

 

[Mile-O]

Anything worth doing is worth doing it right (no offense)

Indeed.

In response to the original question, there is no average size - it depends on the purpose of the database. As Pat says, a minimum of one query per table.

AS for mike's, I get horrible images of it jumping up with Enter Parameter Value over 20 different queries each whenever a report or form is opened when one query can easily reference the parameter for all instances.

It may do the job for you mike but how much time do you spend maintaining it?

 

[Mike375]

Hi,

Tell you what Mike you right me a plain English description of what it is your trying to do. I will design you a full normalized, spanking ass database.
We'll work some way out of transfering your data into it - god's that will be a nightmare . I will get my solicitor to draw up a contract and you can flog it to these interested parties. I'll take 30%. Serious no BS. I've written plenty of DB's for all sorts of people. I'll have to do it in my spare time but what the hell. I'm getting married in Oz in November up at Cairns, i'll be going to Sydney on the way so i'll pop in and see you with it if you like.

TS

TS

Tell you what Mike you right me a plain English description of what it is your trying to do.

Well, I can already do it all in terms of what the data base does. But if I do as some people have suggested in the insurance business and sell it or sell parts of it, then it is going to need to pass the Pat Hartman test at the institutions. Whether it works after that would not be my concern.

I will design you a full normalized, spanking ass database.

You don't need to design anything, that is done and it has been running for a few years. As I said above it would be mainly an issue of it passing the Pat Hartman test with the institutions.

We'll work some way out of transfering your data into it - god's that will be a nightmare .

That part would be very easy to do...of that I can assure you. This thing is put together much better than some my attackers think.

I will get my solicitor to draw up a contract and you can flog it to these interested parties. I'll take 30%. Serious no BS.

I am open to anything in that area. No problems.

I've written plenty of DB's for all sorts of people. I'll have to do it in my spare time but what the hell. I'm getting married in Oz in November up at Cairns, i'll be going to Sydney on the way so i'll pop in and see you with it if you like.

Look forward to seeing you and hearing from you.

My contact details are

Mike McGuire
5 Burrell Steet
Beverly Hills NSW 2209
Ph (02) 95921507
Fax (02) 95920534
email Mike375@optusnet.com.au

What are your contact details?

Mike

 

[Mike375]

You've already been given examples and if you searched here for the PIM example db as suggested you could see for yourself how to approach your problems

I posted up how I do it and all you do is refer back to something else.

I did post up a challenge as to how others would do it. Can you do more that just refer to something. Are you able to do it?

You attack me but do not offer another way of doing it.

Mike

 

[___]

I posted up how I do it and all you do is refer back to something else.

The CORRECT way.

 

[Mike375]

Indeed.

In response to the original question, there is no average size - it depends on the purpose of the database. As Pat says, a minimum of one query per table.

AS for mike's, I get horrible images of it jumping up with Enter Parameter Value over 20 different queries each whenever a report or form is opened when one query can easily reference the parameter for all instances.

It may do the job for you mike but how much time do you spend maintaining it?

AS for mike's, I get horrible images of it jumping up with Enter Parameter Value over 20 different queries each whenever a report or form is opened when one query can easily reference the parameter for all instances.

Absolutely no way. All query paramenters are based on reference to form. If what you were suggesting was happening then it would be totally unsuitable for telemarketing.

But I am still waiting for how Pat Hartman and Ian are going to reduce the 15 diary display tables to one table....And where it works as required.

And then I am also waiting for Pat Hartmans "add one row" for the extra salesman. That is the most interesting one of all.

Mike

 

[Mile-O]

Never realised there was another 2 pages before I posted.

Interesting reading.

Personally, I'm not a professional developer/programmer/whatever. I've taught myself how to use over the past couple of years (although I must admit I've been able to code since I was 4).

When I build databases I make sure I do them properly and I like to think ahead to put some things in the users in my company would want in the near future.
___________________________________

Mike, how many of these people from within your company that have said it is good have had any sort of training with database design or have taken the time to look at the underlying design and structure to legitimatey comment upon it rather than pass mention on the overall aesthetic and operation of it?

I'm in my second insurance company (general the first time, medical now) and some of the stuff they've had prior to my ascendancy and arrival respectively over the two, there was an array of poorly designed databases. What made this all the more worrying was the fact that these databases had been designed and created by people external to the companies that they had brought in and paid a ludicrous charge to create something that looks like the Big Mac you get rather than the Big Mac in the photo that you expect. So, your comments on professional developers coming in and saying it is good really don't inspire and dull confidence - just keep it at a healthy neutrality. The majority of them are cowboys. Not Pat, though.

 

[Mile-O]

I'll make a one table diary system right now and post it for you to see.

 

[Rich]

I posted up how I do it and all you do is refer back to something else.

I did post up a challenge as to how others would do it. Can you do more that just refer to something. Are you able to do it?

You attack me but do not offer another way of doing it.

Mike

You need to lose the chip on your shoulder, nobody is attacking anybody!

 

[Mike375]

Mike, how many of these people from within your company that have said it is good have had any sort of training with database design or have taken the time to look at the underlying design and structure to legitimatey comment upon it rather than pass mention on the overall aesthetic and operation of it?

People that are "Developers" come and look and then see all the macros and then start thie spiel. The to help them a long a bit (I was a born stirrer) I show them the different tables that are obviously from the one family. The I get a non internet version of this thread. But at the end of the day I am an insurance salesman that runs a business that is telemarketing and insurance salesmen. Makes me a different person to the person who works for the insurance company. Not better, not worse but different.

I'm in my second insurance company (general the first time, medical now) and some of the stuff they've had prior to my ascendancy and arrival respectively over the two, there was an array of poorly designed databases. What made this all the more worrying was the fact that these databases had been designed and created by people external to the companies that they had brought in and paid a ludicrous charge to create something that looks like the Big Mac you get rather than the Big Mac in the photo that you expect.

I have often see similar with insurance companies in Australia. In most cases it does not matter because the insurance agents either decide to:

1) Not use it

or

2) Use something I make if they really need to use the computer.

Mike

 

[The Stoat]

Hi Mike,

All arguments aside it is obvious that some parts of you database need "redesigning". I am not necessarily talking about the functionality i.e what the screens do but how the data is held. This will probably endup effecting how the data is presented and accessed anyway. I take your point that it works for you, and it is a big consideration changing something that is in place and you know how to use. However no one will touch this with a barge pole if it is presented to clients and it does to conform to the accepted standards for database design.

If you have an entity relationship diagram or a schematic for the current system that would be very helpful. I will PM you with my details. I will give you an honest review of your current setup and at the very least tell you exactly how this could be achieved using a normalized system. If i think there is some serious mileage in it I will design table structures and give you a story board breakdown on how the data can be entered and retrieved. If at this stage you are happy to go ahead then things are going to have to get legal

A simple fact of IT is there are a many ways of achieving the same goal. Access doesn't force you to use normalization. But it is designed to support a system that is designed that way. Normalization is platform independant. If i was designing an Oracle or SQLServer or any other database using a modern db paltform i would do a systems analysis to understand how the system fits together and once i had done that i would always normalize my data. Having done that any competent db developer could implement this on any platform.

I think what people are trying to say is normalization has been proved to be the most effective way of holding data. It doesn't mean that there are no other ways but like most things in life there are accepted standards so at the very least we can all speak the same language.

TS

 

[Mike375]

I'll make a one table diary system right now and post it for you to see.

Remember, I did say the appointments are in one table.

It is the display when making appointments that has a table for each salesman.

As I mentoned to Ian I could put all of that display in one table, but it slows things downs and has other disadvantages.

Mike

 

[Mile-O]

Remember, I did say the appointments are in one table.

It is the display when making appointments that has a table for each salesman.

As I mentoned to Ian I could put all of that display in one table, but it slows things downs and has other disadvantages.

I'd say it was tables bound to forms and the quantity of macros that was slowing it down.

 

[Mike375]

You need to lose the chip on your shoulder, nobody is attacking anybody!

I think it was Pat Hartman said "not picking on you" so I just added a bit of colour with "attacking"

You are over reacting.

But to come back to the matter we discussed I was able to post up how I sort across a row. Can you post up how you would you do it.

Mike