change field properties, reflect in query

[SueP]

I am a new Access user--don't use code, just the wizards & design views.
I have two main tables with a 1-to-1 relationship (couldn't fit all the
fields in one table--property value too large), and several lookup tables. I
created multitable forms using a query that contains both main tables. All
was working well until I had to change/add some of the lookup tables and
establish some new relationships between some fields in the main two tables
and the lookup tables. The query is no longer recognizing the formatting for
the field types (yes/no check box, lookup combo box, etc) or the default
values for the fields in one of the main tables. I have checked the
relationships. I have tried re-creating the query, but to no avail. Any
suggestions? p.s. It's in Access 2002.

 

[Bruce]

If you used two tables because you could not fit all of the fields into one
table, it is likely you are doing something wrong with your database design.
A general rule for table design is that you should be able to describe the
tables function in a single sentence without using the word "and" (unless for
name and address or something like that). If you are keeping track of
customers and orders, or students and courses in one table, it's time to step
back for another look. A one-to-one relationship is very specialized, and I
wonder if that is what you need.
It would help if you could describe what you need to do with the database,
what you are looking up with the lookup lists, and so forth. I expect the
folks in this group can help you set up a clean, smooth-running database, but
will need some details in order to offer specific guidance. Much of what I
have learned about Access I learned right here, but there is definitely a
learning curve. I wish I had asked a few more questions before undertaking
some of my earliest projects, but I didn't know about this group then.

 

[Sue Potter]

Thanks for your response.

I am using Access for the data entry of surveys--the data will be converted
to SPSS for analysis in flat file format. I'm using lookup tables, validation
rules, etc with forms designed to look exactly like the surveys in order to
minimize human error in data entry. In total, I have 230 distinct fields from
the survey, most of which are linked to other tables for lookups, some are
yes/no check boxes, a few text fields, and a few memo fields. Each respondent
only does a survey once, so I'm not sure it's possible to make the design
more efficient-e.g. there is never a scenario where one case/respondent has
multiple records for one field.

When I was creating the database, I kept getting the error message "Property
value too large." On the MS Help & Support website, there was an article
that suggested this was due to too many fields and/or the Default Value
property was set for memo fields. The article suggested to the split the
tables with a 1-to-1 relationship (which I did figure out) and change the
memo field default value property (which I couldn't figure out). I also
turned off the Track Auto Name feature because it was severely slowing down
the functioning--I would copy and paste fields (then rename new ones) for
survey questions that had the same response options (yes, no, don't know,
etc) to save on time and my own potential to make mistakes.

I ended up restarting from scratch once, and it all worked fine. Then it was
decided by the project group that we needed to change some of the response
options, and when I then modified existing lookup tables and created new ones
(to which some fields are now related), this is when I started to have teh
problem with the query. When in data view for the tables, I see the check
boxes, the drop down boxes, etc. When in the data view for the query, I see
those formats only for some fields.

 

[Bruce]

I have done some checking, and I realize that one possible source of your
difficulty is that you may have deleted fields, but Access is still holding
space for them, so to speak. Click Tools > Database Utilities > Compact and
Repair. Another thing you could try is creating a new blank database and
then exporting objects (tables, queries, forms, etc.) from your current
database to the new one. Access has a maximum of 255 fields, so 230 should
not be a problem (at least not to Access, although the people taking the
survey could get cranky . In fact, I'm not quite sure why you need two
tables, although it does provide you the flexibility to expand the
questionaire in the future.
People in this forum are likely to suppose something is amiss with a
database containing so many fields. In your situation you need many fields,
so in order to avoid responses containing assumptions such as I made it would
probably have helped to explain the large number of fields.