Drill Down Filter on textual databases 
Author Message
 Drill Down Filter on textual databases

Hi All,

Coming across a rash of quasi govt orgs who take out surveys and want to
store textual surveys (some Yes/No, some long winded) in their databases.
They want a facility to filter records in a drill down fashion. Have a
criteria, get a resultset and then apply further criteria to the resultset.
I was thinking of a heavily normalised table structure that has only two
fields - attribute name and attribute value. No matter how many attributes
they want to store, this table self expands without incurring more fields.

Any tips, web references that people have come across?

Thanks

Ananda



Sun, 07 Jul 2002 03:00:00 GMT  
 Drill Down Filter on textual databases
Easy to do with forms.  Your "textual" requirement is not clear to me.

Here is the way I did.  On a continuous form, you make the first line of
each record colored.  And then you can use the double-click property, you
open another form with the proper filter.

Quote:

>Hi All,

>Coming across a rash of quasi govt orgs who take out surveys and want to
>store textual surveys (some Yes/No, some long winded) in their databases.
>They want a facility to filter records in a drill down fashion. Have a
>criteria, get a resultset and then apply further criteria to the resultset.
>I was thinking of a heavily normalised table structure that has only two
>fields - attribute name and attribute value. No matter how many attributes
>they want to store, this table self expands without incurring more fields.

>Any tips, web references that people have come across?

>Thanks

>Ananda



Sun, 07 Jul 2002 03:00:00 GMT  
 Drill Down Filter on textual databases


Quote:
> Easy to do with forms.  Your "textual" requirement is not clear to me.

> Here is the way I did.  On a continuous form, you make the first line
of
> each record colored.  And then you can use the double-click property,
you
> open another form with the proper filter.

Don't get that. Maybe you could elaborate. I'll detail more of my
question.

Assume you have a table of say 100-200 fields. Some fields are as long
as 255 characters and there is little categorisation in those fields
i.e. you can't simply say that this content is either "Germany"
or "France" etc.. - the content is unexpected.

Client(s) want to nominate a field, specify a condition or conditions
for that field and then keep get a result - batch of records. Then they
want to apply further criteria to other fields in that batch of
records, using possibly the same form.

Alternatively, use one form, fill it with criteria and hit go. However,
how do you anticipate which field and which criteria. The QBF example
by Getz for example, would an SQL string that is several miles long
(kidding)

Regards

Ananda

Sent via Deja.com http://www.deja.com/
Before you buy.



Sun, 07 Jul 2002 03:00:00 GMT  
 Drill Down Filter on textual databases
Store all data in a text document and apply simple search?
You cannot really maintain a database where the fields are not strictly
defined, and it may simply be a waste of time and probably space to store so
many unstructured data in so many fields.

--
Regards,
Bas Cost Budde
currently working not at nospam but at wegener



Sun, 07 Jul 2002 03:00:00 GMT  
 Drill Down Filter on textual databases
Ananda,
The only way I can see you doing this is totally dynamically
with unbound Fields and code.

e.g.. Mainform with Listbox ( of Fieldnames ) selected fields are
added to a Temp table displayed in a Subform with additional field
for the criteria the user wants to apply.

LB Fieldnames    |     SubForm SelectedField   :   FieldCriteria

Dynamically create a Create Maketable Query from the Selection,
Then Run a Select * Query and display in an unbound (Grid Control Like )
Subform.(... or even a Listbox ? )

Field 1 : Field 2 : Field 3 : Field 4 : etc....

Re-populate the LB with the fieldnames of the 1st Temp Result Table
let them select and go round again, and again, etc.
( DB bloat may be a problem creating & destroying the temp tables
so many times, but when you're desperate ..... )

Simple loop to get the fieldnames, Lots of code to interrogate the criteria
( for >, <, Like, * Strings etc..) and build the MakeTable SQL Statement.
...but Hey,... You'll only have to do it once. :-)    than fairly simple to
Select *
and populate the result subform/listbox.

Performance wise you'd probably want to add some indexes to the table
based on the LB Selection, prior to running the applying the Criteria with
the MkTable query.

Hope its not too rough an outline and too far from what you're looking for.

--
Henry Craven
-----------------------------------------


Quote:


> > Easy to do with forms.  Your "textual" requirement is not clear to me.

> > Here is the way I did.  On a continuous form, you make the first line
> of
> > each record colored.  And then you can use the double-click property,
> you
> > open another form with the proper filter.

> Don't get that. Maybe you could elaborate. I'll detail more of my
> question.

> Assume you have a table of say 100-200 fields. Some fields are as long
> as 255 characters and there is little categorisation in those fields
> i.e. you can't simply say that this content is either "Germany"
> or "France" etc.. - the content is unexpected.

> Client(s) want to nominate a field, specify a condition or conditions
> for that field and then keep get a result - batch of records. Then they
> want to apply further criteria to other fields in that batch of
> records, using possibly the same form.

> Alternatively, use one form, fill it with criteria and hit go. However,
> how do you anticipate which field and which criteria. The QBF example
> by Getz for example, would an SQL string that is several miles long
> (kidding)

> Regards

> Ananda

> Sent via Deja.com http://www.deja.com/
> Before you buy.



Sun, 07 Jul 2002 03:00:00 GMT  
 Drill Down Filter on textual databases


Quote:
> Ananda,

Hi Henry,

Imagine going international when you're in the same town <grin>

Quote:
> The only way I can see you doing this is totally dynamically
> with unbound Fields and code.

That's what I was looking for, some alternative ideas to the solution.
I'll give the approach a try.

Thanks
Ananda

Sent via Deja.com http://www.deja.com/
Before you buy.



Mon, 08 Jul 2002 03:00:00 GMT  
 Drill Down Filter on textual databases


Quote:
> Store all data in a text document and apply simple search?
> You cannot really maintain a database where the fields are not
strictly
> defined, and it may simply be a waste of time and probably space to
store so
> many unstructured data in so many fields.

Hmm. The client could easily store stuff in a Word document themselves
but management of this kinda data using Word would be a mess.
Alternatively, use some text based search software but again, that
searches a document or documents for "near" "and" conditions like you
do in an MS KB query. I don't have much experience in that kinda thing.

Leads me to think that SOME fields should be categorised but there
should be a search/filter ability for some of the other fields.

Thanks

Ananda

Sent via Deja.com http://www.deja.com/
Before you buy.



Mon, 08 Jul 2002 03:00:00 GMT  
 Drill Down Filter on textual databases
You could then store all answers in a separate record. I think of a table
like answers (questionnaireNumber, answerNumber, answerText). That can be
easily sought. (searched? Which one is it again?)

--
Regards,
Bas Cost Budde
currently working not at nospam but at wegener



Tue, 09 Jul 2002 03:00:00 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. Drill down drives, folders, files help

2. Help: Drill-down info for driving MS apps from Access

3. Drill down to disk IO level

4. Grid drill-down features

5. Help (drilling down deep in XML)

6. datagrid drill down ?

7. Drill Down support from HTML code to C# and VB object code in Beta 2

8. HotMap or other maps w/ drill down

9. Combo box with "drill down"

10. WANTED: Drill down reporting component

11. FYI: Mouse-click on inactive button may drill down

12. MSHFlex Grid and drill down

 

 
Powered by phpBB® Forum Software