query question

  • Thread starter Thread starter Cederic
  • Start date Start date
C

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!
 
The Find Unmatched Query Wizard may help you. If not, the answer is that
you'll need an Outer Join.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Back
Top