over 110k records in a table

[Ngan Bui]

I have 40 tables in a BE mdb file. The db size is about
60MB. There are 3 tables that takes up the majority of
the db file size.

The main table tblPerson (person info) has over 73k
records (size - about 21MB by itself).

The subtable of tblPerson is tblAddress. It lists the
addresses and times of all the pick ups done for a
person. tblAddress has over 112k records (size - about
11MB).

The subtable of tblAddress is called tblETA which logs the
info every time a person calls in about an address. Some
times, we can get 2-3 calls in for one address. This
table has over 122k records (size about 19MB).

So, if you add the three tables up, it's over 50MB (that's
over 80% of the total file size).


Would this be something Access is capable of handling? I
remember ppl saying it's more of the size, not the number
of records that I should worry about. Recommend archiving
some of the records? The records are about 2.5 yrs span.

Thanks.
Ngan

 

[John Vinson]

Would this be something Access is capable of handling?

Well, is Access handling it now? You can answer that better than we
can!

That's getting moderately large. Whan you have 1.1 million records,
after 25 years, you'll have what I'd call a big Access table. But the
exact size is less important than the optimization of your queries,
table structure, number of concurrant users, network stability, etc.
etc.; when performance begins to drag or corruption becomes a hassle,
consider archiving. Frequent compaction is essential with this busy a
database!

 

[Tim Ferguson]

I have 40 tables in a BE mdb file. The db size is about
60MB. There are 3 tables that takes up the majority of
the db file size.

Forty tables is either a very sophisticated design -- in which case you
probably should be answering questions here rather than asking them! -- or
else there are severe design problems. Phrases like "subtables" strongly
suggest the latter...


If you have tables like FrenchCustomers, GermanCustomers, EnglishCustomers
etc. then this is storing data in table names and is a Bad Idea. You might
like to consider your design in order to take advantage of Access's
relational capabilities.

So, if you add the three tables up, it's over 50MB (that's
over 80% of the total file size> ).

How do you know? There are no published methods for finding out how much
space a particular table takes up in an mdb file. It's likely that
multiplying the notional recordsize by the recordcount will be out by
miles: indexes will make it bigger; unused text space will make it smaller;
and there are all kinds of overheads and page links that you cannot know
about at all.

All the best


Tim F

 

[Ngan Bui]

well, this db holds information for our riders, providers,
drivers and employees. tables related to our riders has
the majority of the number of tables.

Because I have included all the people (riders, drivers,
custserv reps, etc) in the table tblPerson, tblPerson has
one-many relationships to 9 tables (CallAddress, RoadObs,
DispResvObs, Incident, RideMonitor, Hire, CertAppeal,
DispatchLog, Suspension). Under the CallAddress, has a
relationship with two tables to track calls. RoadObs and
DispResvObs is related to two tables. tblProvider has 3
tables it's related too.

I have a few type tables (like persontype, incidenttype,
Calltype). Maybe I can combine them into one type table
with a field to distinguish what type they are.

Guess I'll look over my table design to see if there is
any other tables that can be combined.

Ngan

 

[John Vinson]

Should I combine the type tables into one?

I would recommend NOT doing so. Any advantage you'ld get would be
minimal, and it would make doing the maintenance a fair bit harder.
Fifteen little "lookup tables" is not at all unusual!

 

[Tim Ferguson]

If I were to make one table called tblType and have PK,
TypeGroup, and TypeName, I could combine all the type
tables into one.

Like John says, there is no advantage to this, and in any case CallTypes
and CityNames and IncidentTypes are all different entities. As you point
out, you cannot then use the database engine to prevent someone setting
City=LateTrip or TypeOfIncident=LosAngeles..


Please don't misunderstand what I was saying earlier: having a large number
of tables is by no means a bad thing -- unlike, for example, having a large
number of fields in one table. I made some wrong assumptions based on your
first post and smelled an excel-sheet type of design (which would have been
a bad thing) but you have put me right, and there is nothing you have said
that indicates the kind of problems I was imagining. Specifically, there is
no reason to collapse entities just in order to get the the table count
down.

Whew!

All the best


Tim F