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

B

beantype

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
 
J

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.
 
J

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...
 
B

beantype

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

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>
 
B

beantype

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.
 
J

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>
 
Top