Hi,
I have created an union query to pull together the same data from 2 ODBC tables (seperate countries).
As there is a clash in the client number I have added a "N" prefix to differentiate NZ from Australia, as below:
The problem I now have is that I get type mismatch when linking to another table. How could I change the SQL below to dictate the field format to number (providing it will accept the "N" prefix) or all to text?
Thanks in advance
Michael
SELECT dbo_ClientMaster.ClientNumber, dbo_ClientMaster.Name, dbo_ClientMaster.AddressLine1, dbo_ClientMaster.AddressLine2, dbo_ClientMaster.AddressLine3, dbo_ClientMaster.AddressLine4, dbo_ClientMaster.TradeCode, dbo_ClientMaster.ReviewLimit, dbo_ClientMaster.ClientStartDate, dbo_ClientMaster.TypeOfTrade, dbo_ClientMaster.NextReviewDate, dbo_ClientMaster.LastReviewDate, dbo_ClientMaster.TerminationDate, dbo_ClientMaster.TerminationReason, dbo_ClientMaster.BankSortCode, dbo_ClientMaster.BankAccountNumber
FROM dbo_ClientMaster;
UNION ALL SELECT "N" & [dbo_ClientMaster1.ClientNumber], dbo_ClientMaster1.Name, dbo_ClientMaster1.AddressLine1, dbo_ClientMaster1.AddressLine2, dbo_ClientMaster1.AddressLine3, dbo_ClientMaster1.AddressLine4, dbo_ClientMaster1.TradeCode, dbo_ClientMaster1.ReviewLimit, dbo_ClientMaster1.ClientStartDate, dbo_ClientMaster1.TypeOfTrade, dbo_ClientMaster1.NextReviewDate, dbo_ClientMaster1.LastReviewDate, dbo_ClientMaster1.TerminationDate, dbo_ClientMaster1.TerminationReason, dbo_ClientMaster1.BankSortCode, dbo_ClientMaster1.BankAccountNumber
FROM dbo_ClientMaster1;
I have created an union query to pull together the same data from 2 ODBC tables (seperate countries).
As there is a clash in the client number I have added a "N" prefix to differentiate NZ from Australia, as below:
The problem I now have is that I get type mismatch when linking to another table. How could I change the SQL below to dictate the field format to number (providing it will accept the "N" prefix) or all to text?
Thanks in advance
Michael
SELECT dbo_ClientMaster.ClientNumber, dbo_ClientMaster.Name, dbo_ClientMaster.AddressLine1, dbo_ClientMaster.AddressLine2, dbo_ClientMaster.AddressLine3, dbo_ClientMaster.AddressLine4, dbo_ClientMaster.TradeCode, dbo_ClientMaster.ReviewLimit, dbo_ClientMaster.ClientStartDate, dbo_ClientMaster.TypeOfTrade, dbo_ClientMaster.NextReviewDate, dbo_ClientMaster.LastReviewDate, dbo_ClientMaster.TerminationDate, dbo_ClientMaster.TerminationReason, dbo_ClientMaster.BankSortCode, dbo_ClientMaster.BankAccountNumber
FROM dbo_ClientMaster;
UNION ALL SELECT "N" & [dbo_ClientMaster1.ClientNumber], dbo_ClientMaster1.Name, dbo_ClientMaster1.AddressLine1, dbo_ClientMaster1.AddressLine2, dbo_ClientMaster1.AddressLine3, dbo_ClientMaster1.AddressLine4, dbo_ClientMaster1.TradeCode, dbo_ClientMaster1.ReviewLimit, dbo_ClientMaster1.ClientStartDate, dbo_ClientMaster1.TypeOfTrade, dbo_ClientMaster1.NextReviewDate, dbo_ClientMaster1.LastReviewDate, dbo_ClientMaster1.TerminationDate, dbo_ClientMaster1.TerminationReason, dbo_ClientMaster1.BankSortCode, dbo_ClientMaster1.BankAccountNumber
FROM dbo_ClientMaster1;