[AccessD] Two stupid questions from newbie

Drew Wutka DWUTKA at Marlow.com
Mon Mar 17 22:48:21 CDT 2008


Welcome to AccessD.

Can't really answer your second question.  Sorry, don't use access as
the interface enough to have run across that quirk.

However, as to your first question, here's one way to optimize that
process.

The issue field should be setup as a long integer field.  Have another
table with the issues in it.  The KEY for each issue should be a bit
value, ie, 1,2,4,8,16,32,64,128,256,512,1024, etc.  When entering data,
provide a listbox that displays all of the issues, and when saving the
data, enter the sum of those values into that field.  Then, to query it,
do the reverse, provide a listbox with the issues (multiselect, like the
data entry should be), and search with the BAND (which is the Access SQL
version of AND) to find the fields.

That is the way I would do it.  Saves space, runs fast, etc.

However, not everyone is comfortable with bit comparison data
storage/querying.  The other way to go would be to create a many to many
table.

Ie:

tblMain
MainID
Bunch of other field

tblIssues
IssueID
IssueName

tblMainIssues
MainID
IssueID

For each issue you have associated with a record in tblMain, you'd have
a record in tblMainIssues.  To search for issues, you merely query
tblMainIssues, with an inner join to tblMain, off of the IssueID, and
the related records from tblMain will be pulled up by their ID.

I hope this makes sense.  

Drew 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Barbara Mende
Sent: Monday, March 17, 2008 6:35 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Two stupid questions from newbie

I'm a newbie not only to this list, but to Access.  I've worked with
FileMaker and Summation, but not with SQL.


This is a small Access 2002 database with basically one data entry form,
mostly text, no calculations, no relationships.  Not even lookup tables.
It's basically for searching for and reporting on info.


 I would like to do two things which I don't seem able to and which I'm
sure are painfully obvious.


1.  I have a text field which describes issues.  Let's call them wealth,
fame, power.  Some of the data relates to more than one of these, e.g.,
wealth and fame.  (Actually there are about twenty of them.)


What I'd like to do is put "wealth fame" or "wealth, fame" into the
issues field, so I can search for either weaIth or fame and come up with
the appropriate record. don't seem to be able to search on part of that
field, which is a text field.  So what I've been doing is creating one
record under "wealth," then another which is identical except that the
issues field reads "fame."  This seems to be a waste of bytes, and also
allows the likelihood of a search on another field bringing up duplicate
records.  I tried "like" but that didn't do it.  If I put both wealth
and fame into the issues field, I can't pull up either, either by
filtering or by querying.


2.  If you thought that was dumb, this one is dumber but I'm tearing my
hair out.  I would like to create forms and reports based on a saved
filter result.  The instructions say it's easy:  Run the filter, select
"autoform" or "autoreport" from the "new object" icon, and there you
are.  Trouble is that "autoform" and "autoreport" are grayed out.  If I
put them up as individual icons they're grayed out too.  Any ideas of
what settings I've screwed up or how to ungray them?


Thanks, everyone!
Barbara


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI Business Sensitive material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.





More information about the AccessD mailing list