LEFT JOIN??? - How to discovery tblACESSORIES not recorded in tblACESSORIES-CAR

[JR3151006]

Hi all,

i'm working with Access 2003 in the following scenario:

tblCARS
#####
COD, CARNAME

tblACESSORIES
##########
COD, ACESSORIENAME

tblACESSORIES-CARS
##############
COD, CODCAR, CODACESSORIE

* In this MDB, the Relationships (1-n) are:
tblCARS.COD = tblACESSORIES-CARS.CODCAR
tblACESSORIES.COD = tblACESSORIES-CARS.CODACESSORIE

Table Data's
#########
tblCARS have 2 records in COD and CARNAME fields:
1, Ferrari
2, Hummer

tblACESSORIES have 3 records in COD and ACESSORIENAME fields:
1, Light
2, Mp3 Radio
3, GPS

tblACESSORIES-CARS have only one record in COD, CODCAR and CODACESSORIE fields:
1, 1, 2

* This mean that: Ferrari have a MP3 radio... ok...

Now, i would like to construct a SQL that show me what ACESSORIES still havent found on tblACESSORIES-CAR, in this case would be 'Light' and 'GPS' to FERRARI.
For Hummer, the SQL should show all three acessories: Lighr, GPS and Mp3 Radio because none of these are recorded in tblACESSORIES-CAR for Hummer.

The Microsoft Access 2003 have a query-wizard that search for 'no-coincident', I already try it that only show me WHAT acessories still not recorded at tblACESSORIES-CAR. But this query do not show what acessories are 'missing' for FERRARI for example! If I specify CODCAR it dont show anything more!! Why?

I need (by my asp code) list for the car (can be Ferrari or Hummer, for example) what acessories I still can add! How to 'fix' it??

tks

Renato Jr
jrduetto@yahoo.com.br

 

[Jon K]

For the Left Join to work correctly, you need a series of two queries.
See the queries in the attached database.

Run the second query and enter a car cod when asked.
Hopefully, you can run it in your version of Access.


Just in case ...
The database contains these queries.

Query1:-
SELECT tblCARS.COD AS CodCar, tblCARS.CARNAME, tblACESSORIES.COD AS CodAccessory, tblACESSORIES.ACESSORIENAME
FROM tblCARS, tblACESSORIES
WHERE tblCARS.COD=[Enter a car cod];

Query2:-
SELECT Query1.CodCar, Query1.CARNAME, Query1.CodAccessory, Query1.ACESSORIENAME
FROM Query1 LEFT JOIN [tblACESSORIES-CARS] ON (Query1.CodAccessory=[tblACESSORIES-CARS].CODACESSORIE) AND (Query1.CodCar=[tblACESSORIES-CARS].CODCAR)
WHERE [tblACESSORIES-CARS].CODCAR Is Null;

.