链接多个表替代方案 [英] Linking multiple tables alternatives

问题描述

我一直在努力解决这个问题并尝试了多个解决方案而没有运气。


让我先说吧，我我是Access的新手，我不是在寻找

有人帮助设计我的数据库，只是帮助我指出

正确的方向。 />

我有一个包含8个表的数据库，根据我的阅读，不能在单个表单上链接
，并且可以更新。我创建了一个查询

，其中包括所有8个表，然后基于此

查询创建了一个表单。我到了可以创建新记录的地步，但是可以不用b $ b来编辑现有记录。根据这个小组的建议，我用b $ b尝试使用子表单，但是没有工作，创建了多个查询

并且这些都没有用，所以玩了各种关系，以及

仍然无法正常工作。


这就是我对桌子所拥有的：

tblapplicant

tblarchitect

tblengineer

tblcontractor

tblpermitmain

tblplumbingcontractor

tblmechanicalcontractor

tblproperty

tblinspection


我正在尝试创建一个许可证数据库，我需要一个表单我在哪里
可以输入所有这些信息。问题是，并非所有许可证都会说建筑师，工程师或管道工，但在某些情况下，他们可能会说。我被要求创建一个主要表格（为了便于

使用），员工可以简单地输入建筑师或管道工，或者

工程师的现有许可证的信息，我还没有能够做到这一点。


如果我将上面的所有表格合并到一个或两个表格中，我可以

结束PermitMain表（包含所有实际许可信息）和

说个人表（包括所有申请人，建筑师，工程师，

承包商信息），对于任何给定的

记录，我会有很多空字段，因为每条记录可能并不总是有工程师，建筑师，

或水管工，从我读到的关于规范化的内容来看，这很糟糕

数据库设计。那么最好的方法是什么？


有什么建议吗？我不是在寻找某人帮助设计我的

数据库，只是帮助我指出正确的方向。


谢谢!!

dskillingstad

I''ve been struggling with this problem for some time and have tried
multiple solutions with no luck.

Let me start with, I''m a novice at Access and I''m not looking for
someones help to design my database,just help in getting me pointed in
the right direction.

I have a database with 8 tables, which from what I have read, cannot be
linked on a single form, and be updatable. I have created a query
which includes all 8 tables, and then created a form based on this
query. I was to the point where I could create new records, but could
not edit existing records. Based on suggestions from this group, I
tried using subforms, and that didn''t work, created multiple queries
and that didn''t work, played around with various relationships, and
that still didn''t work.

Here''s what I have for tables:
tblapplicant
tblarchitect
tblengineer
tblcontractor
tblpermitmain
tblplumbingcontractor
tblmechanicalcontractor
tblproperty
tblinspection

I''m trying to create a permit database and I need a single form where I
can input all of this information. The problem is, not all permits
will have say an architect or an engineer, or plumber, but at some
point they may. I''ve been asked to create one main form (for ease of
use) where an employee can simply input the architect, or plumber, or
engineer''s information to an existing permit, and I have not been able
to do this.

If I combine all of the tables above into one or two tables, I could
end up with a PermitMain table (with all of the actual permit info) and
say a Personal table (with all of the applicant, architect, engineer,
contractor info), I will have a lot of empty fields for any given
record, because each record may not always have an engineer, architect,
or plumber, and from what I have read about normalization, this is bad
database design. So what is the best way to do this??

Any suggestions? I''m not looking for someones help to design my
database,just help in getting me pointed in the right direction.

Thanks!!
dskillingstad

推荐答案

看起来非常复杂。


有一个ContractorTypes表： - ContratorTypeID自动编号，ContractorType

文本

承包商表：= ContratorID自动编号，ContractorName文本，

ContractorTypeID长和所有地址和电话等其他细节

许可证表： - PermitID自动编号，PermitDetail文本等等
JoinPermitContractor表PermitID长，ContractorID长使这个

组合键

最后一个表格加入根据需要允许尽可能多的承包商，

并且因为您使用的是组合钥匙，它只允许您为每个许可证使用每个

承包商一次。


建立明显的关系。


主表格有许可证信息，并且有第一个子表单

字段一个组合框，用于选择承包商，您可以根据需要选择尽可能多的或多个b $ b承包商。您还需要第二个表格来输入

承包商的详细信息，并使用一个组合框来选择ContractorType


HTH


Phil

