Prev: select and count
Next: Include certain names in query
From: John Spencer on 13 Apr 2010 08:57
The easiest way would be to build a query that shows everyone that HAS
returned the form and then use that in another query (a frustrated outer join).

First query (qFormsReturned):
SELECT CompanyID
FROM [FormsTable]
WHERE FormReturned = "2010/11"

Second Query
SELECT Companies.*
FROM Companies LEFT JOIN qFormsReturned
ON Companies.CompanyId = qFormReturned.CompanyID
WHERE qFormReturned.CompanyID IS NULL

Of course with those table and field names you can do it all in one query. As
long as the table and field names consist of ONLY Letters, Numbers, and the
underscore character Access has no problems with using a subquery in the FROM
clause.

SELECT Companies.*
FROM Companies LEFT JOIN
(SELECT CompanyID
FROM FormsTable
WHERE FormReturned = "2010/11") as qFormsReturned
ON Companies.CompanyId = qFormReturned.CompanyID
WHERE qFormReturned.CompanyID IS NULL

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Mr-Re Man wrote:
> I have a table that records when a form is returned (by year, 2008/09,
> 2009/10, 2010/11), every company in the table is sent a form.
>
> When the business returns the form, their record is updated with the year
> and the date when it was returned.
>
> I am trying to figure out how to enter criteria into a query so that it
> filters what companies have not yet sent in a form for 2010/11 without
> displaying previous years records (if applicable, e.g. they may have only
> joined us in 2010/11)
>
> Ideally, a "date_form_sent" would have been extremely helpful in this
> circumstance, but unfortunately I have not got that luxury. Is it possible
> therefore to search for records that have not returned their form for 2010/11
> without displaying previous years data?
>
> I hope this makes sense and eagerly await any reposnses :)
>
From: Stefan Hoffmann on 13 Apr 2010 09:00
hi,

On 13.04.2010 14:31, Mr-Re Man wrote:
> The Company table is unique, but the it acquries the application form details
> through a sub query and they are linked (Parent/Child) by the Contract No.
I see, the dups are correctly produced by your JOIN operation.

But it makes no sense to me. Why joining the same table which you're
using for determining the records which are not in it? It seems to be a
kind of contradiction to me.

I think you have to filter the outer qryDOC also:

SELECT
TC.ContractNo, TC.CompanyName,
TC.CompanyAddress1, TC.CompanyAddress2,
TC.CompanyAddress3, TC.CompanyAddress4,
TC.CompanyPostcode, TC.Service,
TC.DateContractEnded,
D.DocYearID, D.DoCYear,
D.DateReturned, D.DateReminder
FROM qryDOC D
RIGHT JOIN qryTradeContract TC ON D.ContractNo = TC.ContractNo
WHERE ((TC.DateContractEnded) Is Null)
AND D.DocYear = "2010/11"
AND NOT EXISTS
(
SELECT 1
FROM qryDOC I
WHERE TC.ContractNo = I.ContractNo
AND I.DoCYear = "2010/11"
);




mfG
--> stefan <--
From: Mr-Re Man on 13 Apr 2010 10:32
Steffan, thank you for getting the ball rolling on this one and taking the
time to help me, ver much appreciated.

John, many thanks for coming up with another solution which worked first time.

These newsgroups rock!

"John Spencer" wrote:

> The easiest way would be to build a query that shows everyone that HAS
> returned the form and then use that in another query (a frustrated outer join).
>
> First query (qFormsReturned):
> SELECT CompanyID
> FROM [FormsTable]
> WHERE FormReturned = "2010/11"
>
> Second Query
> SELECT Companies.*
> FROM Companies LEFT JOIN qFormsReturned
> ON Companies.CompanyId = qFormReturned.CompanyID
> WHERE qFormReturned.CompanyID IS NULL
>
> Of course with those table and field names you can do it all in one query. As
> long as the table and field names consist of ONLY Letters, Numbers, and the
> underscore character Access has no problems with using a subquery in the FROM
> clause.
>
> SELECT Companies.*
> FROM Companies LEFT JOIN
> (SELECT CompanyID
> FROM FormsTable
> WHERE FormReturned = "2010/11") as qFormsReturned
> ON Companies.CompanyId = qFormReturned.CompanyID
> WHERE qFormReturned.CompanyID IS NULL
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
> Mr-Re Man wrote:
> > I have a table that records when a form is returned (by year, 2008/09,
> > 2009/10, 2010/11), every company in the table is sent a form.
> >
> > When the business returns the form, their record is updated with the year
> > and the date when it was returned.
> >
> > I am trying to figure out how to enter criteria into a query so that it
> > filters what companies have not yet sent in a form for 2010/11 without
> > displaying previous years records (if applicable, e.g. they may have only
> > joined us in 2010/11)
> >
> > Ideally, a "date_form_sent" would have been extremely helpful in this
> > circumstance, but unfortunately I have not got that luxury. Is it possible
> > therefore to search for records that have not returned their form for 2010/11
> > without displaying previous years data?
> >
> > I hope this makes sense and eagerly await any reposnses :)
> >
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: select and count
Next: Include certain names in query