帮助多对多关系设置 [英] Help with many to many relationship setup
问题描述
我的办公室有一个Excel电子表格，其中包含他们想要变成数据库的信息。某些Excel单元格包含许多信息，我需要在Access中单独存储这些信息。所以，我被建议做多对多的关系。我的表格是：
tblMain：包含所有表格中的所有字段。主键是RecNum（自动编号字段）
tblContracts：包含ContractNum，PayeeName，ContractType，PriorityCategory和13个财务字段，用于指示合同的值。 ContractNum是PK。
tblPayees：包含PayeeName和ContractNum。 PayeeName是PK。
tblCountyDetails：包含县（每条记录可以有多个）和ContractNum。县目前是PK。
在设置关系时，我有来自tblMain的无穷大符号并转到每个表。 （由PayeeName链接的tblPayees，由ContractNum列出的tblContracts和由County链接的tblCountyDetails。）我还检查了级联更新相关字段和级联删除相关记录。
我目前在数据库中没有记录，因为我想确保用户可以创建全新的，不存在的记录。
我有一个链接到tblMain的表单。表格上有两个子表格; subContractDetails（在数据表视图中）和subCounties（这是一个连续的形式，是一个字段）。理论上，subCounties将允许用户为每个合同进入多个县。 subContractDetails将允许用户输入该特定合同的每个合同期的财务价值。
我遇到的问题是当我尝试要创建新记录，它不允许我这样做。它一直告诉我，我在tblContracts中没有相关记录。我之前只和一对一的关系工作过。我已经在这里工作了3个小时试图弄清楚我做错了什么。有人可以帮忙吗？我不能那么遥远。
My office has an Excel spreadsheet that contains information they want to turn into a database. Some of the Excel cells contain numerous pieces of information that I''d need to store individually in Access. So, I''ve been advised to do many-to-many relationships. My tables are:
tblMain: contains all of the fields across all of the tables. Primary Key is RecNum (autonumber field)
tblContracts: contains ContractNum, PayeeName, ContractType, PriorityCategory and 13 fiscal fields to indicate values of the contracts. ContractNum is PK.
tblPayees: contains PayeeName and ContractNum. PayeeName is PK.
tblCountyDetails: contains the County (there can be more than one for each record) and ContractNum. County is currently the PK.
In setting up the relationships, I have the infinity sign coming out of tblMain and going to each of the tables. (tblPayees linked by PayeeName, tblContracts listed by ContractNum and tblCountyDetails linked by County.) I also have Cascade Update Related Fields and Cascade Delete Related Records both checked.
I currently have no records in the database because I want to make sure users can create brand new, non-existing records.
I have a form linked to tblMain. There are two subforms on the form; subContractDetails (which is in datasheet view) and subCounties (which is a continuous form and is one field). In theory, subCounties will allow the user to enter in multiple counties for each contract. subContractDetails will allow the user to enter in the financial values for each of the contract periods of that specific contract.
The problem I''m running into is when I try to create new records, it will not allow me to do so. It keeps telling me that I don''t have related records in tblContracts. I''ve only worked with a single one-to-many relationship before. I''ve been at this for 3 hours trying to figure out what I''m doing wrong. Could anyone help? I can''t be that far off.
推荐答案
在我深入讨论这个问题之前，我必须先问几个问题才能更好地理解数据库。
首先，这个数据库是什么？for？如果我们大致了解您要在整体图片中实现的目标，我们可以更好地了解如何设置表格。另外，知道哪些类型的数据应该在哪些表中有用。
其次，你的Payee和CountyDetails表中有ContractNum，这有点令人困惑。 （不应该是），但是如果ContractNum与tblContracts中的相同，那么它不应该相关吗？
第三，tblMain中有地址。这对于主要而言是奇怪的。表格。
第四（再次，如上一篇文章所述），tblContracts拥有FY14-FY25的字段名称 - 原则上，这是一个坏主意，因为随着时间的推移你的表会变得太宽无法处理。然而，可能有办法解决这个问题。
但是，再次，知道这是什么以及你希望如何工作将会有所帮助。
我最初的想法是在我们正确修复结构之前推迟解决你的问题。
Before I get too far into this thread, I must ask a few questions to understand the databse a bit better.
First, what is this database "for"? If we have a general idea of what you are trying to accomplish in the big picture, we can understand better how to set up your tables. Also, knowing what types of data are supposed to be in which tables will be helpful.
Second, You have ContractNum in your Payee and CountyDetails Tables, which is a bit confusing. (Not that it should be), but if ContractNum is the same as that found in tblContracts, shouldn''t it be related?
Third, tblMain has addresses in it. This is odd for a "Main" table.
Fourth (again, as mentioned in a previous post), tblContracts has field names for FY14-FY25--in principle, this is a bad idea, as over time your tables will get too wide to handle. However there may be ways to resolve this.
But again, knowing what this is for and how you want things to work will be helpful.
My initial thought is to hold off on solving your immediate problem until we fix the structure properly.
跟踪我们需要报告的合同信息。例如，我们可能必须报告特定类型（律师）的特定范围（仅限于2015-15财年 - 1819财年的价值）的特定类别（纽约）的所有合同。然后，我需要知道每个收款人的总美元价值，以及符合指定范围的所有合约的总价值。除了FY字段外，一切都将是文本，这将是货币，当然还有自动编号字段。
我在Contractee和CountyDetails表中抛出ContractNum，这样我就可以准确地验证是否正在提取正确的合同信息。收款人可以拥有数十份合约，但ContractNum将是独一无二的。 ContractNum在所有表格中都是相同的值。
我教的方式（这可能就是为什么我在寻求帮助）是tblMain应该是全部捕获 ;包含数据库所需的所有字段的表格。
您是否建议每个参考表的表格？
It''s to keep track of the contracting information that we need to report on. For example, we will likely have to report all contracts of a specific type (Attorney) for a specific range (only values from FY1415-FY1819) that serves a specific county (New York). I would then need to know the total dollar value of each payee, as well as a total value of all the contracts that fit into the range specified. Everything will be Text with the exception of the FY fields, which would be currency and of course, the autonumber fields.
I threw ContractNum in both Payee and CountyDetails tables so that I could accurately verify that the correct contract information is being pulled. A Payee could have dozens of contracts, but the ContractNum would be unique. ContractNum would be the same value throughout all tables.
The way I was taught (which is probably why I''m looking for assistance) is that tblMain should be the "catch all" table that includes all of the fields needed for the database.
Would you suggest a table for each FY?
我同意Twinny，所以等待进一步的信息。
几个小点。发送您的关系视图总是一个好主意，但如果我们能够在tblMain中看到所有字段，那将会很有帮助。我的直觉是数据库远未被标准化，这将使事情变得困难。
使用alpha主键没有任何问题，但这可能会减慢搜索速度。将长号（自动编号）作为主键更好。
Phil
I agree with Twinny, so await further information.
A couple of small points. Sending your relationship view is always a great idea, but it would be helpful if we could see all the fields in tblMain. My gut feeling is that the database is far from being normalised, and that will make things difficult.
There is nothing wrong with having an alpha primary key, but this can slow searches down. Much better to have a long number (AutoNumber) as the primary key.
Phil
这篇关于帮助多对多关系设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
