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.