Sleeper: VBA Code to Sort problem !

[Keith01]

Dear Sirs,
I have the following VBA Code, which I wish to Modify:-

SELECT L.*, R.*
FROM ((SELECT L.ID AS leftRec, R.ID AS rightRec FROM (SELECT A.ID, Count(B.ID) AS rownum
FROM Tbl_Images AS A INNER JOIN Tbl_Images AS B ON A.ID>= B.ID WHERE True GROUP BY A.ID)  AS L LEFT JOIN
(SELECT A.ID, Count(B.ID) AS rownum
FROM Tbl_Images AS A INNER JOIN Tbl_Images AS B ON A.ID>= B.ID WHERE True GROUP BY A.ID)  AS R ON L.rownum+1 = R.rownum
WHERE ((([L].[rownum] Mod 2)=1)))  AS X INNER JOIN Tbl_Images AS L ON X.leftRec = L.ID) INNER JOIN Tbl_Images AS R ON X.rightRec = R.ID;

The Advice I have been given to modify the code is :-
For sorting, you would need to change the join part A.myTablePK>= B.myTablePK in both the green sections to the field you are sorting on - i.e. to A.fieldName>=B.fieldName for ascending and B.fieldName>=A.fieldName for descending.
with regards filtering you will need to apply criteria to both the green parts of the above query and not use the form filter functionality - replace True with whatever your filter requirements are - which is good practice anyway. I've included the WHERE True to hopefully make it easier to for you to edit, it is not actually required in this example

However no matter what combination I try I cannot get either of these modifications to work !

Any Advice would be much appreciated

Best Regards

Keith

[June7]

That is not VBA code, it is SQL.

Should provide sample data and desired output. Can build tables in post or attach file.

[Keith01]

That is not VBA code, it is SQL.

Should provide sample data and desired output. Can build tables in post or attach file.

Thank you very much for your reply June7

I attach a copy of the DB, what i am trying to achieve is to place a command button on the form that displays the form in either "Regn" Order or type "Order", but because of the underlying query this is proving to be very hard

Any advice would be much appreciated

Regards

Keith

[June7]

Query Qry_Regn_Order has a popup input for Tbl_ImagesQuery.Reg1. I cannot find this reference in the query design and the referenced query does not exist. I had to recreate query object.

Why would you have form based on query instead of table?

[Keith01]

Query Qry_Regn_Order has a popup input for Tbl_ImagesQuery.Reg1. I cannot find this reference in the query design and the referenced query does not exist. I had to recreate query object.

Why would you have form based on query instead of table?

Hi jUne07,

The query is to generate two tables for the form ( L & R ), so that i can place two images side by side on the form....

Regards

Keith

[June7]

Okay, got it, should have recognized that.

Check out this query that has same output.

SELECT L.*, R.* FROM Tbl_Images AS L INNER JOIN (SELECT *, ID-1 AS RID FROM Tbl_Images) AS R ON L.ID=R.RID WHERE L.ID Mod 2=1;

Is this a class exercise? Was that advice provided by instructor? Why would you change JOIN clause to change sort order? Any field in query can be used to sort on. Why not sort or filter on form?

I can't see 'green parts' in query.

If you want to have 2 options on form to change sort order, probably need a Toggle button or Radio buttons in an option group or a Combobox, not a single Command button. Which one appeals to you?

[Keith01]

Okay, got it, should have recognized that.

Check out this query that has same output.

SELECT L.*, R.* FROM Tbl_Images AS L INNER JOIN (SELECT *, ID-1 AS RID FROM Tbl_Images) AS R ON L.ID=R.RID WHERE L.ID Mod 2=1;

Is this a class exercise? Was that advice provided by instructor? Why would you change JOIN clause to change sort order? Any field in query can be used to sort on. Why not sort or filter on form?

I can't see 'green parts' in query.

If you want to have 2 options on form to change sort order, probably need a Toggle button or Radio buttons in an option group or a Combobox, not a single Command button. Which one appeals to you?

Good Afternoon June01,

The sql originated from a post on utter access, where my son originally asked how he could present two record's side by side on a form. The Sql was provided by a contributor, and the display side of things works perfectly. However sorting the form in a meaningful manner was impossible ( ie sorted left to right on "Regn" continuously. Here is the code and the reply :-

" you can do it if you can relate the records in some way so you can display both on one row. This example uses two non standard joins and assumes you are using a numeric PK. You will end up with fields from two records in the same row - in this example each field is designated as belonging to L or R tables.

SELECT L.*, R.*
FROM ((SELECT L.myTablePK AS leftRec, R.myTablePK AS rightRec
FROM
(SELECT A.myTablePK, Count(B.myTablePK) AS rownum
FROM myTable AS A INNER JOIN myTable AS B ON A.myTablePK>= B.myTablePK WHERE True GROUP BY A.myTablePK) AS LLEFT JOIN
(SELECT A.myTablePK, Count(B.myTablePK) AS rownum
FROM myTable AS A INNER JOIN myTable AS B ON A.myTablePK>= B.myTablePK WHERE True GROUP BY A.myTablePK) AS RON L.rownum+1 = R.rownum
WHERE ((([L].[rownum] Mod 2)=1))) AS XINNER JOIN myTable AS L ON X.leftRec = L.myTablePK) INNER JOIN myTable AS R ON X.rightRec = R.myTablePK;

the two parts in green are identical and use a non standard join to get a row number.

These are then joined with a non standard join to a) get the odd counted records as the left record PK and the even numbers as the right record PK (being the left count+1) - identified in blue. Note the left join in case there is not a final even number in the list

finally the left and right PK's are joined back to the original table in orange. Note the use of aliasing to keep it simple

This won't be an updateable query and clearly you will need to repeat controls for the left and right sides

all you should need to do is to change myTable to the name of your table and myTablePK to the name of your primary key.

with regards filtering you will need to apply criteria to both the green parts of the above query and not use the form filter functionality - replace True with whatever your filter requirements are - which is good practice anyway. I've included the WHERE True to hopefully make it easier to for you to edit, it is not actually required in this example

For sorting, you would need to change the join part A.myTablePK>= B.myTablePK in both the green sections to the field you are sorting on - i.e. to A.fieldName>=B.fieldName for ascending and B.fieldName>=A.fieldName for descending. "

Hence my referencing the green part of the code (Sorry for the confusion !). I would like a combo box on the form header, to sort by either regn / type or photo location.

we have tried all sorts of combinations replacing the code in green,but nothing seems to work.......

Regards

Keith

[June7]

Well, I do not understand that advice.

What I did:

1. set form RecordSource to my query
SELECT L.*, R.* FROM Tbl_Images AS L INNER JOIN (SELECT *, ID-1 AS RID FROM Tbl_Images) AS R ON L.ID=R.RID WHERE L.ID Mod 2=1;

2. set up a listbox instead of combobox - either can be used
Name: lstSort
RowSource: Regn;Type;Photo_Location
RowSourceType: Value List

3. code in AfterUpdate event
Private Sub lstSort_AfterUpdate()
Me.OrderBy = "L." & Me.lstSort
Me.OrderByOn = True
End Sub

I picked the L fields for sorting on. If you want to allow sort by either L or R fields, modify list and code to accommodate. And if you want to allow DESCENDING as a choice, have another control for user (again, Toggle, Radio, or Combo/List box) and modify code.