April 12, 2016 at 2:42 am
Hi all,
I am sorry if this is a silly question i am new to SQL although im ok with VB. But can you assign the Primary Key to one of the existing fields in my Query/View?
EXAMPLE
SELECT DepartmentCode, DepartmentName
FROM Departments
Is it possible to make the DepartmentCode the Primary Key?
Also another noob question...I know what im creating is in the 'View' part of the database but would it be called a Query or View? A "SELECT query" sounds right to me but I could be wrong.
Thanks in advance
Chris
April 12, 2016 at 2:59 am
wrightyrx7 (4/12/2016)
Is it possible to make the DepartmentCode the Primary Key?
No, a primary key is a property of the table, not of a particular query that you execute against the table. What exactly are you trying to achieve?
I know what im creating is in the 'View' part of the database but would it be called a Query or View? A "SELECT query" sounds right to me but I could be wrong.
It's a query ("SELECT query" is fine). When you use a CREATE VIEW statement to set it up as a view, then it becomes a view, which you would then query as if it were a table:
SELECT DepartmentCode, DepartmentName
FROM MyView
John
April 12, 2016 at 3:12 am
John Mitchell-245523 (4/12/2016)
wrightyrx7 (4/12/2016)
Is it possible to make the DepartmentCode the Primary Key?No, a primary key is a property of the table, not of a particular query that you execute against the table. What exactly are you trying to achieve?
I know what im creating is in the 'View' part of the database but would it be called a Query or View? A "SELECT query" sounds right to me but I could be wrong.
It's a query ("SELECT query" is fine). When you use a CREATE VIEW statement to set it up as a view, then it becomes a view, which you would then query as if it were a table:
SELECT DepartmentCode, DepartmentNameFROM MyView
John
Thank you for your reply John.
There is nothing I am trying to achieve but when I linked the Query to an Access database it asked for the primary key which confused me a little haha.
And thank you for clearing up the VIEW/QUERY, i just wanted to be sure I refer to it as the correct thing when using the forums.
Chris
April 12, 2016 at 2:16 pm
wrightyrx7 (4/12/2016)
John Mitchell-245523 (4/12/2016)
wrightyrx7 (4/12/2016)
Is it possible to make the DepartmentCode the Primary Key?No, a primary key is a property of the table, not of a particular query that you execute against the table. What exactly are you trying to achieve?
I know what im creating is in the 'View' part of the database but would it be called a Query or View? A "SELECT query" sounds right to me but I could be wrong.
It's a query ("SELECT query" is fine). When you use a CREATE VIEW statement to set it up as a view, then it becomes a view, which you would then query as if it were a table:
SELECT DepartmentCode, DepartmentNameFROM MyView
John
Thank you for your reply John.
There is nothing I am trying to achieve but when I linked the Query to an Access database it asked for the primary key which confused me a little haha.
And thank you for clearing up the VIEW/QUERY, i just wanted to be sure I refer to it as the correct thing when using the forums.
Chris
Access has its own rules and its own weirdnesses.
If you create a linked table, you can indeed link it to a query. In that case, because the query blurs the relationship to the underlying tables, Access cannot figure out what is logically the key in the query's result. So it asks. You do not need to supply one, but that limits your options to edit the data from Access - because Access needs a key to identify a single row. Of course, the query might actually not include a key at all, in which case you'll have to accept that you cannot edit the linked table. However, if there is a column (or combination of colums) that always uniquely identifies a single row in the result set, then you can specify that as the key. Access will store that along with the defnition of the metadata, and use it when you try to insert, update, or delete data.
Also beware of the terminology difference. What Access calls a "query" is what in SQL Server is called a "view". It is named, stored, and can be used as a source in another view or query. The SQL Server term "query" is usually used for ad-hoc queries, a concept I have not found an equivalent for in Access. This is a query you type and execute but do not store (allthough you can of course save the text of the query statement in a text file on your file system).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply