DB size?

[Mike375]

Rakier

As you said the time to make the changes is a real sticking point and doubly so when the thing works.

Another issue is that you might just prefer to do things certain ways. For example, the issue that was mainly discussed on this thread about using 20 tables instead of one table is something that I just prefer. With the 20 categories of prospect for telemarketing it just makes me happy to have it as virtually 20 separate data bases that funnel the results into a central section.

But also I think that data bases are like some other things in life. On one hand you have the enthusiast and for some others things are just a means to an end.

I am very interested in firearms and participate on a couple of gun forums. Now in that area I am probably the counterpart of Pat and Rich. However, the majority of posters on those forums basically have the gun as a means to an end. You find the same deal with car owners.

Like yourself I have often thought about going through this data base and cleaning some things up but time is the killer.

Mike

 

[Rich]

But also I think that data bases are like some other things in life. On one hand you have the enthusiast and for some others things are just a means to an end.

Bit like the modern day car and a steam driven one then eh?

 

[Mike375]

Bit like the modern day car and a steam driven one then eh?

Funny you mention that because I have always been interested in steam engines

 

[Rakier]

I agree that there are definitely preferences to ways to doing things. I have some differences between the way that I name variables during coding and that which is "standard". That's okay though. We're all gonna have some things that we prefer over things that others prefer.

My problem was that the post seemed to be degenerating into a "my way" vs "your way" free for all. The experienced members of the forums (and probably what you would call enthusiasts ) were taking time out of their day to do something which they thought was helpful to you. You took their advice as "attacks" against your methods. I don't think that was their intentions at all. They were merely pointing out that your method is not considered the "proper" method for doing things...even though it works. It's kinda like building a car. You can use the wrong types of lugnuts to hold the tires on, but eventually the tires will get loose and fall off. I think that is the same thing they were trying to say about your dB. Even though it works now and everything's wonderful, eventually you are going to run into a problem. I have found several problems with the old dB that I designed and even though I designed workarounds for them, I knew they would eventually return to bite me in the a**. That's why I proactively redesigned the thing and am doing it again. This way, I can take the time while the production model works fine rather than having a major crisis and rushing with fixes.

 

[Mike375]

Rakier

I agree that there are definitely preferences to ways to doing things. I have some differences between the way that I name variables during coding and that which is "standard". That's okay though. We're all gonna have some things that we prefer over things that others prefer.

We agree

My problem was that the post seemed to be degenerating into a "my way" vs "your way" free for all. The experienced members of the forums (and probably what you would call enthusiasts ) were taking time out of their day to do something which they thought was helpful to you. You took their advice as "attacks" against your methods. I don't think that was their intentions at all.

Go back to the start of the thread. I did no more than respond to the thread starter's question.

They were merely pointing out that your method is not considered the "proper" method for doing things...even though it works.

But there are reasons why it works. Give me at least some credit. I am 56 and have been in the insurance business since I was 22. You must know that the insurance companies and banks ar all computer based. Do you have any idea of how many "Pat Hartmans" have looked at this data base? Do you have any idea of how many times I have heard "I see what you mean" or "That changes things"

My data base is not a piece of shit!!!!

The issue of having one table instead of 20 tables does not work. I have been through that. One of the attractions of this data base for telemarketing is that it updates Results Vs Goals as you complete each call and click Next Prospect. Selecting results from one big table slows things down as the you mobe through the calls.

I am not at all saying that Pat Hartman and others could not make the data base better BUT most of their criticism is misplaced. Again, commonsense should tell you that given I am in the insurance business and make stuff for one insurance company...then I am not operating on some island..

It's kinda like building a car. You can use the wrong types of lugnuts to hold the tires on, but eventually the tires will get loose and fall off. I think that is the same thing they were trying to say about your dB.

That is something I would like someone to elaborate on for me. This data base today is essentially the same as it was in 1999/2000. I am assuming that the data base is not like a car in the sense that it "wears out"

Even though it works now and everything's wonderful, eventually you are going to run into a problem.

Again, elaborate for me. What will be the problem or problems?

These loose general statements don't help me.

