If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Searching for records that do not exist yet
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 |
#2
|
|||
|
|||
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 -- I hope that helped Good luck "Sarah Stockton" wrote: 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 |
#3
|
|||
|
|||
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. -- Chaim "Sarah Stockton" wrote in message ... 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 |
#4
|
|||
|
|||
On Tue, 20 Sep 2005 11:45:06 -0700, "Sarah Stockton"
wrote: 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. 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] |
#5
|
|||
|
|||
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 -- Sarah Kathleen Stockton "Chaim" wrote: 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. -- Chaim "Sarah Stockton" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
EARN $$$ SEARCHING THE NET IN GOOGLE(READ BODY) | Contacts | 0 | May 5th, 2005 04:29 PM | |
EARN $$$ SEARCHING THE NET IN GOOGLE(READ BODY) | Calendar | 0 | May 5th, 2005 04:29 PM | |
EARN $$$ SEARCHING THE NET IN GOOGLE(READ BODY) | Calendar | 0 | May 5th, 2005 04:29 PM | |
EARN $$$ SEARCHING THE NET IN GOOGLE(READ BODY) | Calendar | 0 | May 5th, 2005 04:29 PM | |
Updating master workbook from source that may/may not exist | [email protected] | Worksheet Functions | 20 | April 7th, 2005 03:37 PM |