Creating unique identifier in query

[Phil Stokes]

I have a table with orders information which relates to cutomer information
in another table. I have also got a table of delivery addresses relating to
certain customers. these are amalgamated in a union query with the billing
addresses of each customer giving a comprehensive list of addresses for all
of the customers (The billing address having the identifier 0 and the
delivery addresses having the identifier alotted to them in the delivery
address table.) I have created a unique identifier for the delivery address
(to be stored in the orders table) be it the billing address or a different
delivery address using the clientsID(autonumber field in clients table) and
the DeliveryID from the Delivery Address table using the following SQL code

SELECT Trim(Str([Client NO]))+"-0" AS ShipClientID, [Client No], [Client
Name], [Client Address1] & ", " & [Client Address2] & ", " & [Client
Address3] & ", " & [Client Address4] AS [Billing Address], [Client Fax No]
FROM Clients
UNION SELECT Trim(Str([Client NO]))& "-" &Trim(Str([AltClientNoID])) AS
ShipClientID, [Client No],[Delivery Name] AS Name, [Delivery Address2] & ",
" & [Delivery Address3] & ", " & [Delivery Address4] AS [Delivery Address],
[Delivery Fax No]
FROM tblClientDelivery;

this produces a table with identifiers such as "124-4 for the fourth
delivery addres of client number 124. This is naturally stored as a text
field.

the related record in the orders table is stored as a text field and is
input using a combo box on the orders form that writes the ShipClientID to
the orders table.

when I create a query for a report I get "type mismatch in expression".

Are there any specific rules regarding relationships between two text
fields. can anyone suggest a way around this situation.

 

[John Vinson]

I have created a unique identifier for the delivery address
(to be stored in the orders table) be it the billing address or a different
delivery address using the clientsID(autonumber field in clients table) and
the DeliveryID from the Delivery Address table using the following SQL code

This kind of "intelligent key" - packing multiple disparate pieces of
data into one field - is NEVER either necessary nor a good idea!

Note that a Primary Key, or a Foreign Key, can consist of up to TEN
fields. It is not necessary to create a redundant field for this
purpose. Simply store the ClientID as a Long Integer field, and the
DeliveryID as another Long Integer field; you can concatenate them for
display purposes if you wish, in a calculated field in a query - but
don't store that concatenantion.

John W. Vinson[MVP]