simple select query question

[optionone]

Hi,

Still rather new to access but have some sql experience.. using access 2003

Have two tables inner joined in a select query (customers and client_index) and both have a field called account_type. client_index will have the most up to date accout type and so i want to select all the lines where the account types dont match so i can amend them

Seems like it would be simple to do in SQL with just putting a condition in the where/and line that the two fields dont equal each other but not sure how to do this in access as it just ends up going into the criteria bit and not working?

Thanks in advance

 

[Galaxiom]

Access speaks sql quite well. The criteria box is basically a graphical representation of the where clause in the sql. What you have described sounds like what should happen.

What results are you getting when you run the query?

BTW, your question sounds like a normalization anomaly since you have two versions of account_type for the same client/customer.

 

[optionone]

Hi,

I just get all the results back including the ones where the two account types match

SELECT *
FROM [Customers] INNER JOIN CMI ON [Customers].ID = CMI.ID 
WHERE CStr([Customers].Account_Num) <> CMI.Account_Num;

CStr as customers table account num is a number field in access where as in CMI its a sql server linked varchar

Thanks

 

[Galaxiom]

I expect it is to do with the sql varchar being a non-Unicode character format.

http://msdn.microsoft.com/en-us/library/aa258242(SQL.80).aspx

 

[MSAccessRookie]

Hi,

I just get all the results back including the ones where the two account types match

SELECT *
FROM [Customers] INNER JOIN CMI ON [Customers].ID = CMI.ID 
WHERE CStr([Customers].Account_Num) <> CMI.Account_Num;

CStr as customers table account num is a number field in access where as in CMI its a sql server linked varchar

Thanks

Your problem may be that (as Galaxiom pointed out) a String of type varchar() is viewed as fixed in length. Therefore if the one table contains a Number valued at 12345 while the other table contains a varchar(8) valued at "12345" then your statement interprets as follows:

SELECT *
FROM [Customers] INNER JOIN CMI ON [Customers].ID = CMI.ID 
[B][COLOR=red]WHERE "12345" <> "12345   "[/COLOR][/B];

As you can see, this comparison will always be true unless the Value from the table with the Number has as many digits as the varchar() array declaration.

Reversing the comparison may resolve the issue.

SELECT *
FROM [Customers] INNER JOIN CMI ON [Customers].ID = CMI.ID 
WHERE [B][COLOR=red][Customers].Account_Num[/COLOR][/B] <> [B][COLOR=red]CDbl(CMI.Account_Num)[/COLOR][/B];

 

[SOS]

Just a reminder -

varChar doesn't pad the field.

Char does.