DB size?

[Franknstuff]

Auto fill off of a pull down menu

So far Pat & Mike are still the only ones. After everybody jumped on Mike, now I know why nobody wants to post there DB info.

 

[Pat Hartman]

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.

I am sure that neither Ian nor I could come up with a design that you would accept and I wouldn't even try without specs but in general, you would have a single table for each salesperson and a single diary table where each row would contain the SalesPersonID so that the tables could be linked. Data should not be added to the diary table unless there is something to report. Things to report would include scheduled vacations for the sales person so that commitments would not accidentally be made for that time period. When you only store encumbered time, you reduce your storage requirements.

In addition and as I said in an earlier post those tables just make it so easy to pull off one part of the data base.

You can export queries as easily as you can export tables and they are much more flexible.

I would also mention that Pat Hartman posted just recently about having a corrupt table. So there is another gain I get. All my eggs are not in one basket

If you reread the post you'll see that no data was lost. The potential for the unexpected is why we make backups. The more critical your data, the more frequent your backups. I have worked with several applications that were backed up at noon as well as overnight.

Mile,
This link asks for a log in.

Alternatively again, myself and some others are working on an Access Calendar Project on this site.

Franknstuff,

After everybody jumped on Mike, now I know why nobody wants to post there DB info.

I'm sorry you feel that way. No one is name calling or flaming. We are having a discussion. Have you learned anything from it? I learn new things here all the time. I am not so altruistic that I would spend so much time here if there were nothing in it for me. I know it seems like Mike against the world but the people following this thread have all learned from painful experience that normalization is the way to go. Mike has the luxury of deciding what he wants to do and doing it any way he pleases since no one else is involved. He is both client and developer. The rest of us have clients (whether we are employees or consultants or entrepreneurs creating a commercial product). We develop an application and move on. The applications ultimately need to be understood and maintained by others. We also cannot be creating maintenance nightmares. Our clients need to be able to add a new salesperson or department or customer or whatever without calling on us to create a slew of new objects.

 

[Mike375]

Pat,


I am sure that neither Ian nor I could come up with a design that you would accept and I wouldn't even try without specs but in general, you would have a single table for each salesperson and a single diary table where each row would contain the SalesPersonID so that the tables could be linked.

Basically that is what I have, a table for each salesman and a single table to actually hold the appointments.

Data should not be added to the diary table unless there is something to report. Things to report would include scheduled vacations for the sales person so that commitments would not accidentally be made for that time period. When you only store encumbered time, you reduce your storage requirements.

That is done with my display and therefore goes into the salesman own table. It caters for time out for morning or afternoon or night or all day time or all night time. The dates in question also turn red. I simply do that with two overlaying fields, one with green and one with red for colour. Either one or the oither will always be null.

Quote:
In addition and as I said in an earlier post those tables just make it so easy to pull off one part of the data base.

You can export queries as easily as you can export tables and they are much more flexible.

Problem there is that the query won't have current data available. However, when I hire out part of the data base I simply import a complete category into a blank data base and then its on its way.

Quote:
I would also mention that Pat Hartman posted just recently about having a corrupt table. So there is another gain I get. All my eggs are not in one basket

If you reread the post you'll see that no data was lost. The potential for the unexpected is why we make backups. The more critical your data, the more frequent your backups. I have worked with several applications that were backed up at noon as well as overnight.

We are actually at a cross up here because as I said above what I have is awhat you are saying to have, that is one table per salesman and a diary table. Actually I back data changing tables very often. A macro exports the tables to another .mdb file and then a batch file run by the macro copies the little .mdb file to the CD ROM.

The view being put forward was that one table was all that was required. Also, I just can't see how Rich's cross tab query can sort across the row for the 10 appt slots. In fact i did ask this question sometime ago and as I remember your reply was basically "you are on own". If there was some simply snappy way to have done as compared to how I do it I would have jumped on it. I have to make another version (not for the diary) and will have to make copies and alter the macros that perform the sideways sort.

