On Sat, 23 Feb 2008 10:42:31 -0500 Drew Jensen <[EMAIL PROTECTED]> dijo:
> John Jason Jordan wrote: > > 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] <snippage> Drew, Thanks for the suggestions. The database actually has 43 tables in it, but most are just dead tables created a long time ago. MainBank contains all the records, so that is the only one I really need. There are also several tables used for creating reports, e.g., how many questions I have of each category, and so on. Those could be recreated because the reports themselves were lost when importing the database into Kexi. Kexi can import from Access, but only tables, not queries, forms, reports, etc. So essentially all I need is MainBank. Your suggestions for table design won't work because I have not been clear about the workflow. I said previously that there were three exams, but that was misleading. In fact there will be potentially thousands of exams, all based on the 7,000+ questions in the database. Each time I make a new exam I enter some specific string not used for any previous exam into the field "xxx" for each question that I want in the exam. The string is followed by a three digit number for sorting. The questions are then merged into a Writer document. In Writer I have an extensive macro to fix the formatting using styles for the different fields, change to typographer's quotes, add numbering, make the page two columns, and so on. So for each new exam all I have to do is manually enter the special select string into xxx for those questions I want in the exam. From then on it is mostly just mouse clicks and my exam is finished. The final step is to export as PDF with copy prohibition for delivery to the customer. In the past (using Access) I had a dummy table called "worktable." Instead of merging into Writer based on a query I merged into Writer everything that was in worktable. Thus, the queries were Insert queries into worktable, not Select queries. I also had a Delete query to empty worktable. I created the Insert queries on the fly because all I had to do was copy an existing query and edit the select string. The sort was still performed in the query, however - the records in worktable were in the proper order for the merge into Writer. I don't have any particular reason for using Kexi, so I think your suggestion to move the table(s) to Base is the next thing I should try. Just now I tried this but ran into a bit of confusion. I copied the MainBank table in the existing Base file to the clipboard, created a new database, and tried to paste it in. The paste function worked fine, but I had to cancel due to lack of understanding of field types. The descriptions in the drop-down box are not in human language and I can't find a lot of documentation. Perhaps someone can explain what field types I should specify. The existing table structure is: questioncode required, 12 uppercase chars + three digits, must be unique, keyed field stem must be unlimited amount of text (some stems are very long) romannumeral1-4 four fields for Roman numeral answers, must be unlimited text answera-d four fields for answers, must be unlimited text answer the letter answer (A-D), required, must be one uppercase letter of A-B-C or D explanation must be unlimited text (some explanations are half a page long) miscellaneous there are half a dozen miscellaneous short text fields that have been used in the past for selecting. I need to preserve these because they contain the record of where the questions been used previously. The unlimited text fields above are the ones I can't figure out. Apparently there is a "memo" field type, but it appears to want to save the text for each field in each record as a separate text file. If that is correct I'd end up with about 70,000 files on my hard disk. That is unworkable, but I can't figure out any other field type to use where the contents can be any kind of text in any quantity. I could specify a very large number of characters, but I wouldn't want the database to use that size unless necessary. Is there any place where I can find more detailed documentation of the field types and how they work? --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
