Sorting 1

[karweng]

Hi everyone,

I have been asked to help some users to sort some records from, table A to result, any advise on how to solve this?

Table.A
Catalogue Material Days
45478-2 INLAY BACK 1781
45478-2 INLAY FRONT 1781
SIC-0336 INLAY BACK 1777
SIC-0336 INLAY FRONT 1788
SIC-0365 INLAY FRONT 1794

result A
Catalogue Material Days
SIC-0336 INLAY BACK 1777
SIC-0336 INLAY FRONT 1788
45478-2 INLAY BACK 1781
45478-2 INLAY FRONT 1781
SIC-0365 INLAY FRONT 1794

 

[dhookom]

A that is result is determined how? Are we supposed to guess?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[karweng]

Hi,

Sorry for being so vague, The result.A was manually entered, so i was looking a way to sort from table.A into how result.A

 

[dhookom]

Add a field that you can enter a sorting value.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[karweng]

Hi dhookom,

adding another field is not possible, would it be possible to write a query based on table.A, and get that result?

let me try to explain, one catalogue consist of one or more material.....every material with their own 'day', the 'day' can be different for every material of a catalogue..what the user want is...sort the 'day' from low to high...

if follow the ‘day’ sequence. The record should sorted from 1777,1781,1781,1788,1794…but…the catalogue for 1777 is SIC-0336, then all the SIC-0336 should group together before proceed to the higher number ‘day’

then the next higher ‘day’ is 1781..so 45478-2 will only come after SIC-0336.even though one of the record of SIC-0336 having greater ‘day’ than 45478-2

 

[dhookom]

So you just want to sort the records by the minimum Days value of the same catalogue. You may need to use the very slow DMin() function:

SELECT TableA.*, DMin("DAYS","TableA","Catalogue = """ & [Catalogue] & """") AS SortBy
FROM TableA
ORDER BY DMin("DAYS","TableA","Catalogue = """ & [Catalogue] & """");

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[karweng]

Hi dhookom,

when I tried the query to sort the by the minimum Days of the same catalogue, I'm not getting the exact result. It looks like it is sorting by the maximum Days of the same group.

Catalogue Material Days SortBy
SIC-0336 Inlay Front 1788 1777
SIC-0336 Inlay Back 1777 1777
45478-2 Inlay Front 1781 1781
45478-2 Inlay Back 1781 1781
SIC-0335 Inlay Front 1794 1794

 

[lespaul]

did you add DAYS to the ORDER BY clause?

SELECT TableA.*, DMin("DAYS","TableA","Catalogue = """ & [Catalogue] & """") AS SortBy
FROM TableA
ORDER BY DMin("DAYS","TableA","Catalogue = """ & [Catalogue] & """"), [b]Days[/b];

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[karweng]

Ahh that's it, thanks Lespaul and dhookom.