< ds *********** @ gmail.com>在消息中写道

news：11 ********************** @ g14g2000cwa.googlegr oups.com ...

Looks highly complicated.

Have a table of ContractorTypes:- ContratorTypeID AutoNumber, ContractorType
Text
A Table of Contractors:= ContratorID AutoNumber, ContractorName Text,
ContractorTypeID Long and all the other details like address and phone etc
A Table of Permits:- PermitID AutoNumber, PermitDetail Text, Etc
A Table of JoinPermitContractor PermitID Long, ContractorID Long Make this a
combined key
This last table "Joins" the permit to as many of the contractors as needed,
and because you are using a combined key, it will only allow you to use each
contractor once for each permit.

Set up the obvious relationships.

The Main Form has the Permit information, and have a subform with the first
field a comboBox to pick the Contractor where you can pick as many or as few
contractors as you like. You will also need a second form to input the
Contractor''s details and use a comboBox on this to select the ContractorType

HTH

Phil
<ds***********@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

我一直在努力解决这个问题并尝试了多种解决方案而没有运气。

让我先说一下，我是Access的新手，我不是在找某人帮助设计我的数据库，只是帮助我指出正确的方向。

我有一个包含8个表的数据库，从我所读到的，不能在单个表单上链接，并且可以更新。我创建了一个查询
，其中包括所有8个表，然后基于此查询创建了一个表单。我到了可以创建新记录的地步，但是可以不编辑现有记录。根据这个小组的建议，我尝试使用子表单，但是没有工作，创建了多个查询，并且没有工作，玩弄各种关系，并且
那仍然没有用。

这就是我对桌子的看法：
tblapplicant
tblarchitect
tblengineer
tblcontractor
tblpermitmain
tblplumbingcontractor
tblmechanicalcontractor
tblproperty
tblinspection

我正在尝试创建一个许可证数据库，我需要一个单独的表单，我在这里>可以输入所有这些信息。问题是，并非所有许可证都会说建筑师，工程师或水管工，但在某些情况下，他们可能会这样做。我被要求创建一个主要表单（为了便于使用），员工只需将架构师，管道工或工程师的信息输入现有许可证，我就可以还没有能够这样做。

如果我将上面的所有表格合并到一个或两个表格中，我可以最终得到一个PermitMain表格（全部都是实际许可证信息）和
说个人表（包括所有申请人，建筑师，工程师，
承包商信息），我会为任何给定的
记录提供很多空白字段，因为每个记录可能并不总是有工程师，架构师，管道工，而且从我读到的关于规范化的内容来看，这是不好的数据库设计。那么最好的方法是什么？

有什么建议吗？我不是在寻找帮助设计我的数据库的人，只是帮助我指明正确的方向。

谢谢!!
dskillingstad

I''ve been struggling with this problem for some time and have tried
multiple solutions with no luck.

Let me start with, I''m a novice at Access and I''m not looking for
someones help to design my database,just help in getting me pointed in
the right direction.

I have a database with 8 tables, which from what I have read, cannot be
linked on a single form, and be updatable. I have created a query
which includes all 8 tables, and then created a form based on this
query. I was to the point where I could create new records, but could
not edit existing records. Based on suggestions from this group, I
tried using subforms, and that didn''t work, created multiple queries
and that didn''t work, played around with various relationships, and
that still didn''t work.

Here''s what I have for tables:
tblapplicant
tblarchitect
tblengineer
tblcontractor
tblpermitmain
tblplumbingcontractor
tblmechanicalcontractor
tblproperty
tblinspection

I''m trying to create a permit database and I need a single form where I
can input all of this information. The problem is, not all permits
will have say an architect or an engineer, or plumber, but at some
point they may. I''ve been asked to create one main form (for ease of
use) where an employee can simply input the architect, or plumber, or
engineer''s information to an existing permit, and I have not been able
to do this.

If I combine all of the tables above into one or two tables, I could
end up with a PermitMain table (with all of the actual permit info) and
say a Personal table (with all of the applicant, architect, engineer,
contractor info), I will have a lot of empty fields for any given
record, because each record may not always have an engineer, architect,
or plumber, and from what I have read about normalization, this is bad
database design. So what is the best way to do this??

Any suggestions? I''m not looking for someones help to design my
database,just help in getting me pointed in the right direction.

Thanks!!
dskillingstad

垂直而不是水平运行数据并使用查找表。


