Results 1 to 3 of 3
2015-05-14, 01:24 #1
- 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"
2015-05-14, 01:50 #2jwoodsGuest
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.
2015-05-14, 14:01 #3
- Join Date
- Jan 2010
- Fort McMurray, Alberta, Canada
- Thanked 83 Times in 79 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!