|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!
|
|
#1
April 20th, 2004, 10:46 AM
|
|||
|
|||
|
MS Access Query Help
I am trying to find all records where certain fields match AND one field doesn't.
I have the following fields in the table 'customers': Company, FirstName, LastName, CustomerNumber I want to be able to find all records where FirstName and LastName are the same but CustomerNumber is not. I've had success getting all records where FirstName and LastName are the same but don't know how to add the last condition. Any help would be greatly appreciated! Paneolo 
|
|
#2
April 22nd, 2004, 11:21 AM
|
|||
|
|||
|
I'm not clear on what you're asking...I assume you're loooking for solution for this...
Let's take record A and record B, you want to return where recordA.lastname = recordB.lastname AND recordA.firstname = recordB.firstname AND recordA.CustomerNumber <> to recordB.CustomerNumber?? Is that right?
|
|
#4
April 22nd, 2004, 11:38 AM
|
|||
|
|||
|
IF the customer ID is the primary key and always unique, this will work...know that for both examples, the count on CustomerNumber (in the SELECT) isn't necessary, just thought I'd throw it in.
This will find the people that fit the criteria... Code:
Select C.lastname, C.firstname, count(C.CustomerNumber) as countCustNum from tblCustomers C group by C.lastname, C.firstname having count(C.CustomerNumber) > 1; ...if you still need to go back and return all the fields in the table, join the above query back to the Customers table... Code:
Select * from Customers C
inner join
(Select C.lastname, C.firstname,
count(C.CustomerNumber) as countCustNum
from tblCustomers C
group by C.lastname, C1.firstname
having count(C.customerNumber) > 1) as A
on C.Lastname = A.lastname
and C.firstname = A.firstname;
...is the CustomerID in fact a unique value in all the records? Last edited by Username=NULL : April 23rd, 2004 at 03:21 AM.
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > MS Access Query Help |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
