A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

change field properties, reflect in query



 
 
Thread Tools Display Modes
  #1  
Old November 9th, 2004, 02:17 AM
SueP
external usenet poster
 
Posts: n/a
Default change field properties, reflect in query

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.

  #2  
Old November 9th, 2004, 02:08 PM
Bruce
external usenet poster
 
Posts: n/a
Default

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.

  #3  
Old November 10th, 2004, 12:40 AM
Sue Potter
external usenet poster
 
Posts: n/a
Default

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.

  #4  
Old November 11th, 2004, 01:46 PM
Bruce
external usenet poster
 
Posts: n/a
Default

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.

"Sue Potter" wrote:

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.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't get Access to run a query with a time field RonSLevy Running & Setting Up Queries 3 September 29th, 2004 04:04 PM
Print Taher Setting Up & Running Reports 1 August 31st, 2004 09:07 PM
Hidden files in Ms-Query cause ODBC connect errors or Query is wac needyourhelp General Discussion 4 July 12th, 2004 09:38 PM
Newbie? Do I use Report or Query John Egan New Users 11 June 28th, 2004 08:31 PM
Change "Company" field in File Properties Trixie Worksheet Functions 1 October 28th, 2003 01:43 AM


All times are GMT +1. The time now is 07:12 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.