@keithostertag That seems like a lot of pages for 2500 records (only 4 to a page - mine had 8 on half page). Sounds like a possible grouping/sorting problem. I’ll see about some SQL for you.
HSQLDB v1.8 was still bugging me. Found a solution involving Views. Will edit my answer with explanation & sample shortly.
Thank you! Because I’m a beginner at this, most of what you show is still over my head, but it gives me a direction to study and learn. I have to say I am surprised that my goal seems so complex to work out, since to me it seemed fairly straightforward. I guess that’s part of learning the limitations of the software. It may take me awhile, but I will post back when I make some progress. Thanks again! You have been very helpful!
I want to add that I changed the field values for the spool locations so that they now all start with “spool-”, for instance “spool-H20” and “spool-G32” instead of just “H20” and “G32”. This will make it easier to make queries based on criteria such as LIKE ‘spool-’. To do that I needed to use Tools>SQL, then the statement:
UPDATE “tbl_complete” SET “Location” = REPLACE(“Location”, ‘spool-’, ‘’) WHERE “Location” LIKE ‘%spool-%’
I’m looking at this URL on StackOverflow that seems to be related:
but it’s still a bit over my level of understanding…
@keithostertag I really think you need to understand the resulting query you have. You comment about changing “spool” to me is a waste of time & effort. You can already do what you wanted with very simple statements: SELECT * FROM COMBINEDVIEWS WHERE (SPOOL IS NOT NULL) AND (JCS IS NULL) gets all records with entries in SPOOL but not in JCS. That is why it sometimes takes time to develop some of these queries. Looking at what it is to be used for and making the result effective.
I guess I am also surprised you are still searching for answers when what has been presented is the complete answer - or is there something I didn’t comprehend. You could probably take your table data, drop it into the sample provided and have it all working within minutes provided the field types are comparable.
Thanks! This is all new to me, so I’m learning. Haven’t looked at the split DB yet, but I can now (finally!) use your Query5 to produce similar results with my real data- it gives a 2000 line list (because there are about 2000 different albums). Is there an easy way to modify Query5 to suppress those lines showing albums (GROUP-TITLE) with 'file" location only? for instance, in your above Query5 output I don’t want to see the “Albert Castiglio-Keepin On” or the “Albert Colllins-Cold Snap” lines.
If you read the answer, Query5 is a FAILED attempt to do in v1.8 what Query1 did in v2.3.3 of HSQL. The answer to Query5 is in the product presented in the sample. The sample will produce the same result in v1.8 as Query1 did in v2.3.3 but it needed to take a different approach to get to the same result. What needs to be using for your selection is the Views & the Query COMBINEDVIEWS in the sample to make it work. You can then use SQL as presented in comment above for selection.
You have a great deal to learn about Base. I’m going to attempt to make this as easy as possible to show you this works. I need to know the EXACT field names and field types (with lengths) of your data file.
Yes, as a beginner I am new to both terminology and concepts. I don’t see COMBINEDVIEWS anywhere in the CDListings.odb sample. How to check HSQL version? Using LO 5.2.4.2.1+ under Linux.
I understand Query5 isn't the final product... just taking things little at a time. I wont learn all this at once, and will refer to your suggestions for weeks.
ID Integer[INTEGER] 10
Group Text[VARCHAR] 80
Album Text[VARCHAR] 80
Location Text[VARCHAR] 30
Sorry, still trying to figure out how to format in this comment box!
Sorry, but I also need the EXACT name of your table. COMBINEDVIEWS as mentioned is a query. You will find it in the Queries section. The Views are in the Tables section.
A couple of comments ago you asked for a way to modify Query5. My point is why are you trying to use something which isn’t working correctly? BTW - I’m sure you are using v1.8 HSQL since you can only use a different one if using a split or external DB.
Don’t know how I missed seeing COMBINEDVIEWS in the queries, now got it. The name of my table is “tbl_complete”. Is it standard convention to make all the tablenames, fieldnames, and variables in queries all caps as you seem to do? I was looking at Query5 first because it seemed simpler and I thought the split DB solution was an advanced topic I would need to approach gradually after seeing what the “standard” Base configuration could do, not a good idea? Thanks again for your advice.
Going to add edit in answer. I use all caps (never spaces or spec char) because it makes it much easier to use in queries & especially macros. With caps you don’t need to surround names with double quotes & macros would need two sets surrounding names.
Thanks for all your work! The DUPLICATERECORDS, ERRORALBUMFIELD, and ERRORGROUPFIELD worked as is, but the views did not work with copy/paste- that gave the test data, not the real data! LO was turning them into simple tables, with no sql. So I instead created queries named fileView, jcsView, spoolView and copied the sql into them, then needed to edit the COMBINEDVIEWS appropriately. Now everything seems to work as intended. Lots to learn, and a great tutorial from you, I really appreciate it.
Thanks for all your work! The DUPLICATERECORDS, ERRORALBUMFIELD, and ERRORGROUPFIELD worked as is, but the views did not work with copy/paste- that gave the test data, not the real data! LO was turning them into simple tables, with no sql. So I instead created queries named fileView, jcsView, spoolView and copied the sql into them, then needed to edit the COMBINEDVIEWS appropriately. Now everything seems to work as intended. Lots to learn, and a great tutorial from you, I really appreciate it.
Surprised the copy didn’t work for you since it worked OK for me. Bottom line it is working for you. I consider this question answered. If you have more questions, please ask as new.
If this answers your question please click on the 
(upper left area of answer).
@keithostertag FYI - your assessment of the View copy was correct. I copied many things today but it turns out not Views. I do see this problem and I am certainly glad you were able to work it out. Keep learning!