RE: change field properties, reflect in query

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Sue Potter (SuePotter_at_discussions.microsoft.com)
Date: 11/09/04 

Date: Tue, 9 Nov 2004 15:40:04 -0800

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" wrote:

> 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.
>
> "SueP" wrote:
>
> > 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.
> >

Relevant Pages

  • Re: Join to one of two tables (based on a value in the source table)
    ... > yourself a query that returns apples or octopi. ... or the database in which it resides). ... for items not found in that lookup ... is set to 1, joining tableLists.UnitName to tblLists_lookup2.UNITNM. ...
    (microsoft.public.sqlserver.programming)
  • Re: Join to one of two tables (based on a value in the source table)
    ... >> yourself a query that returns apples or octopi. ... > or the database in which it resides). ... > table to store similar information, for items not found in that lookup ... I need data from a table that doesn't belong to me ...
    (microsoft.public.sqlserver.programming)
  • Re: Four Report Problems
    ... I changed to the query like I thought I had, ... I just typed the names of the banks in the Lookup tab. ... Field List into the Report. ... Don't use an expression to format a value. ...
    (microsoft.public.access.reports)
  • Re: Row cannot be located for updating error (ADO)
    ... "DBEdit1 the field double clicked) in the OnDoubleClick event is writing ... directly to the database but the data aware controls are writing to the ... >>lookup value) dependant on the users selection this is then saved to the ... > I assume your query is a Join using fields that come from the table your ...
    (borland.public.delphi.database.ado)
  • RE: Query for Multiple Lookups
    ... Post the above query SQL. ... proprietary database. ... database has a "master" lookup table that includes every type of lookup value ... subsequent field to another instance of the master lookup table (using same ...
    (microsoft.public.access.queries)