Modified Date Sort

R

Rusty Ashbaugh

Does anyone know how to design a query that will take information as
presented in Table 1 and get it sorted to Table 2? Basically I want to sort
the part numbers by date, but then I want to take the first occurring part
number and put all of its subsequent occurrences right after it (in date
order). Then the second occurring part number and all of its occurrences and
so on. I have developed a way to do this, but it’s ugly.



Table 1 Table 2

Date Part # Date Part #


1/3/05 A 1/3/05 A

1/8/05 C 2/3/05 A

1/13/05 B 3/3/05 A

2/3/05 A 1/8/05 C

2/8/05 C 2/8/05 C

2/13/05 B 3/8/05 C

3/3/05 A 1/13/05 B

3/8/05 C 2/13/05 B

3/13/05 B 3/13/05 B

Thanks,
Rusty Ashbaugh
 
J

John Vinson

Does anyone know how to design a query that will take information as
presented in Table 1 and get it sorted to Table 2? Basically I want to sort
the part numbers by date, but then I want to take the first occurring part
number and put all of its subsequent occurrences right after it (in date
order). Then the second occurring part number and all of its occurrences and
so on. I have developed a way to do this, but it’s ugly.
Click to expand...

Ummm... major issue here: tables HAVE NO SORT ORDER. Your Table2
should not exist, and (even if it exists) you cannot control the order
in which Access will present its records.

If you want to sort data, just keep the data in your Table1 and create
a Query. Unless I'm really misunderstanding, you can put Ascending as
the sort order on Part Number and (in a column of the query grid to
the right of Part Number) the date field. You can then base a report,
form, or whatever you want directly on this Query.

John W. Vinson[MVP]
 
R

Rusty Ashbaugh

Bad use of terminology on my part. Table 1 is the only table. Ultimately I
want a report that is sorted as in "Figure 2" (not table 2). What I'm trying
to do is build a query (or a sequence of queries) that will get me this
report.
Whichever Part # has the earliest date I want to list it first, but then
with all of its subsequent occurrences in ascending date order. Then I want
the earliest date of the next part # with all of its subsequent occurrences.
If I sort ascending by date, then part #, I'll get:

1/3/05 A What I want is this sort: 1/3/05
A earliest occurrence of A
1/8/05 C
2/3/05 A next occurrence of A
1/13/05 B
3/3/05 A next occurrence of A
2/3/05 A
1/8/05 C earliest occurrence of C
2/8/05 C
2/8/05 C next C
2/13/05 B
3/8/05 C next C
3/3/05 A
1/13/05 B earliest occurrence of B
3/8/05 C
2/13/05 B next B
3/13/05 B
3/13/05 B next B

I want to keep the part #'s together, but I want the groups sorted by the
date of the first occurrence of that part #.
Thanks for your patience.
Rusty Ashbaugh
 
J

John Vinson

Whichever Part # has the earliest date I want to list it first, but then
with all of its subsequent occurrences in ascending date order. Then I want
the earliest date of the next part # with all of its subsequent occurrences.
If I sort ascending by date, then part #, I'll get:
Click to expand...

Ok... you'll need a Subquery for the sorting. If you want it
updateable you'll need to use the (probably slower) DMin() function.
For a report, you should be able to use something like:

SELECT (SELECT Min(T.[datefield]) FROM [tablename] AS T WHERE
T.PartNumber = [tablename].[partnumber]) AS Earliest, PartNumber,
Datefield
ORDER BY Earliest, Datefield;

You may need to fiddle with this query - i.e. you might need to put
the Subquery into the ORDER BY clause rather than the alias Earliest;
you might even need to use

SELECT DMin("[datefield]", "[tablename]", "[PartNumber] = '" &
[PartNumber & "'"), ...

instead of the subquery, using the DMin a second time in the order-by.

John W. Vinson[MVP]
 
Top