Sort question - is there a better way?

[TCARPENTER]

Hi All,

I'm going to apologize in advance for this one, as I'm sure it's either been asked and answered before (but I can't seem to find it), or it's just plain basic knowlege.

I have two tables like this:

Part_Table
[ul]
[li]Part_Recnum - Autonumber -PK[/li]
[li]Part_Desc - Text[/li]
[li]...[/li]
[/ul]

PartDetail_Table
[ul]
[li]PartDetail_Recnum - Autonumber[/li]
[li]Part_Recnum - PFK[/li]
[li]Bin_Recnum - PFK[/li]
[li]...[/li]
[/ul]

The Part_Recnum field in PartDetail_Table is set to a combo box in the table with the following properties:
[tt]
Rowsource: SELECT [Part_Recnum], [Part_Desc] FROM Part_Table ORDER BY [Part_Desc]
Bound Column: 1
Column Count: 2
Column Heads: No
Column Widths: 0";1"
[/tt]

User's typically interface with the PartDetail_Table via a subform joined on the Bin_Recnum field, and when writing queries for the form, I have always used an inner join on the Part_Table:

SELECT * FROM Part_Table INNER JOIN PartDetail_Table ON Part_Table.Part_Recnum = PartDetail_Table.Part_Recnum
ORDER BY Part_Table.Part_Desc;

And that's to get the form to sort by Part_Desc (as the descriptions are never entered alphabetically). Is there another way, say by sorting on the second column something like PartDetail_Table.Part_Recnum!Columns(1)? Maybe some other way? Or is this the way it should be done?

TIA
Todd

 

[HarleyQuinn]

If I understand what you mean correctly, how about:

SELECT * FROM Part_Table INNER JOIN PartDetail_Table ON Part_Table.Part_Recnum = PartDetail_Table.Part_Recnum 
ORDER BY PartDetail_Table.Part_Recnum;

That wouold oreder the query by the field Part_Recnum from the table PartDetail_Table.

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[TCARPENTER]

Hi HarleyQuinn,

I had used that in the past, but wound up with complaints about the lists not being alphabetical.

Thanks though.

Todd

 

[HarleyQuinn]

So you don't want to order by another column then???

How about:

SELECT * FROM Part_Table INNER JOIN PartDetail_Table ON Part_Table.Part_Recnum = PartDetail_Table.Part_Recnum 
ORDER BY Part_Table.Part_Desc, PartDetail_Table.Part_Recnum;

That way you will keep the alpha ordering???

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.