MS Access Loop？ [英] MS Access Loop ?
问题描述
大家好，
我有一些订单数据，我需要收集一些信息。
我的日期订单已经打开，关闭，取消等。例如，在开盘和收盘日期之间没有任何关于订单开放的方式。
我需要计算一段时间内每天开立的订单数量。然后是最长和最短的时间。
非常感谢任何想法或帮助。
Hi All,
I have some order data that i need to collect some information.
I have date order opened, closed, cancelled etc.I dont have anything that ways an order was open between the open and close dates for example.
I need to calculate the number of orders open each day for a period. And then max and mins for the period.
Any ideas or assistance much appreciated.
推荐答案
大家好，
我有一些订单数据，我需要收集一些信息。
我有约会对象订单打开，关闭，取消等。例如，在开盘和收盘日期之间没有任何打开订单的方式。
我需要计算一段时间内每天开立的订单数量。然后是最长和最短的时间。
任何想法或帮助非常感谢。
Hi All,
I have some order data that i need to collect some information.
I have date order opened, closed, cancelled etc.I dont have anything that ways an order was open between the open and close dates for example.
I need to calculate the number of orders open each day for a period. And then max and mins for the period.
Any ideas or assistance much appreciated.
请更清楚地解释一下你想要完成什么以及到目前为止你尝试过什么。
做什么你的意思是说你想要计算订单的数量...打开如何以及由谁打开？
另外，请发布您存储订单信息的表格结构。 />
问候，
Scott
Please explain a little more clearly what you are trying to accomplish and what you have tried so far.
What do you mean saying you want to calculate the number of orders open... Open how and by whom?
Also, please post the table structure that you have storing the orders information.
Regards,
Scott
嗨斯科特，
我有下表结构
唯一标识符
日期
Event_Type
客户。
我有活动类型
New_Order
Order_Fill
Order_Edit
Order_Void
Order_Cancel
我要做的是计算每天开的订单总数。我运行了一个按日期分组并计算未结订单的查询。这给了我一个不正确的结果，好像例如Order_new事件在1月1日被记录并且订单在1月10日被填充它只计算1月1日开放的订单。该订单实际上在1月1日至1月10日期间开放了9天。因此，它应该在1月1日至9日期间被视为开放。
我尝试使用新的event_type（order_open）为order_new事件创建新记录日期小于订单填写日期但不成功。我认为循环虽然程序可能有效，但不幸的是我没有技能来编写脚本来生成记录来打包订单表。
希望这会有所帮助。
问候
GoneFishing
Hi Scott,
I have the following table structure
Unique identifier
Date
Event_Type
Customer.
I have event types of
New_Order
Order_Fill
Order_Edit
Order_Void
Order_Cancel
What I am trying to do is calculate the total number of orders open each day. I ran a query that grouped by date and counted open orders. this gave me an incorrect result as if for example the Order_new event was logged on 1 January and the order was filled on 10 January it only counted the order as open on 1 january. The order was in fact open for 9 days between 1 january and 10 january. So it should have been counted as open for the days 1 - 9 january.
I have tried to create a new record with a new event_type (order_open)for each day whilst the order_new event date was less than the order fill date but was unsuccesful. I thought a loop while procedure might work but unfortunately I dont have the skills to write the script to produce the records to poulate the orders table.
Hope this helps.
Regards
GoneFishing
嗨Scott，
我有以下表结构
唯一标识符
日期
Event_Type
客户。
我有活动类型
New_Order
Order_Fill
Order_Edit
Order_Void
Order_Cancel
我要做的是计算每天开的订单总数。我运行了一个按日期分组并计算未结订单的查询。这给了我一个不正确的结果，好像例如Order_new事件在1月1日被记录并且订单在1月10日被填充它只计算1月1日开放的订单。该订单实际上在1月1日至1月10日期间开放了9天。因此，它应该在1月1日至9日期间被视为开放。
我尝试使用新的event_type（order_open）为order_new事件创建新记录日期小于订单填写日期但不成功。我认为循环虽然程序可能有效，但不幸的是我没有技能来编写脚本来生成记录来打包订单表。
希望这会有所帮助。
问候
GoneFishing
Hi Scott,
I have the following table structure
Unique identifier
Date
Event_Type
Customer.
I have event types of
New_Order
Order_Fill
Order_Edit
Order_Void
Order_Cancel
What I am trying to do is calculate the total number of orders open each day. I ran a query that grouped by date and counted open orders. this gave me an incorrect result as if for example the Order_new event was logged on 1 January and the order was filled on 10 January it only counted the order as open on 1 january. The order was in fact open for 9 days between 1 january and 10 january. So it should have been counted as open for the days 1 - 9 january.
I have tried to create a new record with a new event_type (order_open)for each day whilst the order_new event date was less than the order fill date but was unsuccesful. I thought a loop while procedure might work but unfortunately I dont have the skills to write the script to produce the records to poulate the orders table.
Hope this helps.
Regards
GoneFishing
我认为你在表结构上有设计问题......基本上你应该有四个表你给我的数据：
表一;
tblCustomers
CustomerID Autonumber PK
CustomerFirst
CustomerLast
CustomerEtc ...
表二;
tblOrders
OrderID自动编号PK
客户编号FK
订单详细信息
OrderEtc ...
表3;
tblOrderEvents
OrderEventID自动编号PK
OrderID编号FK
EventID编号FK
OrderEventDate日期/时间
表四;
tblEvent
EventID自动编号PK
EventType
您目前正在尝试在没有链接表的情况下尝试在订单和事件之间创建多对多的关系。测试此语句很简单：订单＃1可以有多个EventType ...但是，事件＃1可以与多个订单相关联。这告诉我们这两个主题之间存在多对多的关系，因此需要名为tblOrderEvents的链接表。
然后你将检查任何具有等于New_Order的EventID的OrderID小于或等于任何日期，并且没有等于Order_Close的EventID，或者Order_Close大于或等于任何日期。
我必须暂时退出，但稍后会详细介绍如何完成第二步。
问候，
Scott
I think you have a design issue on the table structure... Essentially you should have four tables with the data that you are giving me:
Table one;
tblCustomers
CustomerID Autonumber PK
CustomerFirst
CustomerLast
CustomerEtc...
Table two;
tblOrders
OrderID Autonumber PK
CustomerID Number FK
OrderDetails
OrderEtc...
Table three;
tblOrderEvents
OrderEventID Autonumber PK
OrderID Number FK
EventID Number FK
OrderEventDate Date/Time
Table four;
tblEvent
EventID Autonumber PK
EventType
You are currently suffering from trying to create a many to many relationship between orders and events without the linking table. Testing this statement is easy: Order #1 can have more than one EventType... However, Event #1 can be associated with more than one Order. That tells us a many to many relationship exists between these two subjects, thus requiring the linking table called tblOrderEvents.
Then you will to check for any OrderID that has an EventID equal to New_Order that is less than or equal to whatever date, and does not have an EventID equal to Order_Close, OR Order_Close is greater than or equal to whatever date.
I have to step out briefly, but will be back in a bit with more detail on how to accomplish the second step.
Regards,
Scott
这篇关于MS Access Loop？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
