match records between 2 queries

[gerard1979]

I have 2 queries
qryCP: CP_id, naam_contactpersoon
gives all customer records
qryYear: CP_id, Year
gives customerID's with year when they get present
CP_id year
1 1999
1 2002
2 2002

QryCP must have one more field: this field contains the match between the queries, if CP_id, 2002 matches in the second query, the field = "Yes", else, the field = "No"

Any idea?
tnx,
Gerard

 

[KenReay]

Hi

Assuming you want to start from the point of having the two queries you quote:

Make a third query, based on the two queries you quote.

Join on Cp_Id

Drag CP_id, naam_contactpersoon into the "Select" portion of the query grid

Add a calculated field to the query grid X:"Yes"

Set a criteria on Year of 2002

One last point, avoid using Access reserved words as column names (Year is such a word)



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[gerard1979]

Hi Ken,
thanks for ur quick reply.
the problem is:
There are 2900 customers
joining the tables with left join gives 4000 records,
there are duplicates in the qryYear
joining normal way gives 1300 or somewhat records,
there are customers without presents.
I want a table with all customers (2900) and for the year 2002, yes if the record have a match in qryYear, and no, if the record have no match in query year.
I give it a little try and becomes to this:
SELECT [contactpersoon + telefoonnummer].CP_id, [contactpersoon + telefoonnummer].bedrijfsnaam, [contactpersoon + telefoonnummer].naam, [contactpersoon + telefoonnummer].omschrijving, jaargangen.jaargangen
FROM [contactpersoon + telefoonnummer] LEFT JOIN jaargangen ON [contactpersoon + telefoonnummer].CP_id = jaargangen.CP_id
WHERE (((jaargangen.jaargangen) Is Null Or (jaargangen.jaargangen)=2002));

this give 2500 rows it only not give the rows where year <> 2002. the trick here is, I only need the distinct rows. So there are CP_id's with more then 1 match in the table.

 

[gerard1979]

I got the answer:
first bound qryYear to 2002, at most 1 match per Customer
then left join this table with allcustomers.
the null values set on NO, the matched values (2002) set on YES.

Thnx Ken, indirect u give me the idea

Gerard