访问 2010 年《头脑特工队》
我最近从微软出版社购买了这本书。我目前拥有 Office Enterprise 2007（包括 Access），并坚定地决定将我的 Informix-SQL 应用程序转换为 Access 2010。但是，我对 VBA、宏以及我的应用程序所需的其他几个功能没有经验。这对我来说将是一个新的学习过程，但我必须对我已有 20 年历史的基于字符的应用程序进行现代化改造并利用新功能。我已经开始定义我的表和列，但还没有定义关系。使用 INFORMIX，我将一个串行（自动增量）列与另一个表中的 INT 列连接起来。现在，当我显示客户主行时，我希望在子表单中自动显示与该客户关联的所有事务，并且能够在任一表上添加、更新、查询、删除。 A'10 可以实现这一点吗？
编辑：好的，这就是我到目前为止所做的，定义的表和关系： 
还有更多验证查找表可供遵循，但这些是主要的表。因此，如果现在我创建一个表单并指定 CLIENTES（客户表）、LOTES（批次表）、ARTICULOS（项目表）和 TRANSACCIONES（交易表），它将创建一个 CLIENT 表作为主表单，其他子表作为子表单在一个屏幕上？
另外，我创建批次表的原因是，当客户典当或出售物品时，当铺将所有这些物品分组为一组，计算总贷款或购买金额，将其全部存储在一笔交易中，并打印带有以下描述的票证所有项目和总金额。所以我想说的是，如果客户拖欠利息或不赎回典当，那么客户将没收所有物品，当铺可能会选择将某些物品出售给黄金精炼厂和/或将其他非黄金物品转移到库存中以出售给公众，那么上述 ER 足以满足此功能吗？
我还想确保当用户在特定公司工作时，每个表中的每一行都具有相同的 store_ID（公司 ID），因为该系统将是多公司的，并且会有合并报告等。
I recently acquired this book from Microsoft Press. I currently have Office Enterprise 2007 (Access included) and have firmly decided to convert my Informix-SQL app to Access 2010. However, I'm not experienced with VBA, Macros and several other functionality my app needs. This is going to be a new learning process for me, but I must modernize my 20 year old char-based app and take advantage of new features. I have begun defining my tables and columns, but not the relationships. With INFORMIX, I join a serial (autoincrement) column with an INT column in another table. Now when I display a customers master row, I would like to automatically display all of the transactions associated with that customer in a sub-form and have the ability to add, update, query, delete on either tables. Can this be accomplished with A'10?
EDIT: OK, this is what I have done so far, defined tables and relationships:
there are more validation lookup tables to follow, but these are the main tables. So if now I create a form and specify the CLIENTES (customer table), LOTES (lot table), ARTICULOS (item table) and TRANSACCIONES (transaction table) it will create a CLIENT table as the master form and the other child tables as subforms on one screen?
Also, the reason I created a lot table is because when customers pawn or sell items, the pawnshop groups all these items into one lot, calculates the total loan or purchase amount, stores it all under one transaction and prints the ticket with a description of all the items and total amount. So I want the ability to say, if customer defaults on interest payments or does not redeem pawn, then customer forfeits all items and pawnshop may choose to sell some items to gold refinery and/or transfer other non-gold items to inventory to sell to the public, so would the above ER be adequate for this capability?
I also want to ensure that every row in every table has the same store_ID (company ID) while users are working within a specific company, as this system will be multi-company and there will be consolidated reports, etc.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这种类型的设置可以在可追溯到 1992 年的任何版本的 Access 中完成。
在 Access 中对这些经典的一对多关系进行建模的方式是将父表单基于父表（请注意，我说的是父表，而不是查询 –我将再次重复一遍：您不需要为您连接数据的查询）。然后，您可以根据子表创建所谓的连续表单。现在您有两个表单，然后您可以简单地将子表的表单拖放到上面的父表单中，就完成了。
事实上，如果您在关系窗口中正确设计和设置关系，那么如果您使用向导创建表单，它实际上会为您构建并自动插入一个子表单。
因此，您应该了解有关上述过程的一些有趣问题。正如我上面指出的，您根本不必构建任何 SQL 查询。您不必编写 sql 来将数据连接在一起。 Access 将为您完成所有这些工作，并且无需任何代码。
因此，当您在主表单中导航记录时，子记录将在子表单中自动显示和过滤。 （如果您添加、删除或编辑这些子记录，则会为您插入并维护正确的关系键，同样无需编写任何代码即可完成）。
在下面的表格中，我们有一个经典的会计资金分配示例。在此示例中，我们正在跟踪会员捐赠。因此，表单的顶部是基于主表的一条记录，并且是捐赠事件。然后我创建了一个连续的形式。当放入主窗体时，它就变成了子窗体。该表格位于左侧，它仅允许我输入为上述活动捐款的成员列表。
表格看起来像：
此时，无需编写任何代码，表单即可运行。
其实上面的表格我有一个主要的记录，左边我有很多捐款的会员。但是，出于会计目的，我还需要将左侧成员的每笔捐款分配到特定帐户。 （当今几乎每个会计包中都可以看到经典的支票拆分）
因此，上面的模型是一对多，然后许多成员也将每次捐款分成许多不同的帐户。这是一个非常令人难以置信的强大设置，而且几乎不需要任何代码。
所以，在上面我实际上是在做三个深度表作为模型。 |并且，公平地说，右侧（捐赠分为帐户）确实需要一行代码才能正确更新，因为当您深入 3 个表时，访问不会为我执行此操作。
然而，在大多数情况下，要在访问中对这些经典的父子表关系进行建模，您根本不需要编写任何代码。您使用主表单，然后对于子表，根据子表插入子表单。
如前所述，如果您正确设置了关系，Access 会自动为您将两者缝合在一起，并为您维护关系。因此，属于一条父记录的子记录的显示将自动为您显示。此功能包括您编辑、删除和添加这些子记录。因此，当您导航到新记录时，所有子记录和信息都将自动刷新以用于下一个表单。
换句话说，上述所有操作都可以在不构建任何 SQL 查询的情况下完成，并且不需要一行代码。
This type of setup can be accomplished in any version of access going back to 1992.
The way you model these classic one to many relationships in access is to base the parent form on the parent table (note I said partent table, not a query – I going to repeat this again: you do not need a query that joins the data for you). You then create what is called a continues form based on the child table. You now have two forms, and you then can simple drag + drop in the form for the child table into the above parent form, and you are done.
In fact, if you design and setup the relationship correctly in the relationships window, then if you use a wizard to create the form, it will actually build and automatically insert a sub form for you.
So, there is some several interesting issues about the above process that you should know As I pointed above, you don't have to build any SQL query at all. You don't have to write sql to join together the data. Access will do all of this for you, and do it without any code.
So, when you navigate records in the main form, the child records will be automatically displayed and filtered for you in the sub form. (and if you add or delete or edit those child records, the correct relationship key is inserted and maintained for you, again done without writing any code at all).
In the following form, we have a classic accounting funds distribution example. In this example we are tracking membership donations. So, the top part of the form is one record based on the master table and is the donation event. I then created an continuous form. When dropped into the main form, it becomes a sub form. That form is the one on the left side, and it simply allows me to enter a list of members who donated for the above event.
The form looks like:
At this point the form will work without any code having been written.
In fact the above form I have the one main record, on the left side I have many members who made a donation. However, I also needed to split out each donation for those memebers on the left side to an to particular account for accounting purposes. (a classic check spliting that you see in just about every accounting package these days)
So the above models a one to many and then the many members also split out into many different accounts for each donation. A really incredible powerful setup, and one that has almost no code.
So, in the above I'm really doing three tables deep as a model. |And, to be fair, the right side (donations split into accounts) did need one line of code to update correctly, as access does not do this for me when you go 3 tables deep.
However for the most part, to model these classic parent to child table relationships in access, you don't need to write any code at all. You use a main form and then for the child table, you insert a sub-form based on the child table.
And as noted if you set the relationships up correctly, access will automatically stitch the two together for you, and maintain the relationship for you. So display of the child records belonging to the one parent record will display for you automatically. And this ability includes you to edit and delete and add those child records. And, thus as you navigate to a new reocrd, all of the child records and information will automatically be refreshed for the next form.
In other words all the above can be done without building any SQL queries, and not one line of code is required.
不幸的是，Stack Overflow 非常适合提出问题并获得答案。然而，基于之前问题的一系列问答，我们发现 StackOverflow 在这里真的崩溃了。我会尝试一下，并且考虑到巨大的堆栈溢出，我很想建议您尝试基于论坛的系统，而不是这样。也许完全访问，甚至访问开发论坛：
http://social.msdn .microsoft.com/Forums/en-US/accessdev
无论如何，让我们看看我们能想出什么。我还应该指出，您的表设计以及如何关联这些表并不是真正的访问问题，而只是数据库设计的问题。对于 MySql、Oracle、FoxPro 或实际上我们行业过去 20 多年拥有的任何关系数据库系统，其布局方式都是相同的。因此，当您询问如何设置数据库时，这实际上并不是一个访问问题。
好吧，让我们看看。在上面，您已将 LOTES 表附加到客户。如果您要将 LOTS 表附加（关联）到客户，那么您不需要 LOTES 表中的 store_id，因为该表是该客户记录的子表。任何时候您都可以访问父表来获取该信息，而无需在子表中重复该信息。因此，您要关联的客户记录已经具有商店 id，并且您可以随时获取该商店 id 值，因为它是一个关系（这就是关系数据库的整体思想，您没有一遍又一遍地重复数据）。
而且，正如您现在所看到的，同样的建议也适用于 LOTES 的两个子表。同样，他们不需要商店 id，因为根据您上面的设计，它们已经附加到 LOTES 表，而该表又附加到商店 id 所在的客户表。因此，在您当前的设计中，customer 下面的所有子表都可以并且应该删除 store_id。
然而，我怀疑上述内容并不是您想要在这里完成的。 LOTES 记录很可能属于一家商店。如果是这种情况，那么您希望 LOTES 表成为商店的子表。
因此，您应该有 STORES->LOTES
这里唯一不清楚的部分是您的设计是否允许一名顾客附加到多个商店。如果这是一种非常罕见的情况，那么您可能会采用更简单的设计，要么强制您再次输入客户信息，要么甚至通过一些复制代码。虽然这会破坏这里的标准化，但通常会出现一些问题，例如，如果同一客户的不同商店的送货地址会发生变化，那么在这种情况下，如果不进行标准化，通常会节省大量代码和设计问题。
但是，如果您需要此功能，则反之亦然，并且正确规范化此数据有很多优点，但通常需要预先进行更多设计工作。
如果您确实希望客户拥有多个商店，那么您需要一个名为 tblCustomerStores 的表。您将将此表作为子表附加到customers，并且该表将具有store_id。然后，您可以将所有客户交易等附加到该子存储表。这个客户商店表甚至可能包括支付类型和交付偏好（例如，如果它们因商店而异）。
所以，你开始于
顾客->顾客店铺->交易->交易
并且，不太清楚表事务中的内容，但其他所有内容可能都属于上述事务表下方。
并且，不清楚文章是否附加到特定交易？如果是，那么文章就是交易的子项。
我更多地考虑
客户->客户商店->文章->批次
而你想要客户商店->交易
而且您想要客户商店 -> 文章
因此，我认为您目前的表深度太多了。请记住，您可以将许多表与父表相关联，就像您在图表中一样，但我认为您所拥有的并不是您正在寻找的
Unfortunately Stack Overflow works great for asking a question and having an answer. However, a serious of Q + A that builds on previous question we much find that StackOverflow REALLY breaks down here. I will give this a shot, and with all due respect to the great stack overflow, I am temped to suggest you try a forum based system as opposed to so. Perahps Utter access, or even the access dev forum here:
http://social.msdn.microsoft.com/Forums/en-US/accessdev
Anyway, lets see what we can come up with. I should also point out that your table design and how to relate those tables is not really a access question but simply that of database design. How this should be laid out will be the same for MySql, Oracle, FoxPro or in fact just about ANY relational database system that we had in the last 20+ years in our industry. So, this is not really a access question when you ask about how a database is to be setup.
Ok, lets see. In the above you have the LOTES table attached to customers. If you WERE going to attach (relate) the LOTS table to a customer, then you don't need the store_id in the LOTES tables since the table is a child to that customer record. Anytime you can go up to the parent table to get that information, you don't need to repeat it in the child table(s). So, that customer record you are thus relating back to already has the store id and you be able to get at that store id value any time since it is a relation (that is the whole idea of a relational database, you don't have to repeat data over and over again).
And, as you have it now, the same advice applies to the two child table to LOTES. Again they don't need the store id since by you above design they are already attached to the LOTES table which in turn is attached to the customer table where the store id is. So, in your current design ALL of the child tables below customer can and should and would have the store_id removed.
However, the above I suspect is not what you looking to accomplish here. It is quite likely that LOTES records belong to a store. If that is the case, then you want LOTES table to be child of stores.
You thus should have STORES->LOTES
The only part not clear here is if your design is going to allow one customer to be attached to more then one store. If this is a very rare case, then perhaps you adopt a easier design that either forces you to enter the customer again, or even via some copy code. While this breaks normalizing here often there are some issues like if the shipping address is going to change for different stores for the same customer, then often you save a lot of code and design issues by not normalizing in this case.
However, if you need this feature, then the reverse is also true and there is MANY advantages to normalizing this data correctly, but often more desing work up front.
If you really do want customers to have more then one store, then you need a table called tblCustomerStores. You will attach this table to customers as a child table, and this this table will have the store_id. You can then attached all customer transactions etc. to this child store table. And this Customer store table might even include perhaps payment type and deliver preferences (if they vary from store to store for example).
So, you start at
Customers->customer stores->Transactions->
And, it not quite clear what is in table transactions, but it possible that everthing else belongs below the above trans table.
And, it not clear if Articles are attached to a particular transaction or not? If they are, then articles for thus be a child of transactions.
I thinking more of
Customers->customer stores->Articles->lots
And you want Customers stores->Tranascation
And you want Customers stores->Articles
So, I think you are currently going too many tables deep. Just keep in mind that you can relate many table to the parent table just like you have in your diagram, but I think what you have it not what you are looking for