Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Brisbane, Queensland, Australia
    Thanked 0 Times in 0 Posts

    Possible Newbie type question but.....

    If I have the Job Number linking various tables, can I call the field JobNum in each table


    or should I have


    I am trying clean up code and I had

    strSQL = "SELECT * FROM tblOriginal WHERE [Job #]=" & [Job#]< job number from form from underlying table tblDelDockets

    and now have

    strSQL = "SELECT * FROM tblOriginal WHERE [JobNum]=" & [JobNum] I changed all variations of fieldname Job* to JobNum using the FIND AND REPLACE addin by Rick Fisher

    Now when I run the code it tells me that the 2nd [JobNum] is empty but it if I refer to the textbox name txtJobNo it works.

    Have I done a bad thing?
    Last edited by weyrman; 2015-05-14 at 01:31.
    "Heading for the deep end"

  2. #2
    You can just call it JobNum in all the tables, although some prefer the abbreviated table name as part of the column name, similar to your example.

    Table aliasing in a query or stored procedure sort of kills that notion, as you see in this example...

    SELECT a.CompanyName, b.OrderID, c.LastName
    FROM Northwind..Customers a
    INNER JOIN Northwind..Orders b ON a.CustomerID = b.CustomerID
    INNER JOIN Northwind..Employees c ON b.EmployeeID = c.EmployeeID

    Using a, b, and c does the same thing.
    Last edited by jwoods; 2015-05-14 at 01:55.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2010
    Fort McMurray, Alberta, Canada
    Thanked 92 Times in 87 Posts
    The field can have the same name in every table. SQL won't let you create ambiguous references, no matter what you do.

    As jwoods said, table aliases are typically used to distinguish which JobNum field you are referring to. Even in the absence of aliases though, you can always qualify the field with the table, just as you did in your example. Thus,


    These are perfectly valid field names, and SQL will know exactly what you are talking about.

    AFAIK Codd never said anything about the rules for naming foreign keys (that's what these are BTW). In production systems it's not uncommon to see different names on the foreign key fields. However I don't much care for various names for the foreign keys. Though if it were to be done, your way is a decent way to implement.

    Here's the larger issue. Take someone new to your system, and they have to make a report, or figure out how it works at the schema level. If the foreign key fields have the same name as the primary key in it's home table, that's a huge hint that those fields relate to each other. Conversely, the more the field names diverge, the more difficult it is for someone to understand that there is that connection.

    Nor is this a theoretical problem. This issue arises regularly in real-world systems. For reporting in particular, relatively non-technical users get exposed to your schema every day. So take pity on them and keep the field names the same!

Posting Permissions

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