MS Access人员简介表 [英] MS Access Person Profile Form
问题描述
好的，我觉得必须有一个更简单的方法来做到这一点，也许我只是没有能力去思考它。我尝试构建查询，我有一个较早的帖子，我尝试使用Dlookup，但后来尝试ConcatRelated，没有什么对我有效。
这里是细分：
期望的结果：
我想要一份表格，我可以提取所有人的个人信息，会员信息和家庭信息。我需要这个表单有两种形式，1可以按名称搜索，可以在需要时修改成员信息，第二种可用作新记录的新表格。
问题：
因为我涉及多个表格，所以我找不到用我想要的视图创建这个表格的方法。我已经以这样的方式创建了数据库，它使用ID而不是名称，但是我希望表单显示名称，并且当它更新时，如果有意义则通过ID更新它。其次，我希望家庭信息显示每个人的个人资料，其家庭信息只显示一次，而不是每个记录重复。
例如：
I我希望在表格上突出显示1个人的所有个人详细信息，然后是另一个包含家庭详细信息的部分，如配偶，家属等，然后是他们的服务详情，例如他们属于哪个部门，他们参加的服务，加入的年份等等。附上预期视图的样本以获得更好的视觉效果。
数据库中的表格：
tblPerson
PersonID（PK）
F_Name
L_Name
DOB
MobileNo
性别
MaritalStatus
国籍
电邮
FamilyID（FK）
类型
ServiceID（FK）
tblFamily
FamilyID（PK）
StreetAddress
城市
州
邮编
HomePhone
tblFamilyRelati onship
FamilyID（PK）也（FK）
PersonID（PK）也（FK）
RelationshipType
tblService
ServiceID（PK）
ServiceName
tblMember
MPersonID（PK）
状态
YearJoined
ShepherdID
tblMinistry_Assignment
MA_PersonID（PK）
MA_MinistryID（PK）
这是否有可能以某种方式将所有内容加入到像每人1个主人档案？或者我是否想要做很多事情并且应该有单独的表格？请提供任何帮助或指导。
**我是MS Access和数据库的新手。但我确实理解了数据库的概念，所以我制作了几个表来重新构建具有大量空值和数据冗余的表。**
谢谢
Ok I feel like there has to be an easier way to do this and maybe I''m just not capable of thinking of it. I tried building queries, I have an earlier post where i tried using a Dlookup but then tried to ConcatRelated and nothing is working for me.
Here''s the breakdown:
Desired Result:
I want 1 form whereby I can pull all of a person''s Personal information, Membership information, and Family information. I need this form to be in 2 forms, 1 that''s searchable by name and modifiable to edit member information when needed and 2nd available as a New form for a new record.
Problem:
Because I have several tables involved I am unable to find a way to create this form with the view I desire. I have created the database in such a way where it uses IDs versus the name however I want the form to show the names, and when it updates, update it by the IDs if that makes sense. Secondly I want the family information to show for each person profile with their family info displayed only once, not repeated for each record.
For Example:
I would like to have 1 person highlighted on the form with all their personal details, then another section with family details such spouse, dependants, etc. and then their service details such what ministry they belong to, what service they attend, year joined, etc. Will attach a sample of expected view for better visual.
Tables in Database:
tblPerson
PersonID (PK)
F_Name
L_Name
DOB
MobileNo
Sex
MaritalStatus
Nationality
Email
FamilyID (FK)
Type
ServiceID (FK)
tblFamily
FamilyID (PK)
StreetAddress
City
State
Zip
HomePhone
tblFamilyRelationship
FamilyID (PK) also (FK)
PersonID (PK) also(FK)
RelationshipType
tblService
ServiceID (PK)
ServiceName
tblMember
MPersonID (PK)
Status
YearJoined
ShepherdID
tblMinistry_Assignment
MA_PersonID (PK)
MA_MinistryID (PK)
Is this even possible to somehow join everything into like 1 master profile per person? Or am I trying to do to much and should have separate forms? Please provide any help or guidance you can.
** I am new to MS Access and Databases in general. I do however understand the concepts of databases so I made several tables to re-frame from having a lot of null values and data redundancy.**
Thank You
推荐答案
我PM''ed你。让我知道你的想法。
I PM''ed you. Let me know your thoughts.
Bri，
我要去看看这个。只是发送一个注释来提升它，所以我不会忘记它。
Bri,
I''m going to take a look a this. Just sending a note to bump it up so I don''t lose sight of it.
我需要这个表格在2表格，1可按名称搜索，可修改以在需要时编辑会员信息，第2表格可用作新记录的新表格。
I need this form to be in 2 forms, 1 that''s searchable by name and modifiable to edit member information when needed and 2nd available as a New form for a new record.
尽量不要绑定数据库的良心。对你想做的事情有一个愿景是很好的。但是，在大多数情况下，您的显示形式也可以作为编辑/新记录表格。
Try not to bind the conscience of the DB. It is good to have a vision of what you want to do. However, in most cases, your display form can also serve as an edit/new record form.
我已经以这样的方式创建了数据库它使用ID与名称，但我希望表单显示名称，并在更新时，如果有意义，请通过ID更新它。
I have created the database in such a way where it uses IDs versus the name however I want the form to show the names, and when it updates, update it by the IDs if that makes sense.
1）不要为使用适当的方法设计数据库而道歉。这里的所有专家都应该知道这样的设计，并鼓励它。
2）我认为你不太熟悉如何创建连接并显示你想要的数据。我们可以使用它。
1) Don''t apologize for designing a database using proper methodology. All the experts here should be aware of such a design and would encourage it.
2) I think you are simply less familiar with how to create joins and display the dat you want. We can work with that.
其次我想要为每个人的个人资料显示家庭信息，他们的家庭信息只显示一次，不会重复记录。
Secondly I want the family information to show for each person profile with their family info displayed only once, not repeated for each record.
非常非常容易。我们将继续努力。
===========================
现在，对于大头钉：
首先，想一下等级。什么是更高层次：人或家庭？应该是家庭，因为一个家庭可以有很多成员。所以，我鼓励你先看看家庭，因为这就是你想要在教堂里聚会的人（通常）。
所以，你的主要观看形式应该基于家庭。你仍然可以搜索一个人，但是当你找到那个人时，你会按照这个人的家人过滤表格。
当你查看一个家庭时，你有与人分开的子表单。您可以通过FamilyID过滤此子表单。我认为不需要tblFamilyRelationship，因为我会创建tblFamilyMemberType（丈夫，妻子，儿子，女儿等），这将成为tblPerson中的FK。
在此子表单中，如果点击一个人，你应该能够查看/编辑该人的详细信息。同时，您应该能够添加一个新人。
此外，我建议删除tblMember，并将该数据合并到tblPerson中。原因是所有这些信息都适用于 每个 人。例如，该人将是以下之一：会员，访客，常规代理人，通讯会员，非交流会员，已故人员，已退出劳斯莱斯等。这也允许您为历史目的维护tblPerson。
部门任务也可以是该人员详细信息的子表格。一个人可能是多个事工领域的成员，所以你需要这种灵活性。
这就是所有谈论的愿景和设计。这里没有详细的解决方案，因为这通常不是我们在这里提供的。这应该让你走上正轨。当他们出现时，我们可以解决重大的挑战和障碍。
Very, very easy to do. We will work on this.
===========================
Now, to the brass tacks:
First, think hierarchically for a moment. What is the higher level: person or family? It should be family, because a family can have many members. So, I would encourage you to look at families first, because this is how you want to group people (usually) in a church.
So, your main viewing form should be based on families. You should still be able to search for a person, but when you find that person, you filter the form by the person''s family.
When you view a family, you have a separate subform with people. You filter this subform by FamilyID. I see no need for tblFamilyRelationship, because I would create tblFamilyMemberType (Husband, Wife, Son, Daughter, etc.) which would then be a FK in tblPerson.
In this subform, if you click on a person, you should be able to view/edit the person''s details. At the same time, you should be able to add a new person.
Additionally, I would recommend eliminating tblMember, and incorporating that data into tblPerson. The reason being that all this information applies to every person. For example, the person would be one of the following: Member, Visitor, Regular Attender, Communing Member, Non-Communing Member, Deceased, Dropped from Rolls, etc. This also allows you to maintain tblPerson for historical purposes.
Ministry assignment could also be a subform on the person''s details. A person could be a member of multiple ministry areas, so you would want that flexibility.
This is all talking vision and design. No detailed solutions here, as that''s typically not what we can provide here. This should get you on the right track. We can work thorugh significant challenges and road blocks as they come up.
这篇关于MS Access人员简介表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
