Creating a Form for Dynamic Table

[Ashe]

I have a dynamic Access table, in which the columns of the table may change over time depending on the user's preferences (additions or deletions, ~100 columns). I am trying to make a form for this table where the columns names would appear on the form in a vertical manner so an individual entry could be reviewed more easily then scrolling across the entire table. Given the fact that the columns could change, I can't fathom how to build the form and then match it to the data? I was thinking this might be a query to get the column names to set up the form's rows, and then some vba/sql code to match the current columns with the right row data depending on the selection of the row. Does anyone have any experience with this? I hope this makes some sense...

[orange]

I'm not following the concept. A table with 100 fields is extremely rare.
Can you give us an example with some real data values of what you are talking about?

It sounds a little like you're reinventing the query design wizard??????????????

[amrut]

table may change over time depending on the user's preferences (additions or deletions, ~100 columns)

Out of curiosity, I would like to know, if you can share, why do users need to add or delete fields in a table ? Experts in the forum will surely guide you.
You can ignore my post.

[Ashe]

It does seem like a reinvented query of sorts- it's a prioritized query of many historical data sets that summarizes the most recent environmental data of the park. So for each area of the park (a row in the table), there's a column for the park's primary tree type, secondary tree, soil content, year that soil content was last analyzed, turf type, etc. etc.

Further down the line, they could end up getting another set of data. For instance, this set of data might say describe the fertilizer content for each area. And then they would want to see this data in this summary of the data sets.
On the other hand, they could decide that the secondary tree type column is never needed, and delete it from being in the query- and then it would no long be in the table's columns.

For each park area, it's so much data to scroll through, so the form would be a drop down list of the park areas (1000s), and then they could read the list of attributes for that individual area.

[orange]

many historical data sets

would seem to mean you still have to open these data sets somewhere to get the relevant data.

My suggestion would be to review some of the techniques here and dynamically adjust the sql via forms to select a specific report, sort order etc.
This is part 6 of his tutorials -- you can see them all for ideas.

[Ashe]

I've built in an import function to bring in any new relevant data. There's a table that holds all the park's query attributes (columns) that stores what fields are wanted in the ultimate query and where they come from. A code is used to fetch this data and build the query table.

I wish Access had some sort of grid function (like sql server) where I could take this table and rotate it. Maybe some sort of vertical continuous form?

[orange]

How about showing us the table design and some sample data?

[John_G]

Hi -

It sound as if you may have some data structure issues. You may want to look at putting different types of data into separate tables, for example fertilizer content. For example, the same area in a park could have different fertilizer content data for different years - a separate table would be required.

But, as has been suggested, show us what you have now and we can give you some ideas.

John