List of last used tables (1 Viewer)

Thales750

Formerly Jsanders
Local time
Today, 07:10
Joined
Dec 20, 2007
Messages
2,061
Pat, Clearly I am a fan of Access. I have 10's of thousands of post on the Internet on the subject and know more about Access than 99.9% of so called Access developers. So do not tell me who is a fan of Access. But it is what it is. The development environment looks the same as 25 years ago except of stupid things like multi value fields and split forms. So there are times when it is time to move on.

How did you derive the 99.9%?

Building database is so much less about technologies, and so much more about business models, and psychology. After that, data structure is more important than controls and their functions.

But, back to the 99.9% thing. Is that 99.9% of, code writers, table makers, UI designers, requirements gatherers, testers, trainers, or documenters?

Or, you in the top .1% of all that?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:10
Joined
Feb 28, 2001
Messages
26,996
If I remember my statistics correctly, 99.9% of all extreme claims are slightly exaggerated.
 

BeeJayEff

Registered User.
Local time
Today, 04:10
Joined
Sep 10, 2013
Messages
198
There are over three hundred tables.

I'm intrigued by this and find it hard to countenance the design of such a system. The biggest db I look after only has about 30 tables of which about 10 are fairly static lookup tables - but the relationships diagram is quite complex enough, and it is properly normalised.

Is a properly normalised Access database with hundreds of tables a frequent occurrence ?
 

isladogs

MVP / VIP
Local time
Today, 11:10
Joined
Jan 14, 2017
Messages
18,186
I'm intrigued by this and find it hard to countenance the design of such a system. The biggest db I look after only has about 30 tables of which about 10 are fairly static lookup tables - but the relationships diagram is quite complex enough, and it is properly normalised.

Is a properly normalised Access database with hundreds of tables a frequent occurrence ?

It's certainly not uncommon

My largest database has around 330 tables mostly in a SQL BE.
See screenshot in this post https://www.access-programmers.co.uk/forums/showpost.php?p=1553897&postcount=2

It is indeed fully normalised though I'm not sure why you think that's relevant.
In fact, fully normalising is likely to reduce the number of fields per table but increase the number of tables
 

BeeJayEff

Registered User.
Local time
Today, 04:10
Joined
Sep 10, 2013
Messages
198
It's certainly not uncommon

My largest database has around 330 tables mostly in a SQL BE.
See screenshot in this post https://www.access-programmers.co.uk/forums/showpost.php?p=1553897&postcount=2

It is indeed fully normalised though I'm not sure why you think that's relevant.
In fact, fully normalising is likely to reduce the number of fields per table but increase the number of tables

Fair enough - the only time I've seen a database with such a huge number of tables was because it was far from normalised (e.g. one table per month for several years).
 

isladogs

MVP / VIP
Local time
Today, 11:10
Joined
Jan 14, 2017
Messages
18,186
Ah I see what you were driving at now....

The database I quoted has been in continual development for over 15 years, is used in a number of schools and does cover a huge amount of staff, student and whole school data.

At one time it contained even more tables until I removed some old features that we're no longer in use!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:10
Joined
Feb 19, 2002
Messages
42,970
That number of tables in a single database is extremely rare. It happens primarily in two cases:
1. an MRP type system that covers multiple business areas such as the one ridders sells.
2. a poorly designed application that is using tables as spreadsheets.

The largest number of tables I've had at one time was about 120. This application basically ran most of the company. Each time I absorbed a new set of functions, some number of new tables had to be added. I don't think it could get to 330 given what it does now and what it might do in the future but it most like will continue to be expanded.

The second largest was a mainframe CICS/DB2 app that I built a lifetime ago and that one had 95 tables.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:10
Joined
Feb 28, 2001
Messages
26,996
The database that was used to make personnel management decisions for the U.S. Navy Reserve had only about 240 tables - which sounds like a lot - but fully HALF of those were either "translation" tables that converted codes to a longer name or constant-lookup tables to supply numeric rates based on member attributes. Translations like the rank-rate table that converted E5 to Petty Officer First Class or O4 to Lieutenant Commander. Constants like the yearly uniform allowance based on the reservist's location and rating.

Another third of the tables (about 80 or so) related to external interfaces with about 20 other Navy organizations. They weren't needed for the internal processing but we needed places to hold things when we were preparing files for electronic transmission to an agency that used different codes than we did.

So if you do the math, about 20 tables were the active parts - with three being primary: Person, Billet, and Unit.

That is the MOST complex database I've ever seen or worked around and decidely YES it was properly normalized. It wasn't Access, though. It was an ORACLE monstrosity.
 

isladogs

MVP / VIP
Local time
Today, 11:10
Joined
Jan 14, 2017
Messages
18,186
It is a very wide ranging application. If anyone is interested, a summary of it's main features can be found here: http://www.mendipdatasystems.co.uk/school-data-analyser/4584605482

I mentioned in a recent thread that I'd made a decision with that monster database over 10 years ago that I'd regretted ever since.

At the end of each academic year all attendance, reporting and other grade tables were archived into separate tables. In addition when students left school all their data was separated into leavers tables plus of course leavers archived tables.
This made finding the data much faster but significantly increased the number of tables together with additional queries, forms, reports and all the code that accompanied them.
Furthermore the end of year routine had to be checked each year and modified where field changes had occurred.

All in all many hundreds if not thousands of hours of work have been added by those decisions and without rewriting the entire database app, it's ongoing work. Luckily, development work has significantly slowed down as it now has all the features needed by all my client schools.

If the decision had been to keep all leavers and archived data in the original tables, the total number of tables would drop by 80 to about 250 with similar drops in numbers for all other database objects. The FE (approx. 130MB) would be smaller and probably faster.

So whilst it still performs very well, it is a behemoth that taught me a lesson I won't forget.
 

Thales750

Formerly Jsanders
Local time
Today, 07:10
Joined
Dec 20, 2007
Messages
2,061
I was informed last night, by a very good friend of mine, that I misspoke. So there is a new edit in this post concerning ISO Certification.

This system is mission critical for an entire chemical plant.

It manages: Shipping and Receiving, Plant Operations (this is too complex to even comment on), PHAs, NCR documentation and remedial planning and tracking, Lab results including SQC Data pushed out to Excel, Training, Interfaces with Accounting, etc.

The Operations side creates material balances (inventories) from continuous flow equipment, keeps track of levels in all tanks, Railcars, and Trailers. The second part of material balances is to group the various flows into customer orders. There may be a few of the 318 tables that are unneeded, but none of them are being used as spreadsheets.

But after all that, it sees into the future. It creates just-on-time ordering and maintenance schedules to optimize plant production months in advance. It accomplishes this by creating SOPs from historical data.

There's more, but we have created hundreds of pages of docs, Power Points, and videos, to disseminate all the information.

Three years in the making. This has been an incredible experience.

Some really smart people worked on this project, both at the customer's site and here at Clear Lake Data, including a Microsoft Access MVP; without whom, this would not have been possible.
 
Last edited:

Thales750

Formerly Jsanders
Local time
Today, 07:10
Joined
Dec 20, 2007
Messages
2,061
This is the final Count of the new Merged Database.

Query2Object
Type Count
acForm 341
acMacro 3
acReport 130
acModule 11
acTable 12
acQuery 297
acTable 306 Linked
 

Users who are viewing this thread

Top Bottom