"I have found several problems with the old dB that I designed and even though I designed workarounds for them, I knew they would eventually return to bite me in the a**. That's why I proactively redesigned the thing and am doing it again. This way, I can take the time while the production model works fine rather than having a major crisis and rushing with fixes."

Again, elaborate for me on what sort of problems I will encounter.

I just did a quick check on some of the forms, macros and queries and the latter part of 1999 was when they were made. So we have been running for 4.5 years.

I do make changes each week or so but they usually only involve opening a different form etc.. From March 2004 until the end of may 2004 I had to add quite a lot because of gov't regulations in Australia that relate to the selling of various financial products. I might also say that our DB is doing it all while the bigger groups are "still in transit" and have Word Templates as a "fill in"

Mike

 

[raskew]

Up till now, everyone has tried to be extremely politically correct.
There comes a time when we just have to call a spade a spade.

There’s really no point in debating this as a programming issue.

What we’re looking at here is ego and divine ignorance.

Mike apparently took Access for Dummies 101 and at that point
his programming education came to a halt.

He was introduced to macros, thought they were neat and, at that
point, decided to quit learning. The fact that his application
contains no modules says it all.

Bottom line:

- Mike possesses no coding skills and doesn’t want to be
encumbered with any.

- Mike is clueless when it comes to database design. His ego
tells him otherwise so there’s no point in trying to educate him,
he’s hopelessly ignorant and that’s the way he wants to remain.

Bob

 

[Rich]

Funny you mention that because I have always been interested in steam engines

takes about as much time to maintain as your db eh

 

[Mike375]

Up till now, everyone has tried to be extremely politically correct.
There comes a time when we just have to call a spade a spade.

There’s really no point in debating this as a programming issue.

What we’re looking at here is ego and divine ignorance.

Mike apparently took Access for Dummies 101 and at that point
his programming education came to a halt.

He was introduced to macros, thought they were neat and, at that
point, decided to quit learning. The fact that his application
contains no modules says it all.

Bottom line:

- Mike possesses no coding skills and doesn’t want to be
encumbered with any.

- Mike is clueless when it comes to database design. His ego
tells him otherwise so there’s no point in trying to educate him,
he’s hopelessly ignorant and that’s the way he wants to remain.

Bob

Bob,

These are the books I have:

Using Access 95 by Roger Jennings

Access 95 Unleashed Dwayne Giffor, et al

Running Microsoft Access for Windows John L Viescas and Microsoft Press. I also have the same book for Excel, Word and Powerpoint

And of course the Access Help material.

You said:

"Mike possesses no coding skills and doesn’t want to be
encumbered with any."

You are right. I don't need to be encumbered.

Mike

 

[Mike375]

Bob,

I do have one module but I have it in a another data base. Do you remember this I have this in a calculated field

AgeAtDeath: Agecount6([DOB],[DOD])

To run the following:

Function Agecount6(ByVal pdob As Date, _
                     Optional ByVal pEdte As Variant, _
                     Optional ByVal pWhat As Variant) As String

'*************************************************  ****
'Purpose:   Display age or difference between
'           two dates with options to display
'           in any variation of years, months,
'           days.
'Coded by:  raskew
'Inputs:    1) ? Agecount6(#3-Mar-80#) 'defaults
'                to current date & "ymd" display
'
'           2) ? Agecount6(#3-Mar-80#, "4/25/04")
'                Uses PEdte in place of date(),
'                and default "ymd" display

'           3) ? Agecount6(#3-Mar-80#, "4/25/04", "d")
'                 Same as 2), but with display as days
'
'Output:    1)  24 years, 1 month, 15 days
'           2)  24 years, 1 month, 22 days
'           3)  8819 days
'*************************************************  ****
                   