-

David Hodgkins ，MCSD，MCDBA，MCSE

JSTAR软件解决方案

4402 Sweet Cherry Ln。

Kalamazoo，MI 49004
www.jstarsoftware.com - AutoCompact for Access数据库的主页

269-382- 2931

< ds *********** @ gmail.com>在消息中写道

news：11 ********************** @ g14g2000cwa.googlegr oups.com ...

Run the data vertically instead of horizontally and use lookup tables.

--
David Hodgkins, MCSD, MCDBA, MCSE
JSTAR Software Solutions
4402 Sweet Cherry Ln.
Kalamazoo, MI 49004
www.jstarsoftware.com - Home of AutoCompact for Access Databases
269-382-2931

<ds***********@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

我一直在努力解决这个问题并尝试了多种解决方案而没有运气。

让我先说一下，我是Access的新手，我不是在找某人帮助设计我的数据库，只是帮助我指出正确的方向。

我有一个包含8个表的数据库，从我所读到的，不能在单个表单上链接，并且可以更新。我创建了一个查询
，其中包括所有8个表，然后基于此查询创建了一个表单。我到了可以创建新记录的地步，但是可以不编辑现有记录。根据这个小组的建议，我尝试使用子表单，但是没有工作，创建了多个查询，并且没有工作，玩弄各种关系，并且
那仍然没有用。

这就是我对桌子的看法：
tblapplicant
tblarchitect
tblengineer
tblcontractor
tblpermitmain
tblplumbingcontractor
tblmechanicalcontractor
tblproperty
tblinspection

我正在尝试创建一个许可证数据库，我需要一个单独的表单，我在这里>可以输入所有这些信息。问题是，并非所有许可证都会说建筑师，工程师或水管工，但在某些情况下，他们可能会这样做。我被要求创建一个主要表单（为了便于使用），员工只需将架构师，管道工或工程师的信息输入现有许可证，我就可以还没有能够这样做。

如果我将上面的所有表格合并到一个或两个表格中，我可以最终得到一个PermitMain表格（全部都是实际许可证信息）和
说个人表（包括所有申请人，建筑师，工程师，
承包商信息），我会为任何给定的
记录提供很多空白字段，因为每个记录可能并不总是有工程师，架构师，管道工，而且从我读到的关于规范化的内容来看，这是不好的数据库设计。那么最好的方法是什么？

有什么建议吗？我不是在寻找帮助设计我的数据库的人，只是帮助我指明正确的方向。

谢谢!!
dskillingstad

I''ve been struggling with this problem for some time and have tried
multiple solutions with no luck.

Let me start with, I''m a novice at Access and I''m not looking for
someones help to design my database,just help in getting me pointed in
the right direction.

I have a database with 8 tables, which from what I have read, cannot be
linked on a single form, and be updatable. I have created a query
which includes all 8 tables, and then created a form based on this
query. I was to the point where I could create new records, but could
not edit existing records. Based on suggestions from this group, I
tried using subforms, and that didn''t work, created multiple queries
and that didn''t work, played around with various relationships, and
that still didn''t work.

Here''s what I have for tables:
tblapplicant
tblarchitect
tblengineer
tblcontractor
tblpermitmain
tblplumbingcontractor
tblmechanicalcontractor
tblproperty
tblinspection

I''m trying to create a permit database and I need a single form where I
can input all of this information. The problem is, not all permits
will have say an architect or an engineer, or plumber, but at some
point they may. I''ve been asked to create one main form (for ease of
use) where an employee can simply input the architect, or plumber, or
engineer''s information to an existing permit, and I have not been able
to do this.

If I combine all of the tables above into one or two tables, I could
end up with a PermitMain table (with all of the actual permit info) and
say a Personal table (with all of the applicant, architect, engineer,
contractor info), I will have a lot of empty fields for any given
record, because each record may not always have an engineer, architect,
or plumber, and from what I have read about normalization, this is bad
database design. So what is the best way to do this??

Any suggestions? I''m not looking for someones help to design my
database,just help in getting me pointed in the right direction.

Thanks!!
dskillingstad

听起来像子表格是这里的方式，但你更大的问题

是表格的设计和它们之间的关系。


i不知道你项目的细节，但我可能会跟

一样：


tblContractors（姓名，地址，电话等）

tblPermits

tbllnk_Permits_and_Contractors

tlkpPermitTypes（查询表）

