Prevent repeated data?

[Henrootje]

I have a query that will generate a list of documents that has to be
printed for a certain country:

This selectioon procvess does what we want

SELECT qryNL.[Res/NONRES], qryNL.[SelectRES/NONRES], qryNL.Client,
qryNL.Land, qryNL.DocCode, qryNL.DocNaam, qryNL.DocTekst,
qryNL.DocToelichting, qryNL.Doc_Intern_NL
FROM qryNL;



This would deliver e.g. the following:

Belgium
CPA - Certified Copy Passport
POA - Power of attorney
STA - Statutes

Spain
CPA - Certified Copy Passport
POA - Power of attorney
STA - Statutes
POD - Poderes


But I would like to see the following


Belgium
CPA - Certified Copy Passport
POA - Power of attorney
STA - Statutes

Spain
POD - Poderes

Notice how the documents CPA / POA / STA are not mentioned at 'SPain'
because they have been mentioned already with Belgium.

How would I be able to do this?

Thanks for thinking!

Henro

 

[Joe]

#1)You should investigate using a recordset instead of all queries.
#2) Load the recordset via a query whatever
#3)Then walk the recordset record by record
#3.a) Have a series of variables CPA_shownAlready, POA_shownAlready,
etc. Intially set these to NO
#4) As you find matches set them to Yes
#5) Make your selection based on the flag
#6) Add found ones into a new building table
#7) Report on this new building table instead of a query
This sounds like a lot of work but you will open up a whole world of
possiblities by learning about recordsets and walking them.

 

[Henrootje]

Yes, the concept of walking through queries probably will open a new
world,

I run into more situations where that could solve things.
But though I understand the usability of walking through recordsets and
evaluating them one by one, I am unsure how to do this. Could you
clarify please?

Greetings,

Henro

 

[Henrootje]

Is there anyone that would like to have a go at this? Has suggestions,
tips, etc etc?

henro

 

[Tom Ellison]

Dear Henro:

It seems these documents are not needed with respect to any particular
country, they are just needed. If you drop the country from the query (and
then perform the query distinctly, if necessary), you would have the list of
documents.

If you need to see for which countries these documents are necessary, a
crosstab might be most useful. You could then have:

Country CPA POA STA POD
Belgium X X X
Spain X X X X

I have used X to represent what could be a check box with the box checked.

Tom Ellison

 

[Henrootje]

<quote> If you drop the country from the query (and
then perform the query distinctly, if necessary), you would have the
list of
documents. </unquote>

I do not really get this? If I drop the country, then how would I be
able to sort them by country?
How do I find those that are used in multiple countries?

 

[Tom Ellison]

Dear Henro:

From your first post I understood you only wanted to see each document once.
If the document applies to multiple countries, you would then NOT see the
multiple countries, only one. That's exactly what I had understood you to
have said.

My response was meant to give you two alternatives. If seeing the countries
for which a document are listed is not important, then drop the country.
Or, you could construct the crosstab that lists all the countries.

To see only one country per document, you could group the data by document
and just show the MIN() value of country. That's a third option. You could
take the results of this and then sort by country. That is another
solution, but which seems the least logical to me. However, perhaps it is
closest to what I understood you asked first.

If you understand the different solutions I have proposed, you could choose
one. If you then have any difficulty implementing it, I would be glad to
try to help with it.

Tom Ellison