search query in access

Question

Dear Users,

i have a question about searching the specific records in table, i have one main table of spare parts with different fields like part number, unit price, quantity....etc. when i receive a new inquiry, i need the price history that what price last time we purchased these items for this i use below query...

SELECT maintable.partnumber, maintable.unitprice, maintable.quantity
FROM maintable INNER JOIN sheet2 ON maintable.partnumber=newinquiry.partnumber;

it works perfect for me. but my question is this some of them times some times are new they are not in main table, obviously they are not searched for those items i want to write another query for not equal to ...

maintable.partnumber ( Not Equal To) newinquiry.partnumber;

i searched on net some where it was written as != and some where <>,

but none of these parameters worked for me ......

Thankssss

---

Tahir Mehmood

Answer 1

It is not a question of one being unequal to the other.  Something which exists can neither be equal to nor unequal to something which doesn't exist.  The solution is to join the tables in a LEFT OUTER JOIN and return rows where NULL is returned on the right side of the join:

SELECT *
FROM newinquery LEFT JOIN maintable
ON newinquery.partnumber = maintable.partnumber
WHERE maintable.partnumber IS NULL;

---

Ken Sheridan, Stafford, England

Answer 2

Hi, Tahir-Mehmood

Not equal to sign can’t solve your problem. I am agree with Ken Sheridan’s suggestion that you can use left or right join on the bases of the data you want to fetch from the table.

Use a LEFT JOIN operation to create a left outer join. Left outer joins include all of the records from the first (left) of two tables, even if there are no matching values for records in the second (right) table.

Use a RIGHT JOIN operation to create a right outer join. Right outer joins include all of the records from the second (right) of two tables, even if there are no matching values for records in the first (left) table.

Here I mentioned 2 links for left and right join from where you can get example of Left and Right Joins.

https://msdn.microsoft.com/en-us/library/office/ff198084.aspx?f=255&MSPPError=-2147217396

http://www.functionx.com/access/topics/outerjoin.htm

<> is used as not equal to sign in Access.

Regards

Deepak

Answer 3

thanks for your kind reply and explanation......

---

Tahir Mehmood