如何获得“查询"链接到SQL表时在Access中具有什么功能? [英] How to get "Lookup" functionality in Access when linking to a SQL table?

问题描述

我正在构建一个SQL数据库，该数据库将具有Access 2010前端.

I am building a SQL database which will have an Access 2010 front-end.

我希望某些字段在Access中进行查找(即用户单击Access中的字段，然后填充一个下拉列表).使字段成为Access中另一个表的查询非常简单，但我似乎不知道如何在SQL中进行操作，然后传播更改.

I would like some of the fields to be lookups in Access (ie the user clicks on the field in Access and a drop down populates). It is fairly straightforward to make a field a lookup for another table in Access but I can't seem to know how to do it in SQL and then propagate the changes.

我的SQL知识非常基础.这是我如何创建SQL表的示例:

My SQL knowledge is very basic. Here's an example of how I am creating my SQL tables:

CREATE TABLE RequestTypes (
    RequestType varchar(50) PRIMARY KEY
);
INSERT INTO RequestTypes (RequestType) VALUES ('Val 1');
INSERT INTO RequestTypes (RequestType) VALUES ('Val 2');
INSERT INTO RequestTypes (RequestType) VALUES ('Val 3');

CREATE TABLE Projects (
    ID int IDENTITY(1,1) PRIMARY KEY,
    RequestStatus varchar(50) FOREIGN KEY REFERENCES RequestStatus(RequestStatus),
    Quantity varchar(50)
);

然后我通过Access中的ODBC连接连接到数据库.

I then connect to the database through the ODBC connection in Access.

如何在SQL中创建表，以使Projects表的RequestStatus字段具有与查找表相同的功能?例如，能够单击项目的RequestStatus属性，然后从列表中选择"Val 1"或"Val 2"或"Val 3".上面的确要求表匹配，但不提供下拉"查找功能.

How can I create my tables in SQL so that the RequestStatus field of my Projects table to have the same functionality a lookup table does? For example, being able to click on the RequestStatus attribute of a Project and select "Val 1" or "Val 2" or "Val 3" from a list. The above does require the tables to match but does not provide the "dropdown" lookup functionality.

推荐答案

在SQL Server中创建表，链接到该表，然后将该表用作所需组合框/下拉列表的行源属性.

Create the table in SQL Server, link to it, then use that table as the row source property for the desired combo box / drop down.

这是在SQL Server中创建表的非常基本的语法:

This is the very basic syntax to create a table in SQL Server:

CREATE TABLE LookupTest 
(
    ID INT NOT NULL,
    LookupValue varchar(255)
);

这篇关于如何获得“查询"链接到SQL表时在Access中具有什么功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！