Hi all!
I thought it would save me some trouble if I made a simple database of the names of those who do the Bible readings at our church; I could then make a list of those who hadn’t read in the current year and would know who to ask. I’ve just got two tables, Readers and Dates, with a form to enter dates against each reader (dates are formatted VARCHAR rather than any of the date formats so that I can search on part of the date). I’ve made a query for those who have read in a given year with no trouble, but eliminating records is eluding me. NOT LIKE 2018 removes those who have only read during 2018 but leaves in those who read during 2017 (I’ve only entered records for these two years). I’m a SQL newbie, so is there any way round this? As it’s only a small database and I’ve only just made it I’m perfectly prepared to start again!
Hello,
I would advise you to save dates as date format. Sql can extract portions of dates for comparison or manipulation. One thing to keep in mind with dates is the method of storage. For example, regardless of the display method of the date, it is stored internally as YYYY-MM-DD. This can be relative when comparing a date:
Select * from MYTABLE where MYDATE < '2018-01-01'
You can also use portions of the date for comparison:
Select * from "Table" where MONTH("MYDATE") < '09'
or:
Select * from "Table" where YEAR("Date2") < '2018'
Of course the specifics of the SQL depend upon the database you are using. If using the default embedded HSQLDB v1.8, the documentation can be found here. It is always recommended to upgrade to a split database (here for new empty or here for converting existing).
Edit:
It is a bit difficult to discern the actual problem. It may be the way the SQL is constructed and the content of the tables. If the Date table reflects each occasion where a Reader has read, what you need to do is create a record based upon the newest date in the Date table. Then using that, eliminate records which are in 2018. This can be done selecting the MAX date in date table then appending it to the appropriate Reader record using a Left Join. Then select all records with a year less than 2018. To help with this, attached is a sample using Date formatted fields.
Sample - ReaderDates.odb
In the sample is a form (for entering records), two tables ( Readers & Dates) and the actual query.
Thanks Ratslinger, and apologies for the delayed answer: I’ve been trying out your sample and it seems to do the business pretty well. Thanks too for the background stuff. I came across the recommendation to use a split database elsewhere on this forum. I gave it a try with another db and it seemed to work fine, right up to the point where I discovered that the ID fields had stopped autofilling and editing tables was greyed-out. I gave up at that point…
@Realname I hope you reconsider the split files. The embedded can corrupt and cause data loss. With split you can still modify existing fields using SQL and add/delete fields through the normal graphic interface. For the Auto incrementing, this is a matter of how you convert. For my method, see document in my answer on this post. Another plus, the database is upgraded & has more capabilities.
After posting the above I did indeed reconsider split databases, revisited your link, reconverted following the method more meticulously than I did before, and am now successfully running a split database! Thanks again for your help.
Glad all is OK. Feel free to post new questions if you have problems.