Can't get my head around this query..help!

[Mr-Re Man]

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

 

[Stefan Hoffmann]

hi,

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?

This can be easily done in SQL:

SELECT *
FROM Company C
WHERE NOT EXISTS
(
SELECT 1
FROM FormReturn F
WHERE F.idCompany = C.ID
AND Year(F.ReturnDate) = 2010 AND Month(F.ReturnDate) = 11
)

The first condition in the inner WHERE clause (WHERE F.idCompany = C.ID)
must include all primary key fields to match the records correctly.


mfG
--> stefan <--

 

[Mr-Re Man]

we're half wat there Stefan, many thanks, the final bit if possible is as
follows.

The query displays 500+ records, many of them duplicates, as they have
returned a form in 1 or 2 years previous, how can I tweak the code to display
just one record by each company?

As the records hold addresses so that I can merge into a reminder letter and
it would be a waste of time/resources etc to generate multiple letters for
the same company.

many thanks

 

[Stefan Hoffmann]

hi,

The query displays 500+ records, many of them duplicates, as they have
returned a form in 1 or 2 years previous, how can I tweak the code to display
just one record by each company?

This sounds like a table structure problem.

The table Company in may example defines the companies. In this table
the companies must be unique.


mfG
--> stefan <--

 

[Mr-Re Man]

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.

Every year the company receives a new form, so their is a one to many
relationship set-up.

This is your code tweaked, which works great but includes duplicates

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

 

[John Spencer]

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

 

[Stefan Hoffmann]

hi,

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 <--

 

[Mr-Re Man]

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!

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

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

.