Quote:
After everybody jumped on Mike, now I know why nobody wants to post there DB info.

I'm sorry you feel that way. No one is name calling or flaming. We are having a discussion. Have you learned anything from it? I learn new things here all the time. I am not so altruistic that I would spend so much time here if there were nothing in it for me. I know it seems like Mike against the world but the people following this thread have all learned from painful experience that normalization is the way to go. Mike has the luxury of deciding what he wants to do and doing it any way he pleases since no one else is involved. He is both client and developer. The rest of us have clients (whether we are employees or consultants or entrepreneurs creating a commercial product). We develop an application and move on. The applications ultimately need to be understood and maintained by others. We also cannot be creating maintenance nightmares. Our clients need to be able to add a new salesperson or department or customer or whatever without calling on us to create a slew of new objects.

"Mike has the luxury of deciding what he wants to do and doing it any way he pleases since no one else is involved. He is both client and developer."

I think I did more or less say that earlier in the thread.

"We also cannot be creating maintenance nightmares. Our clients need to be able to add a new salesperson or department or customer or whatever without calling on us to create a slew of new objects"

That is just one of the reasons that I don't have this data base changed to code. If someone could click their fingers and suddenly this data base was all coding, then I would be lost when I want to make changes.

I regularly deal with data bases that are made by professionals, most commonly life insurance company quote systems and product comparisons. They are designed and made for a different market all together to what my data base is about.

However, I think the point that Franknstuff might have been making (as two people who also contacted me made) is an attitude by some that what I have is a piece of junk. Well, just for the record one life insurance company does use part of my data base and it is the part for product comparisons. In this case a professional developer simply can't match me because they lack the knowledge on the policy wording details.

Mike

 

[Pat Hartman]

have a single table for each salesperson

- sorry, I misspoke. I ment to say - have a single table for salesperson, not one for each.

Problem there is that the query won't have current data available.

- only tables store data which is why data in a query is ALWAYS CURRENT. It is coming directly from the CURRENT table.

In this case a professional developer simply can't match me because they lack the knowledge on the policy wording details.

- As I suspected, we were having a pissing contest. OK, you win. You know more than I do. Answer your own questions.

 

[Mike375]

You know more than I do. Answer your own questions.

When it comes to policy wording that will be the case. Without policy wording knowledge you can't make the data base on your own. You can make code as long as your head points to the sky but you can't add content.

There is no pissing contest and if there was one then it is coming from your side. Go back to my first post on the thread where I answered Franknstuff's question and then see what followed.

And only tables store data which is why data in a query is ALWAYS CURRENT. It is coming directly from the CURRENT table. But it is not current if the table the other person has is not up to date.

Mike

 

[Franknstuff]

still here

I understand Pat it's just that I was the one who asked a question and Mike answered, then it went somewhere else. Kind of feel guilty.

 

[Mile-O]

Don't feel guilty, Frank.....


Mike's database will never see the light of day outwith his own sphere of employment because:

• It is poorly designed;
• It is an ancient version of Access that, once converted to a later version of Access, won't stand up to scrutiny and will have many errors;
• Has no error handling;
• Requires numerous man hours when alterations are required;
• Is slow;
• Is too specific to a specific insurance company;
• Is too specific to an insurance type.

 

[WayneRyan]

Jeepers!

