Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Is this table to big?

Is this table to big?

Is this table to big?

I'm converting a property management system from PICK to Access. Before I get beyond the point of repair... I have a table containing fields to describe properties: address, bedrooms, baths, garage, number of parking spaces, etc. Included with the other fields are 45 (may grow to 60) fields I call "amenities" (air-conditioned, balcony, ceiling fan, self-clean oven, wall-to-wall carpet, etc.) Each of these fields is true or false based on a check-box control. Is the fact that there are so many fields in one table a good reason to make a separate table for the "amenities"? I'd like to avoid separating the tables, because it would take me probably four hours to figure out the relationship of the "tblAmenities" to the "tblProperties": 1 to many? 1 to 1? Many to Many? Argh!
Your advice, as always, would be helpful.
Gus Brunston (an old PICKer).

RE: Is this table to big?

I wouldn't think breaking the table in two was necessary; 60 is not a huge number of fields, especially if they're all Yes/No fields.

But if you decide you want to break it, duplicate the primary key field(s) in your Amenities table. The relationship will then be one-to-one because the primary keys are identical.

When you want the basic data without amenities, use the main table as your recordsource. When you want the amenities as well, use a query that joins the main table and the Amenities table on the common key.

Rick Sprague

RE: Is this table to big?

Good morning Gus!
100% with Rick here. 60 fields isn't so bad...geeze you're allowed 200+ right? As Rick states though, if half of the fields are "vacant" you may want to pull them out and link them one to one. It's really just a space saver. You could get down right fancy and make the table of amenities with an "amenityID" and a description and a table of properties with a propertyID and the basic info, then the "PropertiesAmenities" table which would hold the propertyID (many) and the amenityID (many). That's the formally correct way to do it but depending on your real needs, might not be worth the effort. What's really important is to figure what you want to do with the data later. A good foundation will hold the whole heap up a lot longer and better than a weak one.

Oh, if you chose the latter, you can strip data from your big table pretty quickly using some append queries set to unique values.


RE: Is this table to big?

Gord and Rick:
Thank you both very much. Since the number of records will probably never exceed 1000, and someone else is going to end up maintaining the system and database, I'm going to leave the amenities in the properties table for the "ease of query." Besides, we woke up to over 10" of snow in Denver this morning--no time to divide controls into separate tables!
Thanks again.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close