Dim dte2      As Date
Dim dteMyDate As Date
Dim intHold   As Integer
Dim n         As Integer
Dim strHold   As String
Dim strHold2  As String
Dim strTemp   As String
Dim strWhat   As String

    strWhat = IIf(IsMissing(pWhat), "ymd", pWhat)
    
    dteMyDate = pdob
    dte2 = IIf(IsMissing(pEdte), Date, pEdte)
    For n = 1 To Len(strWhat)
       strHold = Mid(strWhat, n, 1)
       Select Case strHold

          Case "y"
             intHold = DateDiff("yyyy", dteMyDate, dte2) + _
                      (dte2 < DateSerial(Year(dte2), Month(dteMyDate), Day(dteMyDate)))
             dteMyDate = DateAdd("yyyy", intHold, dteMyDate)
             strHold2 = strHold2 & LTrim(Str(intHold)) & " year" & IIf(intHold <> 1, "s, ", ", ")

          Case "m"
             intHold = DateDiff("m", dteMyDate, dte2) + (Day(dteMyDate) > Day(dte2))
             dteMyDate = DateAdd("m", intHold, dteMyDate)
             strHold2 = strHold2 & LTrim(Str(intHold)) & " " & "month" & IIf(intHold <> 1, "s, ", ", ")

          Case "d"
             intHold = DateDiff("d", dteMyDate, dte2)
             strHold2 = strHold2 & LTrim(Str(intHold)) & " " & "day" & IIf(intHold <> 1, "s", "")

       End Select
    Next n
    
    Agecount6 = strHold2

End Function

 

[Rich]

Bob,


"Mike possesses no coding skills and doesn’t want to be
encumbered with any."

You are right. I don't need to be encumbered.

Mike

You already are with the db you have

 

[Kevin_S]

2000 macros + 700 forms + 1200 queries + 300 tables = insane

LOL....

To put this in perspective:

I work in state government (US) and we maintain a human resource management database that contains all the HR details (pay, empy. history, refferals, personal status, complete benefit detail, stock options, retirement packages, etc... for over 50,000 people that is EXTREMELY complex, has millions of records, spans 10 servers, client-server interfaces, web interfaces, etc... is fully normalized (Oracle RDBMS) and it has around 100 tables....

I'd personally really like to see Mike db so that we could all try to help him normalized his data so he can see the forest through the trees...

Kev

 

[Mike375]

2000 macros + 700 forms + 1200 queries + 300 tables = insane

Many of you seem to have missed the point that I made earlier, that is, the data base is in reality several data bases in one.

In the life/disability insurance area people who buy or "hire" professionally made data bases will have the following types

Premium quote system.
Product comparison and product assessment/ratings
Client management
Telemarketing data bases
Gov't compliance requirements for the insurance and financial services industries
Accountancy type data base for income tax and business expense purposes.

I have all of the above in my data base.

There are many advantages having it the one .mdb file. For example the telemarketing part is able to produce the various product comparisons and gov't compliance that we need for the prospect in question.

I am very well aware that the number of tables can be reduced but I prefer to have different tables. A simple example. When prospects are called then certain outcomes mean the prospects are deleted from the main table and appended to other tables. Now I could have all those appended to the one table and queries could separate the various categories of call outcomes. However, I choose to have different tables. On the other hand a bulk of prospects are held in one table for all categories and macros and queries replace records in the main table as they are used.

I will illustrate a part of the data base that is easy to illustrate and then you can tell me where I am wrong.

There are many tables with the following main fields.

Policy Feature..Company A Wording...Company B Wording...Differences

There are several other fields for dates, times and so on but the above four fileds are the key ones.

I have a table for each Company. However, the product comparitor uses a single table and when a Company is selected then the records in the table are appended to the table that the product comparitor uses.

The product comparisona set up so that Company A is the superior policy. However, at different times we will present a comparison where Company A is second best. This often depends on the parameters set for the comparison. I simply make a copy of one of the tables. In each table there are two spare fields. I simply copy and paste the fields with the policy wordings for each feature into the two spare fields. I then copy and paste them back so that they are reversed. The "differences" I then need to write out. For me this is a much easer operation than if all the product material for each company was in the one table. I am well aware that I could do all of the above with query to separate out one group. But I would need a lot of queries.

Just because I cook fish with bread crumb does not mean that I am unaware of grilled fish and the health benefits of grilled fish as compared to deep fried fish done in bread crumb.

99% of the time spent on this data base (by me) is doing what I am doing at present. That is entering policy wording data and key policy differences because of changes in the products by insurance companies. I always have this done within a couple of days of product changes. On the other hand the professionally made product comparitors are invariable one month later and that is preceded by an email warning you that the data on policy ABC is no longer current.

Mike

 

[Mile-O]

Do you not even feel that Access 95 is lacking so many features compared to the late{r/st} version{s} of Access?

What about support? Should your database [no will; just should] pack in and die then who are you going to call to fix it? This is a question of technological support too.

And, then there's size. A database does have a maxium size (2GB in A97 upwards - I don't know about A95) and all these extra object definitions will be contributing to that size.

 

