John Jason Jordan wrote:
On Fri, 22 Feb 2008 20:02:19 -0500
Drew Jensen <[EMAIL PROTECTED]> dijo:
In my database I have a number of test questions which I have coded for
more than one exam. Using * before and after gets them all merged, but
for one exam I cannot get the sort to work. The records are being
selected at random. This query was copied and pasted from another that
does work, and after copying and pasting was edited to change the
select criteria from LIKE '*LFb???*' to LIKE '*LFc???*'. In other
words, the only change was from "b" to "c" in the search criteria.
Nothing else was changed, including the sort specs. Yet the original
"b" query sorts correctly and the "c" query gives random sort order.
I don't know why the first one works, but I think in order to get the
second one to work I need to figure out how to sort on the results of
the sort criteria. This is not easy to figure out because none of the
fields has any way to sort the records correctly. What I am getting at
is the order of the field on which the select is made. Remember, the
select criteria starts with *, so some records have stuff in front of
the select string and others do not. For example, here is what is in
the field "xxx" on which the select is performed for a few
representative records a, b and c:
a LFc-016
b LQ1a-015, LFc-014
c LQ1b-018, LFc-017
It should be sorting it in order b, a, c (14-16-17). Instead it is
sorting it a, b, c. That is because the sort is being performed on the
entire contents of the field, not on the portion of the field on which
the select is performed.
The select query design shows:
Field mainbank.* xxx
Alias
Table mainbank mainbank
Sort ascending
Visible x
Function
Criterion LIKE '*LFc-???*'
I've poked at this for a couple hours without success.
There are three exams - LFa, LFb and LFc. First, I created LFa and it
worked (after figuring out how to use wildcards - thanks Barbara). Then
I copied and pasted this twice, once for LFb and again for LFc. I
edited LFb and changed the "a" to "b", and then I edited LFc and
changed "a" to "c". LFa continues to work, LFb works fine also, but LFc
sorts in random order.
Any suggestions welcome.
You have your self a problem - not least because of the table structure. A flat
table for a relational problem. Also, you are limited to the string
manipulation functions available from the database engine, in this case SQLite,
and those are pretty limited. ( Specifically there is no LOCATE function which
is really what would have worked here ) ( And I was surpriesed that you are
right adding a column to the table, kexi via ODBC wants to empty the table,
same under Kexi it turned out and when I actually tried it directly in *Kexi*
the application crashed on me twice...hmmm.. maybe it doesn't like 64bit Linux )
If you are delivering an OpenOffice.org based application to your customer on
Monday then I suggest you move your table ( it appears you have created just
one flat table ) to an actual Base database. The string functions are robust
enough that you can do what you are trying to do with just a query - in the
long run though this flat file scheme is really going to become burdensome.
If you really want to leave the data in Kexi then I would recommend that you
restructure the table into 3 tables. ( I would recommend this in a Base
database also, but under Kexi you might darn well have to )
So another way to structure your tables would be. [note this is a very
simplified view of the data]
tblExam ( ExID, Description )
tbQuestion( QtID, Question )
tblExamQuestions( ExID, QtID , QtNum )
tblExam has these records:
LFa Describe this
LFb Describe this
LFc Describe this
tblQuestion is for all intent and purposes a copy of your current MainBank
table, minus field xxx. I would use an auto_increment field for the QtID field
- this has no use but for the database relation.
tblExamQuestions is where you associate questions with exams and the QtNum
field would allow you arrange the questions on a particular exam anyway you
like.
Your table MainBank then becomes the name of the query, and you just use a
named parameter to pull the questions for a particular exam at run time.
SELECT
"tblQuestion".*
FROM
"tblQuestion", "tblExamQuestions"
WHERE
"tblQuestion"."QtID" = "tblExamQuestions"."QtID"
AND
"tblExamQuestions"."ExID" = :Exam
ORDER BY
"tblExamQuestions"."QtNum" ASC
When you execute the query a dialog box will prompt you for the string EXAM.
Enter LFc and you will get only the questions for that exam, sorted in the
proper order.
I know you are running short on time - but it is my best advice. ( if you want
to go that route let me know ASAP and maybe I can offer a bit more in the way
of a concrete example )
Drew
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
