Transforming Multiple Rows into Single Row in a New Table based on column value in original table

[hbl1992]

DLV_LOTLIST DLV_LOTLIST

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

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

and

Query3

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)

 

[arnelgp]

not very good.
you need to re-structure your table.
otherwise, you'll keep on "fixing" something in the future.
hint: always use meaningful fieldname.

 

[hbl1992]

not very good.
you need to re-structure your table.
otherwise, you'll keep on "fixing" something in the future.
hint: always use meaningful fieldname.

what would you do differently>?

 

[hbl1992]

What is the purpose of the output? I hope you are not planning to keep the data that way. The column names are pretty much indecipherable so it s hard to offer a better suggestion but any time you find yourself using numeric suffixes, STOP immediately. Do not pas GO. Do not collect $200. Whenever you have more than one of something, you have many and many means you need a second table. So your starting table is not normalized. Then you want to make it even worse.

The solution is not a query, although multiple crosstabs joined together might work. I would use VBA. Open a recordset SORTED by how you want the records grouped. Then as long as the grouping value of one record matches the previous, add the columns to the end of the current record. When the new records' grouping field values is different from the previous record's, write the record you are building in your work area and start a new one.

The final result i need is a pdf of the Delivery Challan with the details of upto 6 lots (i intend to do that thru msword mail merge after all the data is in a single row)