Need an advice

[Guest]

I have two databases, I would like to combine them to one, (instead of using
two, use one) but before than I do this I first wanted to get an advice from
you.

Both databases have one tblMain that have one 63 columns and the other 58
columns. The question is if I add these columns all together, is it too much,
or will this slow database performance, or reach its 1GB limit soon (I enter
100 records per day, and empty records every fiscal year), or will there be a
way to link them so that when I enter a record I have all those 121 fields.

Please advice?

Thank you,
Adnan

 

[Allen Browne]

Access can handle 121 columns, but it will not be a correctly designed
database, and it will not be easy to create queries or reports. It will
basically be a fat spreadsheet stuck in an Access interface.

Some of those columns will be repeating (such as Week1, Week2, ... or
Employee1, Employee2), or else will have repeating values (the same value
repeated several times in different rows.) The repeating columns need to be
broken into a related table. The repeating values need to be assigned to a
lookup table.

In this way, you will end up with a relational design. You will have several
tables related to each other via one-to-many relationships. Doing anything
else with Access is a bit like buying a new car to park in your front yard
to grow plants in: you can do it, but you don't get any of the benefits of
the way the product was designed.

If you want to read up on how to create a relational database, search for
"normalization". Here's a starting point:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

 

[Joseph Meehan]

I have two databases, I would like to combine them to one, (instead
of using two, use one) but before than I do this I first wanted to
get an advice from you.

Both databases have one tblMain that have one 63 columns and the
other 58 columns.

It is extremely unlikely that a table with 63 or even 58 fields is
properly normalized (designed). Usually they were built like a spreadsheet.

The usual situation is a family situation. You have one parent and that
parent has many children. So in a spreadsheet you make columns for each
possible child Parent, Child1, Child2, etc.

In a relational database like Access you have a parent table and a child
table. They are related by ID number so the parent table has

Parent, ID (along with maybe address and phone number etc)

The child table has:

Child, ID, ParentID. With the ParentID connected to the ID field in the
Parent table.

The end result is a much easier to use, faster, and smaller database.