How to find Unpaid Fees

[60myles]

I am developing a Condominium system - one of the modules tracks Quarterly Maintenance Fee payments. Link is to the ROSTER table by Unit #. How do I find Unpaid Maintenance Fees from the Maintenance Fee table, using a simple(?) query(ies)? I have tried Union Queries, Append Queries, Make table Queries, but obviously I have not stumbled accidentaly on the correct method yet!

 

[60myles]

It should be quite simple - one table has all the Units (ROSTER) and one table might not ([Maintenance Fees]) WHILE Quarter = [Which Quarter?] - I need to find the missing ones, if any! But...B-(

 

[Maquis]

sounds like you might need a "find unmatched records" query. This finds all records in a table without matching records in a related table.

The query wizard will do this for you automatically. B-)

 

[60myles]

Thanks for your reply - I'm using Access 2000. but I don't find any "find unmatched records" criteria ?? :-(

 

[Maquis]

Hmmm, I'm using Access 97, but I'm surprised that the wizard isn't in 2000 as well. You may not have all of your wizards installed. Regardless, here is the general syntax that you need:

SELECT DISTINCTROW Roster.*
FROM Roster LEFT JOIN MaintFees ON Roster.ID = MaintFees.ID
WHERE (((MaintFees.ID) Is Null));

This assumes that you want any record in the roster table which doesn't have a corresponding record in the maintfees table.

 

[60myles]

OK - I found it in the "Help" topics - let me give it a try and work on it further - thanks for your help.

 

[60myles]

Well, this is weird - I typed in the code exactly as you gave it (substituting my field names, of course), and tried all sorts of variations from there, my "Join Properties" are 2: Include All from ROSTER and only those from MaintFees...
and my SQL reads:

SELECT DISTINCTROW ROSTER.[Unit#], ROSTER.LastName FROM ROSTER LEFT JOIN
[Maintenance Fees] ON ROSTER.[Unit#]=[Maintenance Fees].UNIT
WHERE ((([Maintenance Fees].UNIT Is Null) AND (([Maintenance Fees].Quarter=[What Quarter?]));

I know I am missing one Maint Fee payment but this query won't find it - just brings up an empty record.

I suppose it could be done using VBA but I know somewhere between 0 and nothing about Visual Basic - I have wrillen SQL for FoxPro.

I'm retired and bought a condo in FL a couple of years ago - in a fit of insanity I ran for the Board of Directors so now I am the treasurer. Watching our Office Manager work on all her Excel spreadsheets I decided there was a better way, and in another fit of insanity(!) volunteered my very limited intelligence to improving it - I have it about 90% complete, but am stuck at this spot, needing expert help...

 

[Maquis]

You're trying to find Roster records that don't have maint fee records, right? But your query above says to show records where Maintfee.quarter = [input].

Basically you can't do a where clause on a record that doesn't exist in the first place. Is quarter in your roster table? If so run your where criteria against that table.

 

[60myles]

Well, my ROSTER table is a pretty much fixed list of all the Condos with their owners, adresses, phones, etc. the parts which change if a unit is sold, i. e.
the Maintenance Fees is YEAR, UNIT (linked to ROSTER.Unit#), Deposit#, Quarter, Check Date, Check#, Payment, [Late Fees], [Date Posted], Comments
I suppose it doesn't matter about the <WHERE Quarter=> part as the fees are all paid before the next quarter, however, leaving only the
WHERE (([Maintenance Fees].UNIT) Is Null;
still only leaves an empty field after the query runs, and it should show the unpaid Unit#('s), if it selects all the ROSTER units which don't match the selected Maintenance Fees units as it's supposed to.

 

[60myles]

I think I have it figured out - I have to make 2 queries. First is to make a temporary table [T2] for Maintenance Fees for the quarter, then make a &quot;find unmatched records&quot; query on [T2] and the ROSTER table. Otherwise the Maintenance Fees table has several &quot;hits&quot; on each Unit# and it will never find the missing one! Just keep me at this until 10:30 every night and I will figure it out!
Thanks much for your assistance and setting me on the right track B-)