Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2006
    Posts
    35

    Unanswered: Two tables, same field name, merged into one

    Using MS-Access 2003 I have a query with a summary table linked to two other tables that contain different info yet some field names are the same (contract header, delivery header tables containing a common field called creator). A listing from the summary table will have either a creator from the contract table or the delivery table but never both so duplicity is not a concern.
    Could anyone provide the syntax that I could insert to create a calculated field in the query that would allow me to sort the report based on this new calculated field? Some records may not have a creator due to human error somewhere in the process. As it now stands, I sort the report A-Z on the contract header value then A-Z on the delivery header value which leaves me with two sections. The "creator" field is text and contains firstname then last - it comes from a feed from the mainframe so sorting this on the firstname will have to do.

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    To uniquely identify the fields in the query you just have to put the table name at the beginning, surrounded by square brackets. So to refer to Creator in Table1 the syntax is [Table1].[Creator]. Therefore the caluclated field in query design would be:

    CreatorName:[Table1].[Creator] & " " & [Table2].[Creator]

    or in SQL:

    [Table1].[Creator] & " " & [Table2].[Creator] as CreatorName

  3. #3
    Join Date
    Jan 2006
    Posts
    35
    DCK, Thanks!
    That was exactly what I was looking for.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •