与Union Query相反 [英] Opposite of Union Query
问题描述
我在大约一年前看过一篇文章，思考是否可能与联合查询相反，即相交查询。然而，OP关注的是彼此交叉的各种场地。
我有不同的方法。
我有一张记录表。有一个日期字段，一个开始时间字段和一个结束时间字段。 （不是在一起，由于外部编程兼容性问题）我希望能够通过一段时间对记录进行排序。即所有记录在凌晨3点之后的第1天开始，但在第2天下午6点之前完成。
我厌倦了有条款，但我没有聚合功能。我尝试了一个日期>开始时间和开始时间> =开始时间和日期< =结束日期和结束时间< =结束时间。
，如果发生了什么事后发生了在开始日期之后的结束时间，但是在结束日期之前，它不会出现，
说我有这些活动
日期开始时间结束时间
1/1/2000 1:00 PM 1:53 PM
1/1/2000 6:53 PM 7:35 PM <
1/2/200 4:45 PM 5:30 PM
我希望在2000年1月1日中午到下午6:00之间的活动1/2/200，我会错过1/1/200 6:53，因为结束时间是在第二个日期的结束时间之后，但它仍然在我的加入值范围内。
I saw a post about a year ago, that pondered if an opposite to a union query was possible, i.e. an intersect query. however, the OP was concerned about varous fields intersectiong with one another.
i have a different approach.
i have a table of records. there is a date field a start time field and an end time field. (not together, due to outside programming compatability issues) i want to be able to sort through the records through a block of time. i.e. all records that started on day 1 after 3 am, but finished on day 2 before 6 pm.
i tired the having clause, but i don''t have an aggregarate function. i tried a where date>start tate and start time >= start time and date <= end date and endtime <= end time.
hwoever, if if something happened after the end time after the start date, but was before the end date, it won''t show up,
say i have these events
Date Start Time end time
1/1/2000 1:00 PM 1:53 PM
1/1/2000 6:53 PM 7:35 PM
1/2/200 4:45 PM 5:30 PM
and i wanted events between noon on 1/1/2000 and 6:00 PM on 1/2/200, i would miss 1/1/200 6:53 because the end time was after end time for the econd date, but it was still in my range of accecpted values.
推荐答案
首先，我建议您将fieldname Date更改为其他名称，例如RecDate。
接下来你需要确保使用WHERE子句并且在带有时间的日期字段上执行比较，否则Access将使用00:00:00进行...
如果仍然不确定，请在此查询错误结果的描述，以便我们查看。
Nic; o）
First I would like to advise you to change the fieldname Date into another name, e.g. RecDate.
Next you need to make sure to use a WHERE clause and that the comparison is performed on a datefield with a time, else Access will use 00:00:00 for the time...
When still uncertain, post your query with a description of the erroneous results here so we can have a look.
Nic;o)
由于我们没有您想要选择的比较日期和时间，我会使用几个提示字段：
[输入开始日期] /时间]和[输入结束日期/时间]。
我也假设您提到的[日期]字段是日期/时间格式但没有时间部分和[开始时间] ]和[结束时间]是字符串。
As we don''t have the comparison dates and times you want to select from I''ll use a couple of prompt fields :
[Enter Start Date/Time] and [Enter End Date/Time].
I''m also assuming that the [Date] field you mention is in Date/time format but with no time part and that [Start Time] and [End Time] are strings.
我知道我不应该使用日期作为字段名称，但是，这就是需要设置数据库。我更容易用几行VBA来处理日期作为字段名称，而不是更改大量的c代码来修复同样的问题。 （不是我做的，我只是做我告诉的事情）
接下来，这是'我的VBA代码来生成字符串。
i know i shouldn''t use date as a field name, however, that''s the way the database needed to be set up. easier for me to wrie a few lines of VBA to deal with the date as a field name, than to change a lot of c code to fix the same problem. (not my doing, i just do what i''m told)
next, here''s my VBA code to produce the string.
这篇关于与Union Query相反的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
