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

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.

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

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
-----------------------------------------

Hi Henry,

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

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.

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.

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