[Kevin_S]

Mike-

Do you have referential integrity set up between your tables?

I'm guessing no and that what you have set up is more like 300 spreadsheets stored in an mdb file with macros moving records between spreadsheets...

Regardless -

I could take the time to site here and type out a million reasons why your system is flawed, I could try and explain why having a table for each company is wrong, how to make it work better, etc.. but in the end you will disagree - tell me I don't know what I'm talking about - tell me how its different for you in your case (which it is not by the way), tell me how its different in the insurance business (its not), and then state that I'm attacking you - so...

good luck to you with your endeavors - I wish you the best of luck...

Kevin

 

[Mike375]

Kevin,

From your posting:

Do you have referential integrity set up between your tables?

I'm guessing no and that what you have set up is more like 300 spreadsheets stored in an mdb file with macros moving records between spreadsheets...

No, not at all. In all cases where there is a One to Many (of which there are heaps) I have a macro open the many form to match the ID number on main form and then a series of Setvalues run that put ID number in, persons name (no I don't need it but I just like to see it) If there is no exsisting record for the ID number then of course the form opens blank.

I have very few queries that join tables. For example, where mail outs are done i do not use a query to link policy holder and policy benefits. Rather I have a query (sometimes two) that are linked into a Word document and policy holder details insert via Bookmarks, for which I have to use Visual Basic. That stuff just managed to creep in

Regardless -

I could take the time to site here and type out a million reasons why your system is flawed, I could try and explain why having a table for each company is wrong, how to make it work better, etc.. but in the end you will disagree - tell me I don't know what I'm talking about

Not so at all. I already know that most of you know far more about Access than I will ever know. But at the end of the day changes take time to impliment and doubly and triply so when I would also have to learn about it. If changes are easy then I will do them. For example I was using this for some query criteria from a form

>=[Forms]![Attempts]![1] And <=[Forms]![Attempts]![2]

Mile O Phile suggested another way, from memory it was

Between >=[Forms]![Attempts]![1] And <=[Forms]![Attempts]![2]

I changed mine to his, but it took all of 5 minutes with a copy and paste into a few queries.


- tell me how its different for you in your case (which it is not by the way), tell me how its different in the insurance business (its not), and then state that I'm attacking you - so...

The facts are that there are differences, many are just because of my choice, the way I go about the business. Some will relate to the nature of the business. A simple example might be a diary for telemarketing Vs a diary for a medical specialist. For the medical specialist most appointments will be made with someone who is not already in the data base. In my case the names are already there so a facility to make entry of the name is of zero value to me. On the other hand my diary is being used in a sales situation whereas the Drs diary is totally opposite.

I think what you are saying (and others as well) is that if you are cooking steak then cooking fish will be similar and it is ideal if the pots and pans are clean etc and etc.

good luck to you with your endeavors - I wish you the best of luck...

Thank you

But something you might consider which is dollar and time etc.

Let's just pretend I could click my fingers and have the knowledge that you and other have and all by next Monday. If I nows sit down and start to make the changes the time will be very considerable. If I pay someone to do it it will cost plenty and unless I also learn all the coding I won't be able to make the on the run changes.

Now if the case was one where the data base was limiting what we could do then of course things would be different. As I said above 99% of time spent on this data base is changing policy wording entries as insurance companies change their policies and there is no way around that except on the keyboard.

Mike

 

[Mike375]

Kevin,

A PS.

One reason I have a lot of forms is because many are variations for appearance.

Telemarketing results can be influenced by the screens. Thus I might have 20 forms and simply save as Export in the data base and with the appropriate form name.

Mike

 

[KKilfoil]

First and last post to this thread:

At the risk of putting a figurative hand in the middle of a catfight:

Both sides on this 'debate' are right, and both are wrong.

Mike can make his application ANY way he likes, so long as he is content with the way it works. Although he may actually benefit in the medium to long run by 'doing things the right way', he chooses not to do this for reasons of expediency.

Everyone else can do things their own way, and smugly tell themselves that Mike is eventually going to run into an unfixable problem with his approach. They may be right. Only time will tell.

Mike can even sell his thing if he wants, but it is HIS reputation which suffers if it doesn't work for others. Any reasonably knowledgeable IT Pro working for the purchaser will look at his application and come to their own conclusions as to its 'correctness' and 'supportability'.

{I do have one caution for Mike: if the purchaser insists on a db compatible with the 'latest version' of Access, Micro$oft has threatened to abandon support for macros for a few years now, in future versions.}



What bothers me about this thread is:

1) The fact that many experienced forum members refuse to accept that Mike truely believes that 'his' way seems to work for him right now. Please let it go (and pray that you don't have to inherit a requirement to support his application someday).

2) Mike has taken much of the well-intended commentary (solicited or not) on his techniques as an attack on him, and attacked back, leading to a 'polite flame-war'. Please let it go.

