REPORT FORM order based on another tables values

Question

I have two tables and I need to create a report(.FRX/.FRT) from one of them based on a sorted value in the other.
The tables are as follows
TABLE-KEYS
field->Key_number, C, 10
field->Key_sorted, C, 10

TABLE-ISSUED
field->Key_Number, C, 10
field->Key_Date, D, 8
field->Issue_Notes, C, 25

An example of the data is:
KEYS:
Key_Number: ABC
Key_Sorted: 921
Key_Number: ZYZ
Key_Sorted: 134
Key_Number: FGH
Key_Sorted: 212
Key_Number: GHM
Key_Sorted: 111
Key_Number: TYZ
Key_Sorted: 654
Key_Number: URA
Key_Sorted: 342

ISSUED:
Key_Number: ABC
Key_Date: 10/11/2009
Key_Number: ABC
Key_Date: 10/25/2009
Key_Number: ZYZ
Key_Date: 09/09/2009
Key_Number: GHM
Key_Date: 09/12/2009
Key_Number: FGH
Key_Date: 10/26/2009

There are certain rules for the Key_Number and how it is to be sorted. So instead of trying to sort on a function I created a sort field, and that is the order the KEYS are
displayed within the system.
So the order of the Key_Number for this report, would be displayed in the following order. (Based on the key_sorted field in the KEYS table)
GHM
ZYZ
FGM
ABC

Everything in the system is working fine. But now I need to create a report that is based off the ISSUED table. There is not a 1-to-1 relationship to the KEYS table. There may be multiples of the same key and some KEYS that have not been ISSUED, and therefore not in the ISSUED table. However I do not have the key_sorted field within the ISSUED table.
I need the report to print the following data, in this exact order:

Key_Number: GHM, 09/12/2009
Key_Number: ZYZ, 09/09/2009
Key_Number: FGH, 10/26/09
Key_Number: ABC, 10/11/2009
Key_Number: ABC, 10/25/2009

However if just sorted on the Key_Number it prints in the following order:
Key_Number: ABC, 10/11/2009
Key_Number: ABC, 10/25/2009
Key_Number: FGH, 10/26/09
Key_Number: GHM, 09/12/2009
Key_Number: ZYZ, 09/09/2009

So how can I get the ISSUED table to read in the order of the KEYS table, without adding the Key_Sorted field?

The required report does not include any data from KEYS table, I just want to order the report based off the Key_Sorted field. But, I really don't want to add the Key_Sorted field to the ISSUED table.

I would like to be able to set the data up, the issue a REPORT FORM format. The report is already created

I hope this makes sense.

Is there someway to order the ISSUED table based on the Key_Sorted field within the KEYS table, while having the ISSUED table as the main driving table?

thanks in advance.

Answer 1

YOU CAN GET result as per your requirement from following SQL 

but Key_number required in both table..

SELECT TABLE_ISSUED.* FROM TABLE_ISSUED INNER JOIN TABLE_KEYS ON TABLE_ISSUED.key_number = TABLE_KEYS.key_number ORDER BY TABLE_KEYS.key_number

---

Please "Mark as Answer" if this post answered your question. :)

Kalpesh Chhatrala | Software Developer | Rajkot | India

Kalpesh's Blog

Answer 2

          HELLO:
                  
            SELECT  A.*,B.KAY_SORTED   FROM  ISSUED  A , KEYS  B   WHERE; A.KEY_NUMBER=B.KEY_NUMBER  INTO CURSOR  MYCURSOR   ORDER BY ;  KEY_SORTED,A.KEY_DATE
 
                            REPORT  FORM   MYREPORT   TO PRINT  PREVIEW

---

azizsallam

Answer 3

SELECT  A.*,B.KEY_SORTED  FROM  TABLE_ISSUED  A , TABLE_KEYS  B  WHERE A.KEY_NUMBER=B.KEY_NUMBER  INTO CURSOR  MYCURSOR   ORDER BY KEY_SORTED,A.KEY_DATE

---

Please "Mark as Answer" if this post answered your question. :)

Kalpesh Chhatrala | Software Developer | Rajkot | India

Kalpesh's Blog