如何基于摘要查询创建表单 [英] How to create a form based on Summary Query

问题描述

我正在尝试在我的系统上创建一个可编辑形式的已完成订单，并且遇到了基于此查询创建表单的问题。我需要更新的字段（发票号）是只读的。我理解这样做的原因：


查询从订单表和具有一对多关系的交易表中获取信息。我不想使用任何交易字段，但我确实需要使用基于订单中的交易的查询来过滤表单上显示的订单号。此摘要将告诉我所有已完成的订单（即订购数量 - 交货数量= 0）。


我想基于上述查询创建表单但不能好像这样做。我希望表单的最后字段包含：CustomerID，OrderID，PO_Number，InvoiceNumber - 所有这些都在订单表上，而且没有一个在交易清单上


我了解具有''group by''属性的查询是只读的，但有任何方法 - 例如 - 根据此查询过滤表单结果，或使用VBA解决此问题。


对不起，如果我在这里的解释不是很清楚，但我对访问术语相当新，因此偶尔使用''whatsitcalled''。提前致谢！


任何想法，解决方法？

I''m trying to create an editable form of fulfilled orders on my system and am running into problems with creating a form based on this query. The field that I need to update (invoice number) is read-only. I DO understand the reasons for this:

The query takes information from an orders table and a transactions table which have a one to many relationship. I don''t want to use any of the transaction fields, but I do need to use a query based on the transactions in an order to filter the order numbers displayed on the form. This summary will tell me all of the fulfilled orders (ie when Quantity Ordered - Quantity Delivered = 0).

I want to create a form based on the above Query but can''t seem to do it. I want the final fields of the form to contain: CustomerID, OrderID, PO_Number, InvoiceNumber - all of which are on the orders table, and none of which are on the transactions list

I understand that a query with a ''group by'' property is read only, but is there any way to - for example - filter the form results based on this query, or use VBA to get around this.

Sorry if my explanations here aren''t so clear, but I''m fairly new to the access terminology, hence the occasional ''whatsitcalled'' usage. Thanks in advance!

Any ideas, workarounds?

推荐答案

我认为你自己的建议似乎是最好的主意。您可以使用复杂的，不可更新的GROUP BY查询来过滤直接绑定到表的简单表单。


另一个建议，如果不需要聚合，但只减少输出对于每个分组项目的单个记录，是使用DISTINCT谓词。 Thay可能会更加繁琐，具体取决于你想要做什么。

I think your own suggestion seems to be the best idea. You can use the complex, non-updatable, GROUP BY query to filter a simpler form bound directly to the table.

Another suggestion, if no aggregation is required, but only reducing the output to a single record for each grouped item, is to use the DISTINCT predicate. Thay may be more fiddly, depending on exactly what you''re trying to do.

嗯，不确定我是否在这里正确解释了自己。对于符合条件的订单，交易表上至少需要两个项目。


1 - 订单进入（以及订购数量）

2 - 交货结果（等量出货）。


所以我有一个查询，它会对订单（订单或交货）上的所有相同项目进行汇总，并总计差异。我有一个查询，只包含差异为零的项目（意味着它们已完成）。


我想在此查询的订单上建立一个表单 - 但我很难，因为它基于使用求和的查询 - 即使字段我'使用的内容实际上并未包含在摘要中。


希望这能解决我的问题！


JT

Hmm not sure if if I''ve explained myself correctly here. For an order to qualify as fulfilled, there needs to be a minimum of two items on the transaction table.

1 - the order coming in (and the quantity ordered)
2 - the delivery going out (and an equal quantity going out).

So I have one query that takes a summary of all of the same items on an order (order or delivery) and totals up the difference. I have a query that only includes items which have a difference of zero (meaning they are fulfilled).

I want to base a form on the orders that are on this query - but am having a hard time because it is based on a query that uses summing - even though the fields I''m using are not actually included in the summary.

Hope this clears my question up!

JT

嗯，第二个建议几乎是当然不会对你有任何用处，但你能不能告诉我为什么你认为第一个看起来没什么用处。

Well, the second suggestion is almost certainly not going to be of any use to you, but can you tell me why you believe the first doesn''t appear to be of use.

这篇关于如何基于摘要查询创建表单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！