Re: Not Common both Tables
- From: Steve B. <SteveB@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 18 May 2006 09:41:01 -0700
Sylvain,
This runs but it returns to many records in the query. I added the FROM
[table]
SELECT Main2006.PartNumber, Main2006.AnnualCSIConfirmation
FROM Main2006
WHERE Not Exists (SELECT * FROM Main2005 WHERE Main2005.PartNumber =
Main2006.PartNumber AND Main2005.AnnualCSIConfirmation = "Yes")
AND (Main2006.AnnualCSIConfirmation = "Yes")
UNION ALL
SELECT Main2005.PartNumber, Main2005.AnnualCSIConfirmation
FROM Main2005
WHERE Not Exists (SELECT * FROM Main2006 WHERE Main2006.PartNumber =
Main2005.PartNumber AND Main2006.AnnualCSIConfirmation = "Yes")
AND (Main2005.AnnualCSIConfirmation = "Yes");
"Sylvain Lafontaine" wrote:
There is some differences between Access and SQL-Server for the syntax of.
SQL. For exemple, I'm sure that the Full Outer Join is not available under
Access. However, I'm pretty sure that the Exits() statement and the UNION
are available but for the UNION statement, try removing the ALL operator.
For the syntax error, it's probably a missing AND in the first subquery of
the first Select:
.... Select Main2006.PartNumber, Main2006.AnnualCSIConfirmation
where Not Exists (Select * from Main2005 where Main2005.PartNumber =
Main2006.PartNumber AND Main2005.AnnualCSIConfirmation = "Yes")
and (Main2006.AnnualCSIConfirmation = "Yes") ...
Please note also that I'm not sure about the need for the filters like «
Main2005.AnnualCSIConfirmation = "Yes") » because I don't know what you
really need from your description of your problem. I've added them as an
exemple.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Steve B." <SteveB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:003C0EC3-E1A6-4E09-88C3-DA6563ADF023@xxxxxxxxxxxxxxxx
Thanks Sylain, Sorry for the late reply.
It's for MS-Access and I'm getting a syntax error (missing operator).
I'll
keep looking. I also changed 2007 to 2005 in your post.
Steve
"Sylvain Lafontaine" wrote:
For Access or for SQL-Server?
You can use the Not Exists() statement with an Union:
Select Main2006.PartNumber, Main2006.AnnualCSIConfirmation
where Not Exists (Select * from Main2005 where Main2005.PartNumber =
Main2006.PartNumber Main2005.AnnualCSIConfirmation = "Yes")
and (Main2006.AnnualCSIConfirmation = "Yes")
Union All
Select Main2005.PartNumber, Main2005.AnnualCSIConfirmation
where Not Exists (Select * from Main2006 where Main2006.PartNumber =
Main2007.PartNumber and Main2006.AnnualCSIConfirmation = "Yes")
and (Main2005.AnnualCSIConfirmation = "Yes")
You can also use an Outer Join and select all the records which will have
their left or right values set to null; however, it's a little more
complicated to mix it with other WHERE clauses and I can't remember if
the
Full Outer Join is supported by Access.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Steve B." <SteveB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0ABD9FD3-3C66-48AE-B0E9-938C96989C7A@xxxxxxxxxxxxxxxx
This SQL selects all the common part numbers between the Main 2005/2006
tables but what criteria do I use to select the PN's that don't matchup
(<>)
between the two tables?
Steve
SELECT Main2006.PartNumber, Main2006.AnnualCSIConfirmation,
Main2005.PartNumber, Main2005.AnnualCSIConfirmation
FROM Main2006
INNER JOIN Main2005 ON Main2006.PartNumber = Main2005.PartNumber
WHERE (((Main2006.AnnualCSIConfirmation)="Yes") AND
((Main2005.AnnualCSIConfirmation)="Yes"));
- Follow-Ups:
- Re: Not Common both Tables
- From: Sylvain Lafontaine
- Re: Not Common both Tables
- References:
- Re: Not Common both Tables
- From: Sylvain Lafontaine
- Re: Not Common both Tables
- From: Sylvain Lafontaine
- Re: Not Common both Tables
- Prev by Date: Re: Form not saving to table
- Next by Date: Re: Not Common both Tables
- Previous by thread: Re: Not Common both Tables
- Next by thread: Re: Not Common both Tables
- Index(es):
Relevant Pages
|
