Not in query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
I have a query
with 3 tables
One - model
one colour
one the connect model and colour, just with two numbers

Like:
SÃ¥ in my connect table I maybe have
1 - 1 ' so this is model 1 and colour 1
1 - 2 ' so this is model 1 and clour 2
And so on

Now i want to see all the clours in my colour table, but just the clours
where the selected model not have, i can make a query the show me alle the
clours the model have but i can't make a query there show me all the clours
the model don't have yet
Hope someone can help, and understand what i trying to do

regards
Alvin
 
Hi Alvin

The easiest way to create this is to run the 'Find Unmatched' Query Wizard.
The query will be something like:

SELECT tblModel_Colour.iModelID, tblModel_Colour.iColourID
FROM tblModel_Colour LEFT JOIN tblColour ON tblModel_Colour.iColourID =
tblColour.iColourID
WHERE (((tblColour.iColourID) Is Null));
 
Hi pete
sorry i can't get it to work
Not so god at this
MAybe i don't understand what you have write
I can't see how the query can show me wjat clour i dont have in model 2

Like show me all the clours in my color table Where model 2 not in
connect table .

My table is =
Table model `= modelid and modelname
Table color = colorid and colorname
table concorlorandmoldel = modelid and colorid

alvin
 
Hi Alvin

Sorry I had misunderstood you. There may be better ways to do this, but this
works...

First of I created a catesian query, qryAll that returns every combination
of model and colour:

SELECT tblModel.iModelID, tblColour.iColourID
FROM tblColour, tblModel;

Then a second query that returns the colours and models that are not in your
link table using an unmatched query.

SELECT tblModel.cModelName, tblColour.cColourName
FROM ((qryAll LEFT JOIN tblModel_Colour ON (qryAll.iModelID =
tblModel_Colour.iModelID) AND (qryAll.iColourID = tblModel_Colour.iColourID))
INNER JOIN tblColour ON qryAll.iColourID = tblColour.iColourID) INNER JOIN
tblModel ON qryAll.iModelID = tblModel.iModelID
WHERE (((tblModel_Colour.iModelID) Is Null) AND ((tblModel_Colour.iColourID)
Is Null))
ORDER BY tblModel.cModelName, tblColour.cColourName;
 
Thanks
I ytu it out
Alvin

Pete said:
Hi Alvin

Sorry I had misunderstood you. There may be better ways to do this, but this
works...

First of I created a catesian query, qryAll that returns every combination
of model and colour:

SELECT tblModel.iModelID, tblColour.iColourID
FROM tblColour, tblModel;

Then a second query that returns the colours and models that are not in your
link table using an unmatched query.

SELECT tblModel.cModelName, tblColour.cColourName
FROM ((qryAll LEFT JOIN tblModel_Colour ON (qryAll.iModelID =
tblModel_Colour.iModelID) AND (qryAll.iColourID = tblModel_Colour.iColourID))
INNER JOIN tblColour ON qryAll.iColourID = tblColour.iColourID) INNER JOIN
tblModel ON qryAll.iModelID = tblModel.iModelID
WHERE (((tblModel_Colour.iModelID) Is Null) AND ((tblModel_Colour.iColourID)
Is Null))
ORDER BY tblModel.cModelName, tblColour.cColourName;
Click to expand...
 
Back
Top