tying data to multiple (or no) records

[bethtop]

Is there a way in Access to tie the same information to multiple records? Or to automatically populate a field in multiple records with information entered into only one place?



I have a database with info about buildings in several different cities. I have two problems:

1. Sometimes, there is information that applies to all the buildings in a city. I want a way to enter it only once and have that info somewhere on the record for every building in that city.

2. Sometimes I have important information that applies to a city, but no particular building in that city. It would be better if this info was not tied to every record in that city--it's a different kind of info so doesn't really make sense with the fields in the building records form. Any ideas for how to work this? Should I create a record for "general info" for a city and just ignore the fields except for comments? I could also create a new form with fields more appropriate to this info. If I did that, would the records still show up in my master table (this is desirable)? Both of these ideas seem kind of janky to me.

Access noob here (1 week at it). Please explain everything.
Thanks so much!
Beth

[June7]

Presume you have a table of Cities. Can have a field in that table for this city-wide attribute. Have a table of records that are choices for this field.

If you want to allow multiple choices for each city then have multiple fields (non-normalized structure) or a related child table:

tblCityDetails
CityID
InfoID
other fields as desired (Comment, EnterDate, EnterBy)

[Dal Jeanis]

The basic way to think about it - called "normalization" - is that different tables each have a single type of related data, and you build a "query" to relate the data to each other.

If your building is in a city, then you can build a query that gives you that building's attributes that it gets because it's in that particular city. It's not on the BUILDING record, but the BUILDING record has a city field, and the CITY record has the items that it's buildings all inherit.

If a city has a different type of information that's not related to the buildings, then you wouldn't add that unneeded information to the query for the buildings.

Go over to Reoger Carlson's site at http://rogersaccesslibrary.com and review his basic tutorials on database design and application design. A few hours working his sample problems now will save you dozens of hours of rework later.