Make Table: Record Too Large

[jake]

I need to create a table with approx 125 fields (all text fields, no
memo) from three tables -

If I add more than 70 fields into the make table query, I get the
"Record Too Large" error. Any suggestions?

 

[jake]

It is a "record of funeral" database. It will store all the
information for each funeral. Including name, address, s.s number,
service information, doctor information, etc. It may not be perfect,
but it will be perfect for my needs. The database will not store have
many records, only 2 to 3 thousand.

And I already have made database, and it works great! I am in the
process of importing all our funeral records from our old program.

 

[Rick B]

I'm still lost. You say you are trying to "make table" from three other
tables. Just leave the data in the three separate tables and link them with
a common field.

If you insist on just having one big table, then why not just use a
spreadsheet?

If you ignore good database design rules, it generally will come back to
bite you down the road. It may meet your needs at the moment, but you will
have trouble at some point.

What if you have several "clients" that had the same doctor? Do you enter
all the docotr information for every record? If it were me I'd take a few
extra minutes to build it right.

All that being said, it would appear that you can't do this with the
make-table query. It appears there is a limit to the size of the make table
query.


Rick B

 

[jake]

Thanks Rick, I think I will follow your advice and go back and
normalize my tables. Thanks for setting me straight. Jake

 

[John Vinson]

I need to create a table with approx 125 fields (all text fields, no
memo) from three tables -

If I add more than 70 fields into the make table query, I get the
"Record Too Large" error. Any suggestions?

Rick's quite correct. The error you're running into is one of Access'
built in limitations: a single record in a table can have no more than
2000 characters (4000 bytes, for A2002 and later) summed across all
fields.

Normalize!

John W. Vinson[MVP]

 

[John Spencer (MVP)]

You do realize that the maximum size of a record is 2000 characters. So the
record too large error probably means that you are trying to stuff too many
characters into one record. There is the same limit on the number of characters
in a row of a query.

One way to work around this is to use memo fields (which I think take 8 bytes),
but the penalty there is you loose the ability to sort and aggregate on those fields.

You really do need to break your table structure out differently.

You probably need a "deceased" table. A "Doctor" table. A service table. and
maybe others as well.