tlkpContractorTypes（可查询）


然后为各种承包商类型分别设置表格

（tblPlumbers，tblElectricians等）仅包含

的细节将一种类型与另一种类型区分开来。我真的不太了解

行业，可以说出这些差异可能是什么，以及你需要跟踪它们中的哪一个。


就子表格而言，在这种情况下你有很多不同的方式可以实现它们，但是一种方法是让你的

主许可证表单上有一个空的子表单控件。在主要的

表格中，放一个组合框，其中包含所有承包商类型的清单

吧。


在更新后中组合框的属性，添加一个函数，

根据组合框中的值更改子窗体。这是非常粗略的，但它看起来像：


函数fnChangeSubform（）

选择Case me.cboContractorType

案例管道工

me.subContractor.sourceobject =" frmContractors_subPlumbers"

案例电工

me.subContractor.sourceobject =" frmContractors_subElectricians"

结束选择

结束功能


链接子域和主域属性子窗体控件

（包含子窗体的主窗体上的shell）为PermitID或者

无论如何。


在我看来，子表单应该只用于捕获

有关之间关系的详细信息。承包商和此

特殊许可证，不用于捕获/编辑

承包商的一般信息（如电话和地址信息）。该信息

应存储在单独的表格中，通过

ContractorID链接到许可证，并在连接到每张桌子的单独表格上进行编辑。


为了更清楚，从数据架构的角度来看，它听起来就像你想要一个链接表一样。将承包商与许可证连接起来。

这个表格至少有两列--PermitID和ContractorID

- 每一列都是外键。来自tblPermits和tblContractors，分别是
，然后可能是一堆其他列来跟踪信息

与承包商和
$ b之间的这个特定连接有关$ b许可。将这个表命名为tbllnkContractors_and_Permits链接。


再次，有很多方法可以给这只猫上皮，但上面的内容至少是值得思考的。 />

如果您再次回复此主题，请详细描述您想要捕获的许可证和承包商的各种类型
$

它会更容易为你的桌子建议一些指示

以及它们之间的关系。


祝你好运！


adm

it sounds like sub-forms are the way to go here, but your bigger issue
is the design of your tables and the relationships between them.

i don''t know the details of your project, but i would probably go with
something like:

tblContractors (for name, address, phone, etc)
tblPermits
tbllnk_Permits_and_Contractors
tlkpPermitTypes (lookup table)
tlkpContractorTypes (lookuptable)

and then have separate tables for each of the various contractor types
(tblPlumbers, tblElectricians, etc.) that contain ONLY the details that
distinguish one type from another. i don''t really know enough about the
industry to say what, if any, these differences might be, and which of
them you need to keep track of.

as far as the subforms go, there are a bunch of different ways you
could implement them in this case, but one way would be to have your
main permit form have an empty subform control on it. also on the main
form, put a combo-box that has a list of all the contractor types in
it.

in the "After Update" property of the combo box, add a function that
changes the subform based on the value in the combo box. this is very
rough, but it would look something like:

function fnChangeSubform()
Select Case me.cboContractorType
Case "Plumber"
me.subContractor.sourceobject = "frmContractors_subPlumbers"
Case "Electrician"
me.subContractor.sourceobject = "frmContractors_subElectricians"
End Select
end function

Link the childfield and masterfield properties on the subform control
(the shell on the main form that holds the subform) to "PermitID" or
whatever.

In my opinion, the subforms should probably only be used for capturing
details about the relationship between the contractor and this
particular permit, NOT for capturing/editing information about the
contractor in general (such as phone and address info). That info
should be stored in separate tables, linked to permits via a
ContractorID and be edited on separate forms connected to each table.

To be more clear, from a data architecture point of view, it sounds
like you want a "linking table" that connects contractors to permits.
This table would have at least two columns -- PermitID and ContractorID
-- each of which is a "foreign key" from tblPermits and tblContractors,
respectively, and then possibly a bunch of other columns to track info
related to this particular connection between the contractor and the
permit. Name this table something link tbllnkContractors_and_Permits.

Again, there are many ways to skin this cat, but the above is at least
food for thought.

If you reply to this thread again, describe in more detail the kinds of
things about the permits and contractors you want to capture and then
it''ll be easier to suggest some directions to consider for your tables
and the relationships b/t them.

Good luck!

adm

这篇关于链接多个表替代方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！