=================================================

On the subject of the original poster's question, I have made Access Db's with only 1 table, 3 queries, 1 form, 1 report, and 0 modules; and I have made db's with 22 tables, 140 Queries, 28 main forms, 11 main reports, and 9 modules (I have a habit of jamming many routines into one module), c/w FE/BE structure.
The size varies with literally every application, and I suspect that two 'expert' programmers can arrive at slightly different db sizes to solve exactly the same case.

I don't think that there is any such thing as 'typical' when it comes to Access db's

The nice thing about Access is that it accomodates a large range of db scales well, comes with an almost automatic upgrade path to a fancier MS product, and can serve well as an interface via ODBC with almost any 'full-blown' RDMS such as Oracle. All that, and it is easy to learn and use.

 

[Mike375]

Mile,

From your posting:

Do you not even feel that Access 95 is lacking so many features compared to the late{r/st} version{s} of Access?

I am extremely resistant to changing software. I have used A97 a little bit and have done some things with A2000 over the phone. But I guess the bottom line is that I am not walking around thinking "if only I could do this". The one problem I do have with A95 is that there are some conflcicts with Windows XP, but I get around those problems.

What about support? Should your database [no will; just should] pack in and die then who are you going to call to fix it? This is a question of technological support too.

I have a large number of copies of the .mdb file and they are never more than a day old so I guess that offers me some protection.

And, then there's size. A database does have a maxium size (2GB in A97 upwards - I don't know about A95) and all these extra object definitions will be contributing to that size.

The books say 1GB. It was about 80mb compacted for the last 3 or 4 years and has recently grown to about 90mb compacted. It never has a real lot of records in it as the bulk of prospects names are held in other .mdb files and as prospect's names are consumed those people that don't become clients are moved to other .mdb files.

Apart from copies of the data base I probably have about 15 other .mdb files and they all store prospects names and also do a few things with those names. For example, one of them changes all phone numbers to the same formats, removes spaces, brackets etc. We do this so we can check for duplicate numbers.

We also sell a lot of names to other people after we have called them. That is another reason why there are lots of tables etc because used names are output to different tables and then exported to other .mdb files.

So as you can see the main data base remains fairly static in size because it is in reality a processor rather than a holder of records. Perhaps you could think of it as being like the kicthen sink in that 1000s of gallons of water pass trough it each year but there is never more than a gallon of water in the sink at any given time.

Mike

 

[Rich]

Micro$oft has threatened to abandon support for macros for a few years now, in future versions.}

along with DAO, ADO, DoMenuItem....................................

 

[Mike375]

along with DAO, ADO, DoMenuItem....................................

Does anyone know if Access XP has all the macro functions?