Welcome to the Tweaking4All community forums!
When participating, please keep the Forum Rules in mind!
Topics for particular software or systems: Start your topic link with the name of the application or system.
For example “MacOS X – Your question“, or “MS Word – Your Tip or Trick“.
Please note that switching to another language when reading a post will not bring you to the same post, in Dutch, as there is no translation for that post!
Hi all
I'm trying to create a query that lists a group of records only when the last record of the group doesn't have a specific field value (trans<>'delete')
(I showed the list here grouped by access, ordered by access, date)
key access trans date (mm/dd/yyyy) 1 adt add 01/01/2020 2 adt change 01/02/2020 3 adt delete 01/03/2020 4 btt add 01/01/2020 5 btt change 01/02/2020 6 cdt add 01/01/2020 7 cdt change 01/02/2020 8 cdt delete 01/03/2020 9 ddt add 01/01/2020 10 ddt change 01/02/2020 11 ddt change 01/03/2020
Select * from table1 exist where (Select access, date from table1 group by access, date having trans <> 'delete')
I'm looking for this result: group without a last delete row
4 btt add 01/01/2020 5 btt change 01/02/2020 9 ddt add 01/01/2020 10 ddt change 01/02/2020 11 ddt change 01/03/2020
In my other sql attempts, I'm only able to get the Delete row. SQL for Paradox tables seems to make this a difficult task. Any help is appreciated.
Thank you
Hello JCBond,
I haven't used Paradox in a very very long time, so I may not be able to help with a Paradox specific query.
However, in most SQL variants this should work, so I did a quick test in SQLite (database attached below).
SELECT * FROM table1 WHERE access NOT IN (SELECT access FROM table1 WHERE trans='delete');
Which gave me this:
This is the zipped test database:
Thanks for the quick response Hans.
Yes, Paradox is -old- and I have requested this database go to SQL Server long ago, but politics is slow...
That query in SQLite does work, but, unfortunately, Paradox sql can't do that
You're welcome!
And here I thought Paradox supports sub-queries and "not in".
What part is not working? The "not in" or the sub-query?
The issue is the sub-query (at least).
Paradox sql does sub-queries but doesn't treat the sub-query as a group the way SQLite does in that construction.
The sub-query results are just individual records, so removing a "Delete" transaction is just 1 record
Interesting challenge for sure ... 🧐
Unfortunately, testing Paradox would take quite a bit of work for me to even get started.
So I'm not sure how I can assist,... 😞
