SQL Code To Define Field Format - Mismatch

[mellis]

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;

 

[MarkK]

In this case I would be tempted to add a field to the query to distinguish what a record's original table was. Consider this simplified example. . .

SELECT Field1, Field2, "Table1" As srcTable
FROM Table1
UNION
SELECT Field1, Field2, "Table2" As srcTable
FROM Table2

This preserves the data type of the source data, but still gives you a handle on the source table of each row.
Hope this helps,

 

[CJ_London]

a number is a number, add a non numeric character and it becomes text.

Suggest either make all values text (i.e. add a different letter to the ClientNumber) or better to have a separate field for the N

SELECT "A" AS Country, dbo_ClientMaster.ClientNumber,....
UNION SELECT "N", [dbo_ClientMaster1.ClientNumber],...

The reason it is better is that indexing will be preserved which means your query will work faster and it will be easier to link to other tables