Querying Large table with Yes/No fields

[jlancton]

Hello,

I'm working on a Contacts db I inherited. The main Contacts table has every bit of info for the person, including about 40 Yes/No checkboxes for categories.

My question is, how can I easily search for any given combination of the checkboxes? I've tried putting checks in the boxes I want and then doing Filter by Selection, but it returns inconsistent results, plus it always makes the filtered changes to whatever record I was working in originally.

Is there some way to display the fields in another form and use whatever is selected there as the basis for a query? That seems backwards, as forms usually display the results of the query, but I can't think of another way to do it.

Any help will be greatly appreciated.

Thank you,

-Jeff

 

[Mike555]

Check out: thread702-563930
The database posted by jfgambit may point you in the right direction.

--
Mike

In defeat: unbeatable. In victory: unbearable. -- Winston Churchill

 

[jlancton]

Thanks Mike. I looked through that thread, and it looks like what I need, just that it is looking at multiple text boxes.

The fields I have to search on are all Yes/No's displayed as check boxes. So something like that but displaying all the possible checkboxes, and re-running the query based on those that are checked.

I'm looking at modifying it but the coding is a bit beyond me.

 

[Mike555]

The code is beyond me as well. Best case scenerio would be to ask jfgambit. Go to the FAQ he wrorte about it and send him the question.

faq702-3702

--
Mike

In defeat: unbeatable. In victory: unbearable. -- Winston Churchill

 

[willir]

Jeff

I hate to say it Jeff, but it sounds like you inherited a mess. One table for everything. Everytime somebody wanted to add something, they added a new yes/no box.

Am I right?

I converted a very similar contact database. The non-profit organization used the check lists to state what contacts the person belong to, and then the check box determined if they received a mailer. "Breakfast club", "Mall walk", "Volunteer", "Brd Member", "Saff", etc. I guess it seemed to make sense at the time to the designer but because of the weakness in the design it became a mess.

But I always say "Bad data begats bad data".

It has to be done sooner or later...

Noramlize the databse. In my case, I created a new table for "groups" plus a many-to-many join table since a person could belong to more than one group, and more than one member could belong to a group.

Cure the cold before you get pneumonia.

Richard

 

[MichaelRed]

willir is on the right path, though without specifics. I, also, can''t give specifics, however some additional detail re "normalizatio":

Create a new seperate table for the "checkbox" attributes. It will include only two fields, the [ContactId] from the existing table and an 'Attribute' field. In a smaller set, you could use a simple (long) number as a binary tree for the individual attributes (1 = first attribute; 2 = second attribute; 4 = third attribute; 8 = fouthh atribute; ...). As soon as you pass the 32nd attribute, this approach becomes a lot more complex, so a simple text field is, perhaps, more appropiate. and it just is the name of the attribute (hopefully constructed according to a reasonable naming convention).

To query on a 'set' of attributes, you just join the two tables and query for the named attribues in the second field, returning the [ContactId] and whic other fields you use in the original table. Of course, it will take some effort to populate the new table with the existing attributes and a bit of work to generate the procedures to maintain the relational aspects, but some review of relational database concepts seem to be in your future and most of this should be quite clear by the time you are actually ready ot implement the change.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[jlancton]

Willir: Yes, you got it. I started reading up on normalization, and I'm fine with creating separate tables and the many-many joins. I'm struggling with how to get the data into the tables though, guess I'll plug away some more and pop in here if/when I get stuck.

Thanks to everyone who responded!

-Jeff

 

[jlancton]

Willir, if you're out there, I've got a couple of questions.

There are five groups of categories, some large, some small. They are Geograpy, Work Groups, Tourism, Community, and Media. They are all M:M, as any one could belong to many of the others.

First, would the join table have the primary key's from all the other tables, or do I have to create a three table set for each?

Second, and more importantly, how do I get the info from all the tables on a form for data entry?

I'm sure there'll be sub forms, but if each item in one of category tables is a text field, how can it be displayed as a checkbox?

I'm really lost here.

Whatever help you can provide will be especially appreciated.

Thanks,

-Jeff

 

[willir]

jlancton

To answer this, I need / you need to know what you want for your outcomes. I realize that you want contact information.

Are these categories "inormational" fields? For example:
Other than knowing the geographic location of a person, do you need to know more about the location? Likewise for work groups, community, tourism and media.

If for example, you just need to know how or which contacts are in a geographical location, then a geographical field on the contact table would work. Adding a geographical location table would achieve the same result but you would have more flexibility. For example, if you wanted to document the geographical location of the Community and Media. Now using a geographical location as field on the contact table does not make sense - using a geographical location table does.

Go through the same thought process for the other parameters.

Once this is done, the next step is to determine the relations.

Here is another "walk through" using another example.
thread700-628486 realize this seems a little general at this point, but as we get more specifics, it will become clearer.

Richard

 

[jlancton]

The desired outcomes go something like this:

Items under media include daily, weekly, monthly, and type of media, such as boating, travel, etc. Thus a query might be all daily boating in a geographical area. Similarly, community groups include churches, senior centers, etc, so a query for all churches in a geographic area would be common. There may also be queries using several of the tables, such as all Westchester daily travel in a particular workgroup.

The client is a PR firm if that provides any insight.

So it looks like a location table is the way to go. It also occurs to me that the daily weekly monthly might go in a table for frequency using this logic.

It still seems that all relationships will be M:M since any one of the items in a given table could be part of several others.

I read through the thread you posted, and it contains a wealth of information which I think will apply to my situation. I can't begin to thank you for your help with this.

-Jeff