How can I return a limited set of table fields conditionally?

[Guest]

I have a table with many fields (50) for the purpose of editing a
distribution list. Generally however, I want to work with a subset of fields,
say between 2 & 20. How can I return just the subset of interest to show only
those fields meeting certain criteria? That is, I only want to see and work
with those fields that have data? This resultant set of fields needs to
change with each select or update query. A more permanent table with all the
fields, is updated with the newly edited data after which the editing table
is reset.

Using Access 2003 & WinXP

Thanks for any suggestions

 

[Jeff Boyce]

?50+ fields? That's unusual in a well-normalized table design. It may be
that the (better) answer to your question involves relational data design.

Please provide an bit more detail on what you are storing in all these
fields -- it could make a difference in how the 'group suggests you handle
the issue you posted.

 

[Guest]

Sure. I understand your perplexity and I'll try to explain clearly.
There are 50 test locations to which seed must be assigned for 100s of plant
varieties. Locations require different amounts of seed. Not all varieties are
assigned to all locations in any one year nor is the distribution for any
single variety handled in a single editing session. However, usually groups
of locations are filled at a time for groups of varieties. Each spring, as
available seed diminishes, changes must be made in the distriution amount &
location. While Locations are usually in a field of there own, as a 2-letter
code, for the purpose of distributing seed, there are reorganized into fields
to simplify the editing/distribtion process visually. The editing query
displays the changes dynamically with different distribution decisions. When
the editing session is completed, a semi-permanent record is updated and the
Location Template is cleared.

At the start of a session, the Location Distribution template with 50 fields
is populated with standard amounts to be distributed for just a subset of
locations. These are determined via parameter query. So in any one session,
only a subset of locations is of interest, but all locations remain visible
in the template. It would simplify the distribtion step if during editing,
only the fields of interest were visible.

I hope this is not too muddled an explanation.

Thanks for replying.

 

[Jeff Boyce]

If I understand, you could also use:

trelLocationDistribution
LocationDistributionID (primary key, ?Autonumber)
LocationID
FieldID
DistributionInfo (I'm still not clear on this... but don't need to
be)

Notice that this table only has 4 fields, and each of your 50+ fields can be
reflected as rows.

I suspect your design was created for a spreadsheet. I'm more certain,
after your description, that you would be able to make much better use of
Access' functions and features if you stepped back from what you've done and
looked into normalization (see Access HELP). Otherwise, you might as well
be using a spreadsheet...

 

[Guest]

Thanks. I'll try that. I do struggle to eliminate the dreaded, spreadsheet
thinking.

 

[Jeff Boyce]

Nothing dreadful about Excel -- it is a marvelous tool for the purposes it
is designed for. But you wouldn't use a chain saw to drive nails, would
you?

Good luck

Jeff Boyce
<Access MVP>

 

[Guest]

Jeff,
Is it a necessary trade-off with Access that in order to have the relational
attributes one must give up the visual display of choice? I have (& had
actually) the table you recommend but I am not able to have the visual
arrangement I prefer for data editing. Am I trying to have my cake and...
Thanks.

 

[Jeff Boyce]

I think you'll find, if you survey the tablesdbdesign newsgroup, that the
consensus is to first understand your data and get your structure
normalized.

After that, by all means, use the queries and forms to make it look the way
you want it to. You are NOT limited in how it looks by how you structure
your data.

Jeff Boyce
<Access MVP>