Dear all,

Hi, the following is the SQL, the fun part comes in the structure that is created:

SELECT * ;
FROM a09tchit tchit ;
WHERE BETWEEN(tchit.chitdate, m.fromdate, m.todate) ;
ORDER BY chitdate, chitbook, chitno ;
INTO CURSOR a09rchit



IF FILE(getcurrcompany() + "\a09bchit.dbf")
SELECT * ;
FROM a09rchit rchit, a09bchit bchit ;
WHERE bchit.docid = rchit.uniqueid ;
ORDER BY rchit.chitdate, rchit.chitbook, rchit.chitno ;
INTO CURSOR a09rchit
ENDIF



IF m.iniwitems
IF FILE(getcurrcompany() + "\a09bchit.dbf")
m.zuid = "rchit.uniqueid_a"
ELSE
m.zuid = "rchit.uniqueid"
ENDIF


SELECT * ;
FROM a09rchit rchit, a09mitem mitem, a09schit schit ;
WHERE schit.docid = &zuid. AND ;
mitem.uniqueid = schit.itemid ;
INTO CURSOR a09qchit


IF FILE(getcurrcompany() + "\a09cchit.dbf")
IF FILE(getcurrcompany() + "\a09bchit.dbf")
m.zuid = "qchit.uniqueid_b"
ELSE
m.zuid = "qchit.uniqueid_c"
ENDIF


SELECT * ;
FROM a09qchit qchit, a09cchit cchit ;
WHERE cchit.docid = &zuid. ;
INTO CURSOR a09qchit
ENDIF
ENDIF


The structure of a09qchit, notice the uniqueid field, uniqueid field is in all of the tables involved:


Structure for database: C:\TRASHCAN\71739858.TMP
Number of data records: 0
Date of last update : 11/10/01
Memo file block size : 33
Code Page : 0
Field Field Name Type Width Dec Index Collate
1 CHITBOOK Character 5
2 CHITNO Numeric 10
3 CHITDATE Date 8
4 STOCKTIME Numeric 4
5 REMARKS_A Memo 10
6 INTERNAL_A Character 1
7 DOCID_A_A Character 9
8 UNIQUEID_A Character 9
9 LASTUPDA_A Character 9
10 REGISTER_A Logical 1
11 YOURFLDN_A Character 10
12 INTERNAL_A Character 1
13 DOCID_B_A Character 9
14 UNIQUEID_A Character 9
15 LASTUPDA_A Character 9
16 REGISTER_A Logical 1
17 DESC_A Character 50
18 PARTNO_A Character 25
19 SUBHEAD_A Numeric 7 2
20 FULLDESC Memo 10
21 TYPE Character 15
22 XREF Character 5
23 OPSTOCK Numeric 12 3
24 UNITCODE Character 3
25 PACKSTOCK Numeric 12 3
26 BSRSTOCK Numeric 12 3
27 LOOSESTOCK Numeric 12 3
28 AUTOISSUE Logical 1
29 UNITCOST Numeric 12 2
30 UNITCOSTPE Numeric 6 2
31 GROSSWEIGH Numeric 12 3
32 NETWEIGHT Numeric 12 3
33 SIZEHEIGHT Numeric 6 2
34 SIZEWIDTH Numeric 6 2
35 SIZELENGTH Numeric 6 2
36 MINLEVEL Numeric 12 3
37 REORDLEVEL Numeric 12 3
38 MAXLEVEL Numeric 12 3
39 LASTSALE Numeric 12 2
40 LASTPURCH Numeric 12 2
41 GRPSTOCK Logical 1
42 GRPDESC Character 50
43 GRPPARTNO Character 25
44 GRPSUBHEAD Numeric 7 2
45 MAINTDEC Logical 1
46 PRINTDEC Numeric 1
47 KEEPRG1 Logical 1
48 RG1ENTRYWI Logical 1
49 KEEPRG23A1 Logical 1
50 KEEPRG23C1 Logical 1
51 STFOLIONO Numeric 6
52 KEEPRT5 Logical 1
53 KEEPRT12 Logical 1
54 KEEPFORM4 Logical 1
55 KEEPB1 Logical 1
56 FRM4NOPROD Logical 1
57 PROD_IN_WT Logical 1
58 KEEPANX1 Logical 1
59 KEEPANX154 Logical 1
60 KEEPMTHA Logical 1
61 CEXABATEME Numeric 6 2
62 TOD Memo 10
63 ROD Memo 10
64 NOTI Memo 10
65 FORCAPTIVE Logical 1
66 TODCAPTIVE Memo 10
67 RODCAPTIVE Memo 10
68 NOTICAPTIV Memo 10
69 FOREXPORT Logical 1
70 TODEXPORT Memo 10
71 RODEXPORT Memo 10
72 NOTIEXPORT Memo 10
73 INTERNAL_B Character 1
74 LVLNO Numeric 6
75 BOMNOS Numeric 4
76 GRPITEMID Character 9
77 OPSTKDETNO Numeric 4
78 OPBASIC Numeric 12 3
79 TOTADD Numeric 15 3
80 TOTLESS Numeric 15 3
81 STARTSTOCK Numeric 15 3
82 HASTRANS Logical 1
83 CURRSTOCK Numeric 15 3
84 TOBEDELETE Character 1
85 NAMEOFCOMM Character 90
86 NATUREINPU Character 15
87 INTERMFOR Numeric 7 2
88 DOCID_A_B Character 9
89 UNIQUEID_B Character 9
90 LASTUPDA_B Character 9
91 REGISTER_B Logical 1
92 DESC_B Character 50
93 PARTNO_B Character 25
94 SUBHEAD_B Numeric 7 2
95 QTY Numeric 12 3
96 PACKLOOSE Character 5
97 PACKDETAIL Character 25
98 EXCISABLE Logical 1
99 REMARKS_B Memo 10
100 INTERNAL_C Character 1
101 FOLIONO Numeric 6
102 ENTRYNO Numeric 6
103 PACKID Character 9
104 PACKQTY Numeric 12 3
105 PACKRATE Numeric 12 2
106 UNITPERPAC Numeric 12 3
107 CHITID Character 9
108 STOCKID Character 9
109 ITEMID Character 9
110 DOCID_B_B Character 9
111 UNIQUEID_C Character 9
112 LASTUPDA_C Character 9
113 REGISTER_C Logical 1
114 YOURFLDN_B Character 10
115 INTERNALS Character 1
116 DOCID Character 9
117 UNIQUEID Character 9
118 LASTUPDATE Character 9
119 REGISTERED Logical 1
** Total ** 1117



Here is the summary of uniqueid field:
8 UNIQUEID_A Character 9
14 UNIQUEID_A Character 9
89 UNIQUEID_B Character 9
111 UNIQUEID_C Character 9
117 UNIQUEID Character 9

What say.