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.
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: