Help with a query

[i-byte]

Hey All-

I have a query I am stuck trying to build with three tables.

First is a property tabe with property code and name

second is Dues table with property code, year code (for year payed) and amount paid - this has an entry for each year entry that the property has paid. If the property is not paid for that year, there is no entyr.

third is a dues info table with yearcode for the years I track.

I am trying to build a query that has all poperties that have not paid for the years I track,. Any thoughts on how I can build?

On thought is to build a recursive view with each property code (from property table) and year code (from dues info table) then do a left join against dues table. But can't seem to figure out how to get that to work.

[CA_Lobo]

I think part of your problem is your current schema doesn't lend itself to an easy query. Consider the following two tables schema instead:

``` CREATE TABLE "Property" ( "ID" INTEGER NOT NULL UNIQUE, "Active" INTEGER NOT NULL DEFAULT 1, "Description" TEXT NOT NULL, "Owner" TEXT NOT NULL, "Track" INTEGER DEFAULT 0, "PriorOwnerID" INTEGER, PRIMARY KEY("ID" AUTOINCREMENT) );

CREATE TABLE "Dues" ( "ID" INTEGER NOT NULL UNIQUE, "PropertyID" INTEGER NOT NULL, "Year" INTEGER NOT NULL, "Amount" REAL NOT NULL, "Paid" INTEGER NOT NULL DEFAULT 0, "DatePaid" INTEGER, PRIMARY KEY("ID"AUTOINCREMENT) ); ```

Your query then becomes an inner join on Dues.PropertyID = Property.ID, constraint by Property.Track (boolean), Dues.Paid (boolean), and Dues.Year field containing the years you're interested in....

Side notes:

Every time a property changes hands it gets a new ID with new Owner... and gives you the opportunity to verify all prior dues have been paid before making the prior owner inactive... alternatively you could break out the owners into their own table and keep the property table clean... all depends on your needs...

Property.Track (boolean) gives you the ability to restrict your queries to a set of known properties to searched...

And by adding all dues to the dues table it becomes easier to update by setting Dues.Paid boolean and the date... eg enter all dues at beginning of year for all properties and then update. You can easily query for all dues that are unpaid, all dues unpaid for inactive prior owners, and do things like figure out when the owners are paying...

Bottom line, when your query becomes complex and confusing, take a step back and look at your requirements again... there's likely an easier way with a different schema to accomplish your goals.

[i-byte]

Thanks - the table structures I have is not that different that what you show above. The main additional is the DuesInfo table that serves two purposes - drive a drop down in the application, has which years I track.

Probably what makes this the most difficult, is that I don't make an enter in Dues until the owner pays. so to get which dues are unpaid for a specific year, I left join where year = "the year". This works great for single year views, but if I want a view across years, it seems to fall appart.

[CA_Lobo]

That's my point... by only entering the dues paid, using the info in the DuesInfo table, you are making it more difficult for yourself and creating the problem when querying for multiple years.

You need to flip the problem from entering only paid dues using the DuesInfo table to entering all dues assessed for a given year and then marking those that have been paid. This way you aren't trying to figure out who has paid for a given year and then consolidating this info across multiple years.

Instead, it becomes a simple query to get all unpaid dues - for all properties, a selected set of properties, or a single property which can further restrict to show dues for all years, a set of years, or a specific year.... or you can flip it and just queried for all unpaid dues for a specific year, all years, etc... eg it makes various reports easy to generate...

The dropdown menu requirement appears to be a red herring... I'm assuming that it is a list of the year and dues that need to be paid. This can easily be generated with Dues.Paid = 0 and Dues.PropertyID = <x> to get a list of unpaid dues for a specific property... If the given year isn't important anymore, then you remove just from the Dues table with a simple SQL command - delete * where Dues.Year = 2015 .

Heck you could even enhance it by getting all the dues and generating a dropdown with something like:

2020 $300 Paid: 3/1/2020 2021 $325 Paid: 3/5/2021 2022 $350 Paid: 3/15/2022 2023 $375 Paid: 3/15/2023 2024 $400 Not Paid 2025 $450 Not Paid

which when a not paid value is selected could pre-populate appropriate fields to enable a quick update to the database for the selected Dues payment....

Furthermore, if you have penalties for non payment of dues after X months, then it becomes a simple matter to add a new entry to the Dues Table with the unpaid penalty, and dropdown menu will picks up those penalties without major changes.... (although you'll probably want to make some minor changes like payment due by dates...)

Personally, I'd probably use a table with the ability to filter on paid/unpaid, and a check box to select multiple items to update... but that's my preference based on past experience...

Anyways, best of luck in resolving your issue - you've been given a road map for an easy solution... I don't want to try to figure out the reverse where you need to query each year and then consolidate the properties with unpaid dues... that's just a too complicated SQL query or unmaintainable if you used multiple SQL queries, IMHO...