DLV_LOTLIST DLV_LOTLIST
DC_FINAL DC_FINAL
so i have DLV_LOTLIST which i generated running a few queries from my original list, with these fields to be input by me in this table
the problem:
the DC_SLNO_1 has a delivery challan sl no (upto 6 records to have the same number) and DC_ROW_NUM1 has the row number of the delivery challan (1 to 6)
now, i want the DC_FINAL table filled with data from DLV_LOTLIST , with all the records of the same delivery challan (upto 6) being merged into a single record in the new DC_FINAL table , being filled in the respective columns as per the row number in DC_ROW_NUM1 column.
what i've done so far:
QRY_ULTMT
resulting in
QRY_ULTMT QRY_ULTMT
and
Query3
resulting in data being appended into DC_FINAL
now, I need your help... please help me with this.
PS. there is also DC_SL_NUM2 in the DLV_LOTLIST table which is to have a similar function as that of DC_SL_NUM1, (basically one lot can have two challans)
ID | DELIVERY_CHALLAN_NUM | AUCTION_NUM | LOT1 | LOT1_NAME | L1_QTY | L1_BAGS | L1_SL | L1_AMT | L1_LTNUM | DC_SLNO_1 | DC_BROKER1 | DC_ROW_NUM1 | DC_SLNO_2 | DC_BROKER2 | DC_ROW_NUM2 |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | 12 | 1 | GOLD BP | 216 | 8 | 1 TO 8 | 54000 | 1 | 141 | ab | 1 | | | ||
3 | 13 | 3 | GOLD OF | 54 | 2 | 11 TO 12 | 13500 | 3 | 141 | ab | 2 | 143 | pe | 1 | |
6 | 12 | 6 | GOLD OF | 81 | 3 | 28 TO 30 | 20250 | 6 | 142 | de | 1 | 143 | pe | 2 |
DC_FINAL DC_FINAL
ID | DC_SLNO | DC_DATE | DC_BROKER | DC_EWB | DC_ACTN_NO | DC_LOT1 | DC_LOT1_NM | DC_LOT1_KGS | DC_LOT1_BAGS | DC_LOT1_SL | DC_LOT1_AMT | DC_LOT2 | DC_LOT2_NM | DC_LOT2_KGS | DC_LOT2_BAGS | DC_LOT2_SL | DC_LOT2_AMT | DC_LOT3 | DC_LOT3_NM | DC_LOT3_KGS | DC_LOT3_BAGS | DC_LOT3_SL | DC_LOT3_AMT | DC_LOT4 | DC_LOT4_NM | DC_LOT4_KGS | DC_LOT4_BAGS | DC_LOT4_SL | DC_LOT4_AMT | DC_LOT5 | DC_LOT5_NM | DC_LOT5_KGS | DC_LOT5_BAGS | DC_LOT5_SL | DC_LOT5_AMT | DC_LOT6 | DC_LOT6_NM | DC_LOT6_KGS | DC_LOT6_BAGS | DC_LOT6_SL | DC_LOT6_AMT | DC_TOT_KGS | DC_TOT_BAGS | DC_TOT_AMT |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | 141 | | | 1 | | | | 3 | | | | | | | | | | | | | | | | | | | ||||||||||||||||||
2 | 142 | | | 6 | | | | | | | | | | | | | | | | | | | | | |
so i have DLV_LOTLIST which i generated running a few queries from my original list, with these fields to be input by me in this table
the problem:
the DC_SLNO_1 has a delivery challan sl no (upto 6 records to have the same number) and DC_ROW_NUM1 has the row number of the delivery challan (1 to 6)
now, i want the DC_FINAL table filled with data from DLV_LOTLIST , with all the records of the same delivery challan (upto 6) being merged into a single record in the new DC_FINAL table , being filled in the respective columns as per the row number in DC_ROW_NUM1 column.
what i've done so far:
QRY_ULTMT
Code:
TRANSFORM First([DLV_LOTLIST.LOT1]) AS Chln
SELECT First(DLV_LOTLIST.DC_SLNO_1) AS FirstOfDC_SLNO_1
FROM DLV_LOTLIST
GROUP BY DLV_LOTLIST.DC_SLNO_1
PIVOT DLV_LOTLIST.DC_ROW_NUM1;
resulting in
QRY_ULTMT QRY_ULTMT
FirstOfDC_SLNO_1 | 1 | 2 |
---|---|---|
141 | 1 | 3 |
142 | 6 |
Query3
Code:
INSERT INTO DC_FINAL ( DC_SLNO, DC_LOT1, DC_LOT2 )
SELECT QRY_ULTMT.FirstOfDC_SLNO_1, QRY_ULTMT.[1], QRY_ULTMT.[2]
FROM QRY_ULTMT, DLV_LOTLIST
WHERE (((QRY_ULTMT.[1])=[DLV_LOTLIST].[LOT1]));
resulting in data being appended into DC_FINAL
now, I need your help... please help me with this.
PS. there is also DC_SL_NUM2 in the DLV_LOTLIST table which is to have a similar function as that of DC_SL_NUM1, (basically one lot can have two challans)