Using Multiple Tables With One Form

[Guest]

I have approx 55 tables that have been imported from Excel into Access. I
have one form that allows me to imput and display information. How can i
access each of these tables using only this one form (is this possible)?

 

[Allen Browne]

If the field names and types are identical in each of the 55 tables, it is
possible to write code that assigns a different table as the RecordSource of
the form.

But a better solution would be to add all these to the one table, with an
extra field to distinguish whatever is the difference between the 55 of
them. You can then examine them all in your form, or filter to form to one
of those 55 values.

 

[Guest]

Allen - Thanks for you response.
Using one large table would certainly be the easiest thing to do.

I am somewhat new to Access, and I am a little concerned about the size of
the table if I put everything in one table. All 55 tables contain exactly the
same fields, (approx. 45 fields) and the number of records ranges from 25 in
some tables to almost 200 in some tables. A total of 4500 to 5000 records
with 45 fields each.

Like I said I am new at Access. I don't know what the size limitations for
Access are and I don't know if this would be considered no problem at all for
Access or if it is large enough that it would cause problems.

 

[Douglas J. Steele]

4,500 to 5000 records is a very tiny table to Access.

45 fields, however, is often indicative that your database hasn't been
properly normalized: it's not that common to have more than, say, 20 fields
in a single table.

 

[Guest]

Douglas - thank you also for your input.

The good news is, I am glad this is considered small. The bad news is 45
fields is what I have cut it down to as a bare minimum. Each of these tables
were imported from an Excel Spreadsheet that had about 60 - 70 fields or
columns.

In addition to this there is a subform that goes with each item or record
that has an additional 10-20 records with about 5 fields each, but these are
in another table.

Are the 45 fields a problem?

 

[Douglas J. Steele]

As I said, it's very unusual for a table in a properly normalized database
to have that many fields, but it's not impossible.

You might consider checking some of the resources Jeff Conrad has at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101
to learn more about how to normalize a database.

 

[Allen Browne]

Just to clarify Doug's point, you are not creating a problem for Access by
using 45 fields. It will cope with that fine.

But you may be creating a problem for yourself when it comes to querying the
data. If you have some repeating fields, e.g. Product1, Price1, Product2,
Price2, ... then the data is not normalized, and you will find it difficult
to write queries to get the results you want.

The link to the articles at Jeff's site will help you understand how to
create related tables with lots of records rather than one spreadsheet-like
table with lots of fields.

As far as records go, millions of records is starting to get large for
Access.

 

[Douglas J Steele]

Thanks for the clarification, Allen. I wasn't very clear, was I?