Need help modifying data for form or report in ms access
Demographics.mdb 
Hello: I am trying to create a form that will show demographics information for certain properties that we own, These properties are identified by the txtID field in my table. I then have a series of demographics information for that property. These are designated by 6 datapoints for each property. There are 230+ fields for each datapoint. The form or report that I am trying to accomplish is attached as well as a copy of the db. My problem is that the data is not really tailored for easy access as a record source for the form. I have tried to write individual queries for each of the datapoints and then join them by I get the error message "too many fields" . I was hoping to write a query or vba code instead of having a huge data entry or cut and paste effort if I could but am stuck on where to begin. I think if I could make a table with the following info I could make it work.
txtID CategoryCode(or Demographics Description) DP1 Value DP2 Value DP3 Value ...
Any suggestions on where to begin? I am sure you may need further explanation so feel free to ask. Thanks in advance.
Hello: I am trying to create a form that will show demographics information for certain properties that we own, These properties are identified by the txtID field in my table. I then have a series of demographics information for that property. These are designated by 6 datapoints for each property. There are 230+ fields for each datapoint. The form or report that I am trying to accomplish is attached as well as a copy of the db. My problem is that the data is not really tailored for easy access as a record source for the form. I have tried to write individual queries for each of the datapoints and then join them by I get the error message "too many fields" . I was hoping to write a query or vba code instead of having a huge data entry or cut and paste effort if I could but am stuck on where to begin. I think if I could make a table with the following info I could make it work. txtID CategoryCode(or Demographics Description) DP1 Value DP2 Value DP3 Value ...
Any suggestions on where to begin? I am sure you may need further explanation so feel free to ask. Thanks in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Scott McDaniel (EE MVE )
You could perhaps build subreports for each of your "datapoints", and then embed those subforms into a single "main" report. This would get you around the "too many fields" issue.
ASKER
I tried the solution brought up by TinTombStone and after about two hours of futile dataentry I came to the brilliant conclusion that I was just masking the fact the the tables for this database needed to be fixed as they were of poor design. They worked perfectly for the form that they were intended for but not for this one. I decided to concentrate my efforts on the original spreadsheets and with about two hours of creating macros was successfully able to get the data into a format that makes this form easy to produce. I was able to create the form with a datasheet view and thus elimitated many hours of coding the individual fields on the form. I also also sure that the new tables will allow for changes much for quickly with the new design. I thank TinTombStone for his efforts and while I did not ultimately use his solution I would like to award him the points as it was a viable solution given the variables he had to work with. Thanks again!
Hi Twands, thanks, but you didn't actualy award me the points! :-) Anyway, you are right, good table design will alleaviate most of your problems. Good luck with it.
ASKER
There was a mistake in the the awarding of the points. They should have gone to TinTombStone. Sorry for the confusion.