Access 2013 Web App-多值查找 [英] Access 2013 Web App - multi value lookup

问题描述

我正在Sharepoint上创建个人Access 2013 Web App. 我想为表格创建一个表格，该表格将基于多个条件查找ID号. 该表描述了带有某些字段的项目，我们将其称为位置，类别和单位.

I'm creating a personal Access 2013 Web App on Sharepoint. I want to make a form for a table that will lookup an ID number based on multiple criteria. The table describes projects with some fields, we'll call them location, category, and units.

relating_table:
id    location    category       units
1     france      maintenance    hours

然后我的项目表就会有

id    fk_id    teamid     whatever
1     1        x          y
2     1        z          a

我想设置我的表单，以便用户可以选择"france"，"maintenance"和"hours"，并且该表单将知道为"fk_id"输入1

I want to set up my form so the user can choose "france" "maintenance" and "hours" and the form will know to input 1 for the "fk_id"

我在Google上发现的大多数东西都是基于客户端的Access数据库.我试图使Web应用程序通过宏或其他内容进行查询，但找不到任何可在其中键入SQL语句的地方，这非常令人沮丧.

Most things I find on Google are for client-based Access databases. I tried to make the web app do a query through macros or something, but I can't find anywhere to type in SQL statements, which is very frustrating.

编辑*对于多值查找"，我的术语可能不正确.我绝对不想在单个字段中存储多个值.我想基于多个条件返回一个值.

edit* my terminology may be incorrect regarding 'multivalue lookup'. I definitely do NOT want to store multiple values in a single field. I want to return a single value based on multiple criteria.

推荐答案

如果我正确理解了您的问题，那么在桌面数据库中，您可以将组合框的行源设置为"relate_table"，并以ID作为绑定列，并显示所有三列的位置，类别和单位.

If I understand your question correctly, then in a desktop database, you would make the rowsource for a combobox be 'relating_table', with id as the bound column, and display all three columns location, category, and units.

该解决方案不适用于Web应用程序，因为它们不支持显示多个字段. 如果是这种情况，那么您可以创建一个查询，该查询具有related_table中的id字段，然后创建一个表达式(不幸的是，您无法为Web应用输入SQL，但查询设计器与桌面数据库相同).它将其他字段(例如[位置] +-" + [类别] +-" + [单位])组合起来用作组合的显示字段.

That solution doesn't work in web apps because they don't support displaying multiple fields. If that is the case, then you can create a query (unfortunately you can't type in SQL for web apps, but the query designer is the same as the desktop database otherwise) that has the id field from relating_table, and then an expression which combines the other fields (e.g. [location] + "-" + [category] + "-" + [units]) to use as the display field for a combo.

另一方面，如果您希望人们能够独立选择位置/类别/单位，然后根据related_table中的匹配条目在项目表中设置fk_id，则解决方案会稍微复杂些.

If, on the other hand, you want people to be able to choose location/category/units independently, and then set fk_id in your projects table based on the matching entry in relating_table, the solutions is slightly more complicated.

1)您将添加3个控件，这些控件使用户可以为每个字段指定要查找的值.
2)您创建一个将这些值用作参数的数据宏，在related_table中执行LookupRecord以查找相应的记录(假设有一个记录)，然后将该宏的返回值设置为related_table.id.
3)为控件创建AfterUpdate宏，该宏允许用户指定要从#2调用数据宏的要查找的值，将控件中的值作为参数传入，然后使用来自的返回值设置fk_id字段.宏.

1) You would add 3 controls that allow the user to specify the value to lookup for each field.
2) You create a data macro that takes those values as parameters, does a LookupRecord in relating_table to find the corresponding record (assuming there is one), then set the return value for the macro to relating_table.id.
3) Create an AfterUpdate macro for the controls that allow the user to specify the values to lookup that invokes the data macro from #2, passing in the values from the controls as parameters, and then setting the fk_id field using the return value from the macro.

这篇关于Access 2013 Web App-多值查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！