Searching for records that do not exist yet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 3 tables "Owner", Business" and "Annual Fees". They all have a one to
many relationship ie. one owner to many businesses and one business to
several annual fees.

Every year my user needs to query the businesses to find the ones that have
not paid their annual fees. How do I set up her query to search for records
that are not there.


Sarah Stockton
 
Use the query wizard to create an unmatch records between two tables, that
return which records apear in one table but not in the other
 
Sarah,

How are the fees and the businesses linked? I can assume that the tables
look something like:

Owners (OwnerID (PK), BusID, other)
Business(BusID (PK), ownerID, BusName, other)
Fees --- Here I get lost.

It seems that you need another table in here. If there is more than a single
kind of fee (implied by the name 'Fees'- at least to me), than Fees would
look something like: Fees (feeType, feeAmount?). But then someplace else is
needed to keep track of which businesses paid/pay which fees.

If there is only a single fee type, than it seems likely that Fees would
look like:

Fees (BusID, Amt, DatePaid, other)

In which case, you're simply looking for those businesses that have paid in
the past but not this year (unless of course every business must pay each
year). In that case, your query would be: (WARNING- THIS IS UNTESTED!)

select B.BusID, B.BusName
from Business as B INNER JOIN Fees as F
on B.BusID = F.BusID
where ( (select count(*) from Fees as F2
where F2.BusID = F.BusID
and Year (F.DatePaid) < Year(Date()) >= 1 )
and ( (select count(*) from Fees as F3
where F3.BusID = F.BusID
and Year(F.DatePaid = Year(Date ()) = 0 )

(It would be great if there was a syntax to embed comments in your Jet SQL
as you go). The first half of the where clause asks are there records from
previous years in which this business paid fees (Note: this doesn't
guarantee that they made payments last year, although that can be checked
with a minor change) and the second half asks whether they made payments in
the current year.

But, as I said, this assumes there is only one Fee type, so separate FeeType
and FeesPaid tables are not needed. It would also be simpler if I knew that
there was a reason to expect the Business to be paying the fees this year. I
don't see any information indicating how it is known that the business is
expected to make payments this year. If they must pay each year, than you
simply look for business with no DatePaid in this year.
 
Every year my user needs to query the businesses to find the ones that have
not paid their annual fees. How do I set up her query to search for records
that are not there.
Click to expand...

The Unmatched Query Wizard will do this for you. Create a new query,
using the wizard; that's one of the options.

Alternatively (and this may be better if you are looking for records
that are not there for a given year, in the presence of records for
other years) use a NOT IN clause: create a query on your Businesses
table with a criterion on the BusinessID of

NOT IN(SELECT BusinessID FROM Payments WHERE Payments.PaymentDate
BETWEEN DateSerial([Enter year:], 1, 1) AND DateSerial([Enter year:] +
1, 1, 0))

John W. Vinson[MVP]
 
I thought that the Unmatched Query wizard would do it to but here is the
catch. At some point (most likely after the first year) all business will
have at least one related record in the Fees table.

My relationships are as follows:

OwnerTable
OwnerID <<AutoNum>> <<Primary Key>>

BusinessTable
BusinessID <<AutoNum>> <<Primary Key>>
OwnerID <<Number>> <<Related Key to OwnerTable>>

FeesTable
AnnualFeeID <<AutoNumber>> <<Primary Key>>
BusinessID <<Number>> <<Related Key to Business Table>>

there are a few other reference tables involved one is Ref_LicenseType
The cost of the annual fee is based on the License type which is a look up
of the Ref_LicenseType.

Sarah Stockton
 
Back
Top