Sort in Query

[Alireza355]

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?

[MikeTheBike]

@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

[NeoPa]

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.

ORDER BY [NUMBER],
         ([CREDIT] IS NULL),
         [ORDER]

[Alireza355]

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:

ORDER BY MainTable.Number, MainTable.Credit>0, MainTable.Order

Thanx

[Alireza355]

I'm sorry, I forgot to take a look back into my database basics.

Because I needed to do some calculations here, I have put some update queries there to replace the nulls with zeros.

That's why I used >0 there.

using Is Not Null seems to be more effective, but my problem is that I can not use my calculations when one of the fields is null

for example: credit - debit results in null if credit or debit is null.

Now what?!?

[NeoPa]

You're confusing me here. You seem to be saying the nulls have been replaced by zeroes, then you ask me how to handle the nulls :S

First you need to sort out if you are using nulls, zeroes, or a mixture of both.

The fundamental answer to your question though, is to use the Nz() function where necessary. This will replace null values on the fly with zeroes (or any other value you supply).

[NeoPa]

@Alireza355
As any Null value would cause the assertion to fail just as a zero value would, I cannot say without seeing the data. I would certainly expect that to work.

Essentially, if a null is found in a numeric assertion it resolves to FALSE.

(Null>0) == (Null<0) == (Null=0) == FALSE

[MikeTheBike]

Hi

By adding a nested IIF() to my previous post (which seems to have been ignored - nothing new here then!) you can cater for all posibilities ie.

ORDER BY [NUMBERS],  IIf(IsNull([DEBIT]),IIf(IsNull([CREDIT]),3,1),2), [ORDER]

At least I think that is what you want??

Or is this method not considered good practice ?

MTB

[NeoPa]

@MikeTheBike
It wasn't my intention to ignore your post Mike. I was simply putting forward an alternative with an explanation.
@MikeTheBike
I wouldn't presume to criticise your code, but I generally recommend that code not be added which essentially adds nothing (as you're asking).

Your latest code makes perfect sense, as it results in three possible values (even if logically either [CREDIT] or [DEBIT] should be true but never both). We all know that data stored in this (non-normalised) way is prone to allowing illogical values though.

When a field already has only two possible states, it seems redundant to me to wrap a function call around it to produce two different states, before sorting on it. It's not wrong. It's simply redundant. Such a minor point though, that I wouldn't post just for that. As I was posting anyway though, I did it the way I was happier with.

[MikeTheBike]

@NeoPa
I have to say I can only agree with all of that.

Cheers

[NeoPa]

@MTB :)
@Alireza355
I noticed this earlier but forgot to respond. Got caught up in other stuff.

You are absolutely correct. My code should have read :

ORDER BY [NUMBER],
         ([CREDIT] IS NOT NULL),
         [ORDER]

Ali, I suspect Mike had the right idea guessing that the reason you had mis-sorts was due to some incorrect values in your data. This would be an illustration of the GIGO law, and can only occur because you have separate fields for [CREDIT] and [DEBIT]. This allows the possibility of an item being both a credit and a debit. Illogical, but only possible because your record design is incorrect.

If you had a single field that contained different values depending on whether the record is a credit or a debit, then not only would this problem go away, but the more logical structure would also mean you would never have needed to post this question in the first place.

I hope this makes sense.

[Alireza355]

Thanks everyone for your kind support.

About the possibility of a record, having a number in both CREDIT and DEBIT fields, I have made some strict supervising codes and rules in my main form that the user enters data in. IMPOSSIBLE


And the main thing I was missing was using "nz" for my calculations.

Then I used the good and perfectly-working code:

ORDER BY [NUMBER],
         ([CREDIT] IS NOT NULL),
         [ORDER]

Which is now working perfect.

Thank you all for your kind support and your perfect clues.

[NeoPa]

@Alireza355
This may well be true, but if I had a pound for every time someone thought that until I showed them the records that didn't fit, then I'd be a wealthy man (well, a little richer than I am at least).

Seriously, proper design of the data structure is so much easier in the long run. In this case, the protection via the main form is very important, so you should be reasonably safe.
@Alireza355
Very pleased to hear it Ali.

Thank you for taking the time to respond and thank the participants.