Dynamic Sort Order

[agust]

Supposed I have the following tables:

TableMonthlyReport consists of the following fields:
*IDStore
*Year
*Month
*IDCatalog
*IDItem
Quantity

TableGoods:
*IDCatalog
*IDItem
ItemName
IDSupplier

The asterisks indicates the index keys. While TableMonthlyReport has 2 links of relationship with the TableGoods on IDCatalog and IDItem.

I have a data entry front end for monthly report. I have no problem to have the record source for this purpose.

I just need that the sort order of the item (in the front end) in a way that they could be managed differently on different period. For example, in March to April 2007 I want the items shown in the following order: A B C D ... While in May to August 2007 I want the items shown as B D A C ....

I tried to create an order table just like this:
TableGoodsOrder:
*IDCatalog
*IDItem
*ValidStart
ValidEnd
OrderItem

It is linked with the TableGoods on IDCatalog and IDItem.

The first order list (March - April 2007) will have the ValidStart of 703 and ValidEnd of 704. While the next order list will have the ValidStart of 705 and 708. The last 2 digits stand for the month, while the first digit is to indicate year.

The SQL query for the record source works but I can not enter any value. So, the idea of having a special table for sort order is not OK.

Can anybody give me a clue for the solution?

 

[neileg]

You should be sorting in a query, not a table. Tables are inherently unsorted.

 

[agust]

Actually I sort using SQL and I just want to use 'ORDER BY OrderItem' - the problem is the order for each month (or period) might be different time to time. That's whay I use a "help table" to manage these different order lists.