Filter Sort Annoyance

[Christine Lisi]

I have a database w/ one table and many queries. The problem I'm
experiencing is that whenever I change the design of the table in any way, I
lose my filter/sort. Is there a way to set a permanent filter/sort to all
tables/queries so that I don't have to redo it every time I make a design
change?

Thanks.

 

[Jeff Boyce]

Christine

I'm not sure I understand. In a well-normalized data design, the
table/tables would only rarely be changing. New data and changes to
existing records, sure, but table design changes?

Could you describe how you're finding it necessary to change your table
design? There may an alternate design to could reduce/eliminate the
changes.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Christine Lisi]

I add and remove fields and rename them all the time. I guess I'm not normal?

 

[Van T. Dinh]

I think you are normal (BG) but the Table is probably not normalised.

Most databases I look after have a lot more than one Table. The database I
am designing at present has more than 100 Tables.

See Jeff Conrad's references on Database Design 101 section:

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.htm

 

[Jeff Boyce]

Christine

Why be normal?!

But I'm still curious -- you explained THAT you do this... but I still don't
know WHY? What business need are you trying to solve by doing this? Again,
I ask because there may be other ways to accomplish what you need done,
without doing it the way you've discovered so far...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Christine Lisi]

I read the info that Van sent about normalizing a database. As far as I can
see, I'm normal (smile)! My databases (2) have no "like" fields. These are
personal databases I use for my collection of casino chips and gift cards.
The main fields are "Casino Name" or "Store Name," "Description," and "Qty"
"Location," and I have a record number field as my primary key field. The
rest of the fields are mostly Yes/No fields (check boxes) or drop down
fields. I trade with other people and use queries for things like printing
lists of the chips/cards I have for trade (where QYT >1), queries for all my
Vegas chips, queries for cards from certain stores, etc. Sometimes I will
eliminate the QTY field from a query because I don't want to display this
information when printing lists. Then my filter/sort criteria gets all
messed up. If there's a better way, please share, but I don't know any other
way to do this.

My question was fairly simple - so I thought. I don't understand why, when
you eliminate a field from a query or rename a field (for whatever reason)
why you lose your filter/sort criteria. I want to be able to "normalize" my
filter/sort criteria for my entire database.

If you can help, I appreciate it.

Thanks.
Christine

 

[Jeff Boyce]

Christine

My original concern (and still active concern) was your assertion that you
are changing your table definitions. Tables store your data. Are you
saying that the data structure (not the values) change frequently? From
your description, you have the same structure, with different values from
time to time. It may be that you table structure would benefit from further
normalization, even if you already consider it normalized.

Queries retrieve sets of data. If your selection criteria change, you can
use the same query and modify it by changing the selection criterion. If
this happens often, you can use a parameter query to prompt for the
selection criterion. If this happens very frequently, you can create a form
on which you select values and revise your query to look to the form's
values for criteria.

If you wish to select by a field, but not display it, create a report based
on the query, and simply leave out the (don't show this) field from the
report definition.

If your data structure has a number of "mostly Yes/No" fields in a table,
I'll suggest that further normalization may be needed.

If you provide the newsgroup more specifics about your current data
structure, readers may be able to offer more specific alternate approaches.

Good luck!

Jeff Boyce
Microsoft Office/Access MVP