Prev: select and count
Next: Include certain names in query
From: Mr-Re Man on 13 Apr 2010 05:19
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 05:29
hi,

On 13.04.2010 11:19, Mr-Re Man wrote:
> 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 <--
From: Mr-Re Man on 13 Apr 2010 06:54
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" wrote:

> hi,
>
> On 13.04.2010 11:19, Mr-Re Man wrote:
> > 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 <--
> .
>
From: Stefan Hoffmann on 13 Apr 2010 07:43
hi,

On 13.04.2010 12:54, Mr-Re Man wrote:
> 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 <--
From: Mr-Re Man on 13 Apr 2010 08:31
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"
);


"Stefan Hoffmann" wrote:

> hi,
>
> On 13.04.2010 12:54, Mr-Re Man wrote:
> > 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 <--
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: select and count
Next: Include certain names in query