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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |
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 12:43 AM |