尝试在查询中使用IIF和DlookUp [英] Trying to use IIF with DlookUp in query

问题描述

为数据库编写查询，在查询中它显示一个系列的配置文件。我希望dependents出现在1个字段连接并用逗号分隔。因此，如果家庭中有超过1个依赖项，则它会在列出的1个字段中出现在查询中。


Access的新功能，所以我试图让一次一个工作。


我到目前为止：

家属：IIf（[Is Dependent] = True，DLookUp（" [F_Name]&''''& ; [L_Name]"，[tblPerson]"），''''）


表样本：

人员表

- PersonID（PK）

- F_Name

- L_Name

- DOB

- 电子邮件

- 手机

- 性别

- 依赖

- FamilyID（FK）

等....


家庭表

- FamilyID（PK）

- HeadID

- SpouseID

- 地址

- 城市

- 州

等....

Writing a query for a database where in the query it''s displaying the profile of a family. I want the dependents to appear in 1 field concatenated and separated by commas. So if there is more than 1 dependent in the family it appears in query in 1 field listed.

New to Access so i was trying to get 1 piece to work at a time.

what I have so far:
Dependents: IIf([Is Dependent]=True,DLookUp("[F_Name] & '' '' & [L_Name]","[tblPerson]"),'''')

Table Sample:
Person Table
- PersonID (PK)
- F_Name
- L_Name
- DOB
- Email
- Mobile
- Sex
- Is Dependent
- FamilyID (FK)
etc....

Family Table
- FamilyID (PK)
- HeadID
- SpouseID
- Address
- City
- State
etc....

推荐答案

DLookup（）只能返回一个字段，而不能返回连接值（[F_Name]&''''& [L_Name]）。此外，DLookup（）仅返回与提供的条件匹配的第一条记录。在您的情况下，没有提供任何条件，因此它将返回族表中的第一条记录。所有这一切都表明你不想使用DLookup（）。查看 Allen Brown的连锁相关记录，了解一种方法您正在寻找。


我还建议您更改表格结构。考虑一个孩子长大并结婚的情况。他/她现在是一个家庭的孩子，另一个家庭的户主。在您当前的设置中关系的位置可能会让人感到困惑。你是否将FamilyID（FK）作为父母的家人，然后将personID放在HeadID或SpouseID中？


考虑这样的事情：

DLookup() can only return one field, not a concatenated value ([F_Name] & '' '' & [L_Name]). Also, DLookup() only returns the first record which matches the criteria provided. In your case, no criteria is provided so it will return the first record in the family table. All this to say that you don''t want to use DLookup() for this. Look at Allen Brown''s Concatenate Related Records for a method to do what you are looking for.

I would also recommend changing your table structure. Consider the situation where a child grows older and gets married. He/she is now a child in one family and the head of household in another. It could get confusing where the relationships are in your current setup. Do you leave the FamilyID (FK) to be the parent''s family and then put the personID in the HeadID or SpouseID?

Consider something like this:

展开 | 选择 | Wrap | 行号

感谢您的回复，它有所帮助。我已经使用多对多关系将我的表格改为上面的建议。我还将该功能复制到Access数据库中。我现在还不确定如何使用concatrelated函数将我的dependents放入基于FamilyID的查询中的1个字段。

Thanks for you response, it has been helpful. I have changed my tables to your suggestion above using a Many to Many relationship. I also copied the function into my Access DB. I am still unsure now how to use the concatrelated function to put my dependents into 1 field in the query based on the FamilyID.

我只想连接关系Type = D的字段


我想在查询中显示的结果：


PERSONID 0001

F_NAME John

L_NAME Doe

SPOUSE Jane Doe

ADDRESS 1234无论什么车道

MOBILE [phone]

相关Brian ，莉莉

I only want to concatenate the the fields where relationship Type = D

Example Result I want in Query:

PERSONID 0001
F_NAME John
L_NAME Doe
SPOUSE Jane Doe
ADDRESS 1234 Whatever Lane
MOBILE [phone]
DEPENDANTS Brian, Lily

这篇关于尝试在查询中使用IIF和DlookUp的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！