This thread is interesting (shouldn't it be in the WaterCooler?).

When I first started with computers ... Rule #1 was that if
automating the app saved you time/effort then it was worthwhile.
Otherwise, forget it. To Mike, this obviously saves him time/effort,
he's happy with it, therefore it must be GOOD!

Whether his DB is normalized, abnormalized, or a complete travesty
of everything that we hold near and dear is beside the point.

Granted, it may be better designed, but it surely does the job
that he intends, he is familiar with it... so let's move on.

Now, if we can get him to post it, maybe some of (us/you) can show
him just how really sweet life can be!

Wayne

 

[Mile-O]

Now, if we can get him to post it, maybe some of (us/you) can show him just how really sweet life can be!

That would be nice.

As for the "if it works for him then that's okay" part, he's talked about selling the bloody monster which, from what we've heard of it is wrong - nobody in their right mind would buy it.

 

[Franknstuff]

Oh yea!

Please don't try to make me fell less guilty. Picked a bad time to quit drinking!

 

[Pat Hartman]

nobody in their right mind would buy it.

- Don't be so sure about that. Lots of people have bought SAP and spent 10's of millions to install it!!

Franknstuff - no need to feel badly. Lots of people have found the post interesting.

 

[WayneRyan]

WOW!

I read this thread earlier, but waited about 4 hours to post.
A lot sure transpired right before I posted.

First of all; If this was a software design meeting, I'd be
swimming back to shore right now. Don't think much positive
tech info will come out of this enterprise.

Frankenstein - Boy did you create a monster!

Mike - Your s/w works for you. Great!

"We develop an application and move on. The applications ultimately
need to be understood and maintained by others. We also cannot be
creating maintenance nightmares. Our clients need to be able to add
a new salesperson or department or customer or whatever without
calling on us to create a slew of new objects."

Your software is the opposite of that. You need intervention
constantly in your app. I don't have the faintest idea what
that dialog was meant to accomplish.

Pat & Mile (Mile & Pat?) spent a bit of their time here to give you
some thoughts about how to structure your data. I just hope
other readers of this thread can benefit from it.

The fact that you can sort your data sideways doesn't mean you
have to define it in such a high-maintenance way.

My original thought on this was that if Mike's software saved
him more time/effort than it took to develop THEN it is good.
If he's happy, so am I.

I'm moving on ... see ya
Wayne

 

[Mike375]

That would be nice.

As for the "if it works for him then that's okay" part, he's talked about selling the bloody monster which, from what we've heard of it is wrong - nobody in their right mind would buy it.

Mile,

The reason people people buy data bases like this one or part of it is due to the design, not the computer design of course. Invariably "parts" of it are bought. The main thing I have to weigh up are the negatives of my competitors having it if the whole "system" was sold.

About 3 years ago I sold part of it to a life insurance company and I can assure you they could not care less whether it had a macro or a code running something or whether it was on Access 95 or whatever. Their main interest is in the whats and whys of what it does and especially the "whys" and in particular the screens.

The problem that continually arises is that the people in the insurance company or outside contactors don't make something that suits the life insurance selling environment. Input they get from insurance agents is usually just this side of useless because the agents don't really use the data base stuff very much because whatever they get is too hard to use and so their input is frequently way off base. Financial planning and General insurance tends to be somewhat different as it does not have the same sort of selling environment as does life and disability insurance.

Mike

 

[ChrisO]

In my opinion, not that that should make any difference, this is a great thread.

Rightly or wrongly, people have stuck to their convictions.

It is refreshing to me to see someone that is prepared to take on the establishment.

Mike375.
I applaud your efforts, even though I think you are wrong.

Pat
If I may quote you…(It is not my place.)
“Don't be so sure about that. Lots of people have bought SAP and spent 10's of millions to install it!!”

My last brush with SAP (and the SAP’s that try to run it in my opinion) desperately tried to offload the ‘detail’ to something else… namely Access.

SAP = Good.
Detail = Good.
SAP = Not Detail.

There is a lot more to that story but, for the moment, would simply muddy the waters.

Regards,
Chris.

 

[The Stoat]

Hi all,

Mike your knowledge of your environment may be indepth/unique etc however a competent systems analyst would be able to transfer your knowledge into a structure that a database developer would understand. Most developers who post on this board do their own systems analysis and development. For big projects it is often necessary for the company to employ a systems analyst or team of analysts if they don't already have one/them. There is a distinct profession of systems analyst. They are able to decipher the complexities of business systems regardless of the environment. That is not to say that some don't specialize but the tools used are good for 99% of all industries/military etc. In the UK contracts for the Government are expected to be run using PRINCE 2 project managment and Structured Systems and Design Methodology (SSADM). I use both.

A true story:

I went to college in the evenings to "learn" IT. My first course was on database implementation. I understood nothing. Fortunatley the course work said "Create a system that is able to do x,y and z" at the end of the module i designed an Excel system and argued in my write up that function should take precedent over implementation. I got a good grade purely because "You have designed a system that works". A year later i did systems analysis. Within 5 minutes I realised that they had got the 2 modules in the wrong order. Even knowing very little about Access at the time, I was able to take the systems analysis and create a complex database in 2 weeks. The systems analysis took 8 weeks!

I honestly don't think this discussion can really be resolved. A well designed database structure does not necessarily mean a well implemented interface. A poorly designed database structure may have a fantastic interface. But a poorly designed structure will always take up more space, be slower, take more maintainence and be less efficient. To be honest i would find it 10 times easier to teach someone good database design than good interface design. If you have a good understanding of interface design then learning how to properly design the relationships and structures will lead you to develop even more useful, simple interfaces. And i will guarantee that you will do the whole process in less than half the time.

TS

 

[Mike375]

TS

You covered much of it in your last paragraph.

When you make the data base like I do much of it is done on the run. Contrary to much of what has been posted, for me, being able to change things very quickly is of prime importance and it is often done under poor conditions.

As an example (and this might be incorrect but is based on my current knowledge) I would not touch coding with OnClick attached to labels. The reason is that sometimes we make some changes while calls are being made and with a macro I just bring the data base forward and make the changes with the data base fully open and ready for the next call. I think with code I would have to close down and go to design view on the form.

Having said that I don't for one moment think that several people on this thread can't make the data base better than myself.

In fact there are several things I would change but simply don't get around to it. For example there are about 8 or 9 combo boxes that do a Setvalue on AfterUpdate for a time drop down list. Australia has a 3 hour time gap during daylight saving from East to West and 1 hour South to North and we telemarket right across Australia while doing all calls in Sydney. When we come off daylight saving the time zones are the same for South to North and drop to two hours for East to West.

So..if we are calling someone in the West and during daylight saving and they say...can you call back at 3pm...then the data base sticks in 6pm, which will be the time in Sydney.

In my case I have a set value action for the West of

[Forms]![2ProspectT]![RingT5]+(0.0208333333*6)

And that sets the value for [RingT5]. When daylight saving finishes I have to go back and change (0.0208333333*6) to (0.0208333333*4)

Since it is only twice a year I never get around to having the setvalue reference a field. Might do it tonight

PS, if I get a moment a bit later I will try and call. Must be just after lunch in good old mother England.

Regards,

Mike

 

[Mile-O]

So..if we are calling someone in the West and during daylight saving and they say...can you call back at 3pm...then the data base sticks in 6pm, which will be the time in Sydney.

In my case I have a set value action for the West of

[Forms]![2ProspectT]![RingT5]+(0.0208333333*6)

And that sets the value for [RingT5]. When daylight saving finishes I have to go back and change (0.0208333333*6) to (0.0208333333*4)

I'd keep all my times as the same Time format but, for locations have a GMT offset within the locations table.

It would remove the need to hard code time conversions into the database.

 

[Mike375]

I'd keep all my times as the same Time format but, for locations have a GMT offset within the locations table.

As I said above I don't have any reservations about you and others being able to make the stuff better.

But we do get down to time involved for change.

For example that field [RingT5] has a lot of things that hang off it. Also consider it probably takes me about 2 minutes a year to change for the daylight saving. Of course I also acknowledge that as simple as the change is it is way above what someone without Access knowledge could do.

Mike

 

[Mile-O]

Would I be right in guessing that you don't even have this database split into a front and backend?

 

[Mike375]

Would I be right in guessing that you don't even have this database split into a front and backend?

You are 10000% right

I have done that before just to do it!! I am assuming you mean that all the forms, queries etc are in one .mdb file and the tables other in another .mdb file.

Mike