Trouble With SELECT syntax

[rokuk]

Hi there!

I am having a touch of trouble with a SELECT statement. It keeps asking me to "Enter Parameter Value" even though I have everything spelled / punctuated correctly and it all exists. I remember that this was once because my syntax was off, and I think this is the same reason for this time as well - but I can't seem to puzzle it out!!

This is what I'm trying to get working (MS Access 2003):

SELECT *
FROM Deletions
WHERE Deletions.[Big Name] <> tblHardware.bigname
OR Deletions.QPP <> tblHardware.qpp
OR Deletions.[Big Number] <> tblHardware.bigNumber


Any advise would be greatly appreciated!!

 

[PaulBricker]

How about
SELECT *
FROM Deletions
[blue]INNER JOIN Deletions ON tblHardware.somefield = Deletions.somefield[/blue]
WHERE Deletions.[Big Name] <> tblHardware.bigname
OR Deletions.QPP <> tblHardware.qpp
OR Deletions.[Big Number] <> tblHardware.bigNumber


Paul

 

[rokuk]

Hi Paul!

Thank you for your suggestion. My concern is that using an INNER JOIN will require one field to definitely match, instead of using a looser structure... I know this should not be a problem with proper primary keys in place, but I have inherited this database where the data is not all standardized and don't always trust the fields to match up when they -should- (which is actually the reason I am checking all of these three fields - which all "should" be the same... but for accuracy's sake I wanted to find them all).

I am trying to go through and standardize everything, but in the mean time I fear that fields may be falsely excluded using the INNER JOIN in this case, unfortunately - is that correct?

For example, if I choose to INNER JOIN ON the .QPP field, that is fine and everything is perfect if all the QPP's match up (it will correctly select records with the other two fields). However if there is an issue with the QPP field, that record would be mis-handled. Is that correct thinking?

If that is the best way to go, I will do it, but I just wanted to resolve that feeling I have that something could go possibly awry with the data I currently find myself with...

Thank you again for your suggestion!

 

[lespaul]

the alternative if there are not keyed fields to join on is a cartesian join:

SELECT *
FROM Deletions[b], tblHardware[/b]
WHERE Deletions.[Big Name] <> tblHardware.bigname
OR Deletions.QPP <> tblHardware.qpp
OR Deletions.[Big Number] <> tblHardware.bigNumber

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[PHV]

Perhaps this ?

SELECT * 
FROM Deletions INNER JOIN tblHardware ON Deletions.QPP = tblHardware.qpp
WHERE Deletions.[Big Name] <> tblHardware.bigname 
OR Deletions.[Big Number] <> tblHardware.bigNumber
UNION SELECT * 
FROM Deletions INNER JOIN tblHardware ON Deletions.[Big Name] = tblHardware.bigname
WHERE Deletions.QPP <> tblHardware.qpp
OR Deletions.[Big Number] <> tblHardware.bigNumber
UNION SELECT * 
FROM Deletions INNER JOIN tblHardware ON Deletions.[Big Number] = tblHardware.bigNumber
WHERE Deletions.QPP <> tblHardware.qpp
OR Deletions.[Big Name] <> tblHardware.bigname

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886