How to arrange order of records

[neilsolaris]

Hi,



I've got a tricky to explain problem, so I'll try to explain with an example. Basically, I have a payments table, with various payment types (fee, porterage, travel, early start etc). What I want to do eventually, is to create separate remittance advice reports for each supplier, detailing the fee for each payment type. However, I want the fee payment type to appear first.

When I enter the data to the payments table, I always enter the fee field first (in this case, it's number 1, which is connected to the payment type table). However, when I run a query on the payments table, the payment types don't seem to be in any discernible order. Apart from putting it in alphabetical order, which wouldn't help anyway, I'm not sure how to arrange it so that the fee payment type is at the top. I'm using Access 2007 if that's relevant.

Edit: I just did some more investigating, I think actually the query is displaying the payment types in reverse order, to which I entered them. Is there an easy solution to this?

Many thanks for any help.

[Bob Fitz]

Can you show us the query SQL statement

[Welshgasman]

You just sort on whatever fields you want?

[orange]

Show us a screen capture or 2.
What are your payment types--show us some sample data.

Better to start with a description of your business process in plain, simple English.
Don't jump into physical database(access) until you have your basic info organized.

[neilsolaris]

Can you show us the query SQL statement

Hi Bob, Here's the SQL from the query table. Thanks in advance.

SELECT T_Engagements.EngagementsText_PK, T_MusiciansDetails.First_Name, T_MusiciansDetails.Surname, T_Engagements.Description, [T-PaymentTypes].PaymentType, T_Payments.Amount
FROM [T-PaymentTypes] INNER JOIN (T_MusiciansDetails INNER JOIN (T_Engagements INNER JOIN T_Payments ON T_Engagements.EngagementsText_PK = T_Payments.EngagementsText_FK) ON T_MusiciansDetails.PlayerCode_PK = T_Payments.PlayerCode_FK) ON [T-PaymentTypes].PaymentTypeID_PK = T_Payments.PaymentTypeID_FK
ORDER BY T_Engagements.EngagementsText_PK, T_MusiciansDetails.Surname;

[neilsolaris]

You just sort on whatever fields you want?

I'm not sure how. It gives me three sorting options as far as I can see. Ascending, descending or not sorted. None of these help me though.

[neilsolaris]

Show us a screen capture or 2.
What are your payment types--show us some sample data.

Better to start with a description of your business process in plain, simple English.
Don't jump into physical database(access) until you have your basic info organized.

Thanks. I'll wait for Bob to check my SQL first, to see if he's able to solve it. I have a lot of sensitive information on my database, so it'll take me a while to edit it before I can take any screen captures.

[Welshgasman]

I'm not sure how. It gives me three sorting options as far as I can see. Ascending, descending or not sorted. None of these help me though.

Well you are currently sorting on Surname Ascending?
Why can you not do that with Payment_Type

[neilsolaris]

Well you are currently sorting on Surname Ascending?
Why can you not do that with Payment_Type

I can sort payment type alphabetically ascending or descending, but that doesn't resolve the problem (I just want the "fee" payment type to appear first).

[neilsolaris]

I found this code on a web search (I believe it reverses the order of the query records, but I could be wrong). I don't understand SQL, so I wouldn't know how to incorporate it into mine.

SELECT q.*
FROM (SELECT TOP 3 *
FROM table
ORDER BY id DESC) q
ORDER BY q.id ASC

[Welshgasman]

I can sort payment type alphabetically ascending or descending, but that doesn't resolve the problem (I just want the "fee" payment type to appear first).

That is what I meant? the fee payment type?
You can use the ID of the type (you said 1) and not the description.?

[neilsolaris]

That is what I meant? the fee payment type?
You can use the ID of the type (you said 1) and not the description.?

I see, that works! Thanks a lot for that.

[neilsolaris]

I have three other fee types that have higher numbers in the payment type table. Therefore, I would still be interested if anyone was able to show me how to reverse the order to what I typed in the table, using SQL or something else.

Or the other option, maybe better, would be to rearrange my payment types table, to have all the fee payment types occupying numbers 1 to 4. I'm not sure how to go about this, now that it's already set up.

[Welshgasman]

The order you type them in should not matter.?
YOU decide the order to present them whether it is in a query or report.?

You might have a case where you want an account to be sorted first, then payment types, so you would see

Account Debit
Account Debit
Account Credit
Account Refund

where Debit is 1, Credit is 2 and Refund is 4?

[neilsolaris]

The order you type them in should not matter.?
YOU decide the order to present them whether it is in a query or report.?

You might have a case where you want an account to be sorted first, then payment types, so you would see

Account Debit
Account Debit
Account Credit
Account Refund

where Debit is 1, Credit is 2 and Refund is 4?

I think I understand, but I'm not 100% sure!

Basically, in my example, in the payment type table I have 6 fee payment types. The ID and payment types are as follows. 1 - fee, 15 - miming fee, 17 - session fee, 20 - library recording fee, 21 - BPI recording fee, 22 - management fee

All the numbers 1 to 23 are populated with payment types, so all the other numbers are not fees. So unless the payment type is simply "fee", all the other fee payment types are unlikely to appear at the beginning of the list of payment types.

If I could get all my fee payment types to occupy numbers 1 to 6 then I would have full control over it.

Am I misunderstanding you though?