2 tab query question

[Cederic]

Hi,

I want to write a query to re-assemble missing/stolen CD's
example: I have 1000 CD boxes without CD and 850 cd's without box

I created two tables BOX and CD.
Table BOX
id,Article, description, location
1,144 ,R.E.M, 16a
2,2,MADONNA,164
3,1,TOP HITS 2003,1501
4,144,REM,174
5,150,SUMMERHITS,188
Table CD
id, article, description, location
10,144,REM,8044
12,144,REM,8058
13,5,PEARL JAM,4545
14,150,SUMMERHITS,2444

I want to have a query that returns all articles from BOX that have an
identical article in CD.
SELECT box.article,box.description,box.location,cd.location from box, cd
where box.article=cd.article

that's the result I get from the query:
144,REM,16a,144,8044
144,REM,16a,144,8058

144,REM,174,144,8044
144,REM,174,144,8058

150,SUMMERHITS,188,150,2444

But I want as result only one line per article (an article from table CD can
only be used once)
144,REM,16a,144,8044
144,REM,174,144,8058
150,SUMMERHITS,188,150,2444

Thanks for help!

 

[Michel Walsh]

Hi,


SELECT DISTINCT ...


Hoping it may help,
Vanderghast, Access MVP

 

[Cederic]

Hello,

With distinct I get next result:

Hi,


SELECT DISTINCT ...


Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


Make a BoxQuery:

SELECT *, DCount("*", "Box", "Article=" & Article & " AND id<=" & id ) As Rank
FROM Box


Make a CDquery:

SELECT *, DCount("*", "CD", "Article=" & Article & " AND id<=" & id ) As Rank
FROM CD


Make your final query:

SELECT a.*, b.*
FROM BoxQuery As a INNER JOIN CDQuery As b
ON ( a.Article=b.Article AND a.Rank = b.Rank)



Hoping it may help,
Vanderghast, Access MVP