Copy 2 Tables to 1 Table

[donlapre]

Hi,

I have two fields (Address & Street) in a table that I would like to combine into one field (Full Address) in the same table. How do I make the "Full Address" field automatically contain the values of the first two combined fields with a space in between?

I'm doing it this way because I need to be able to interface with a Visual Basic program that uses the Full Address field (I don't know how to reprogram it to use 2 fields). However, I also need to be able to sort the lists by street name which would require two fields to separate the address and the street.

Thanks,

Matt

 

[donlapre]

Another way that I could solve this problem would be if you could tell me how to do a query that sorts the "Full Address" field by street, then by address. So if the fields are "555 Pine St" "333 Pine St." "999 Jones Rd." "333 Jones Rd.", then the query would sort them like 333 Jones Rd, 999 Jones Rd, 333 Pine St, 555 Pine St.

That would probably be a better database design. Thanks,

 

[Darrylles]

Don,

The 'Order By' clause (as with all sorting) sorts by the first char then the 2nd etc. So 333 Pine St would follow 333 Jones Rd.

To combine 2 fields in code with a space use this:

Dim Mystr as String 'Create a variable called Mystr.

Mystr = "Field1" & " " & "Field2"

------------------------------------------------------
To update the Full Address field create a simple select query like this to create the combined address (call it qryCombinedAddress):

SELECT tablea.Address, tablea.Street, [Address] & " " & [Street] AS expr
FROM tablea;

now create an UPDATE query called qryCombinedUpdate which will actuall fill the new field with the results:

UPDATE tablea INNER JOIN [QryCombinedAddress] ON tablea.pk = [QryCombinedAddress].pk SET tablea.deploc = [QryCombinedAddress].[expr];

Obviously replace 'tablec' and fieldnames with yours.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."