Help with a query

[herbertioz]

I have two colums in a table in my database. The first one is a list of Motors and the second one a column with the Status of this motors.

For example:

ID Motors Status
1 Mot01 Installed
2 Mot01 Installed
3 Mot01 In Stock
4 Mot02 In Stock
5 Mot03 Installed
6 Mot03 Installed
7 Mot03 Installed


I want to make a query who present only one motor (if the motors has the same motorname) that has Status only "Installed".
The point is to get a list of the motors I need to order.

The result I would like to have:

Motors
Mot03

 

[plog]

want to make a query who present only one motor (if the motors has the same motorname) that has Status only "Installed".

Huh? Do you mean, 'I want a list of motors that have no 'In Stock' status? If so, you will need a sub-query.

First create a query that is a list of all motors that are in stock:

SELECT Motors FROM YourTableNameHere GROUP BY Motors WHERE Status = 'In Stock';

Name that 'subInStock' and be sure to replace the instance of 'YourTableNameHere'
with the actual name of your table.

Now, create another aggregate query based on YourTableNameHere and that sub-query. Make a LEFT JOIN from YourTableNameHere to subInStock on the Motors field. Bring the Motors field from both data sources. Change the GROUP BY underneath the Motors field from subInStock to 'Where' and put 'Is Null' in the criteria. Run that query and you will have your list.

 

[herbertioz]

Thanks for help I need some help to understand. The subInstock query is ok, but what do you exactly mean by the rest?

 

[plog]

It was pretty straight forward, a little complicated, but straight forward. What are you having issues with? What have you tried?

 

[herbertioz]

Something like these:

SELECT *
FROM Motortable LEFT JOIN subInStock ON Motortable.Motors = subInStock.Motors WHERE (((subInStock.Motors Is Null));

 

[plog]

So what did that give you? What's the issue?

 

[herbertioz]

I am not getting one motor, but all motors that is "Installed". I have multiple motors with the same name and someone is "In Stock" and some is "Installed".

I want to get a list with only the motors which is "Installed" and have not any InStock if the motor has the same name.

 

[herbertioz]

Sorry for that last post. You've solved it Now I got what I want.

SELECT *
FROM Motortable LEFT JOIN subInStock ON Motortable.Motors = subInStock.Motors WHERE (((subInStock.Motors Is Null));

Thanks for all help