在查询中排序 [英] Sort in Query
问题描述
亲爱的，
我创建了一个包含大量表单，表格，查询等的会计数据库。我想要一个查询，它给出了以下结果：<在主表信息中有
，有一个名为CREDIT的列和一个名为DEBIT的列。还有一些其他列，其中一列称为ORDER。
当CREDIT列中有一些数字时，DEBIT为NULL，当DEBIT中有一些数字时，CREDIT为NULL。
$在这个ORDER列中的b $ b，有一些数字，从1开始，然后是2，依此类推。此列显示在主表中输入数据的顺序。 （首先输入的项目，ORDER编号小于后面输入的项目。
最后，有一个NUMBER列，每天有一个数字例如，NUMBER 3表示5月2日，NUMBER 4表示5月3日，依此类推。
我希望有一个查询能够提供该表中的项目，首先进行排序使用NUMBER，以便日期实际上是排序的。但是棘手的是我想要每天的CREDIT项目然后是DEBIT项目，所有项目都按ORDER列排序。
任何人都有任何想法？
Dear all,
I have created an accounting database with a lot of forms, tables, queries, etc. I want to have a query that gives me the following results:
in the main table of information, there is a column named CREDIT and one named DEBIT. there are also some other columns, one of which is called ORDER.
When there are some numbers in the CREDIT column, the DEBIT is NULL, and when there are some numbers in DEBIT, the CREDIT is NULL.
in this ORDER column, there are some numbers, starting from 1 and then 2 and so on. this column shows the order of entering data in the main table. (The items that have been entered first, have smaller ORDER numbers than the ones entered after them.
And finally, there is a NUMBER column, that has one number in it for each day. for example NUMBER 3 is for May 2nd, NUMBER 4 is for May 3rd, and so on.
I want to have a query that gives me the items in that table, sorted firstly with NUMBER, so that the days are in fact sorted. But the tricky thing is that I want the CREDIT items in each day then the DEBIT items, all sorted by the ORDER column.
Anyone has any idea?
推荐答案
@ Alireza355
嗨
既然CREDIT和DEBIT都不能为空，那么我建议这样的事情： -
在查询设计器中将此添加到您的字段列表中
Sort_Debi tCredit：IIF（IsNull（DEBIT），1,2）
然后先在NUMBER字段上排序，然后在上面输入的字段中排序第二，最后但在ORDER字段上排序。
希望这就是你想要的？
MTB
@Alireza355
Hi
On the basis that both CREDIT and DEBIT cannot BOTH be null then I suggest something like this:-
In the query designer add this to you field list
Sort_DebitCredit: IIF(IsNull(DEBIT),1,2)
And then sort on the NUMBER field first, then second on the filed entered above and last but not least on the ORDER field.
Hope that is what you wanted?
MTB
排序的第一个字段当然是[NUMBER]，最后是[ORDER]。这些都是直截了当的简单。
要在债务之前处理所有积分，我们必须更有创意。
如果我们断言（[CREDIT]为NULL）然后结果将始终为TRUE或FALSE之一。我们知道TRUE在数值上评估为-1（全1位），FALSE评估为0（全0位），对此断言进行排序必然会将Credits与Debits分开，并确保Credits是第一个。
The first field to sort by would be [NUMBER] of course, and the last [ORDER]. These are straightforward no-brainers.
To handle Credits all coming before the Debits, we have to be a little more creative.
If we assert that ([CREDIT] IS NULL) then the result will always be one of either TRUE or FALSE. As we know that TRUE evaluates numerically to -1 (all 1 bits) and FALSE to 0 (all 0 bits), sorting on this assertion will necessarily separate out the Credits from the Debits, and ensure that Credits are first.
我试过这个，看起来工作正常，但是几天前，我第一次找到了错误排序（有一些学分，一些借记，一天还有一些学分）。
你能否告诉我这有什么问题：
I have tried this, and it looks to work fine, but a few days ago, for the first time ever, I found a mis-sort (there were some credits, some debits, and again some credits in one day).
Can you please tell me what is wrong with this:
这篇关于在查询中排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
