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

hbl1992

New member
Local time
Today, 22:20
Joined
Nov 18, 2021
Messages
4
DLV_LOTLIST DLV_LOTLIST

IDDELIVERY_CHALLAN_NUMAUCTION_NUMLOT1LOT1_NAMEL1_QTYL1_BAGSL1_SLL1_AMTL1_LTNUMDC_SLNO_1DC_BROKER1DC_ROW_NUM1DC_SLNO_2DC_BROKER2DC_ROW_NUM2
1​
121GOLD BP
216​
8​
1 TO 8
54000​
1​
141​
ab
1​
3​
133GOLD OF
54​
2​
11 TO 12
13500​
3​
141​
ab
2​
143​
pe
1​
6​
126GOLD OF
81​
3​
28 TO 30
20250​
6​
142​
de
1​
143​
pe
2​


DC_FINAL DC_FINAL

IDDC_SLNODC_DATEDC_BROKERDC_EWBDC_ACTN_NODC_LOT1DC_LOT1_NMDC_LOT1_KGSDC_LOT1_BAGSDC_LOT1_SLDC_LOT1_AMTDC_LOT2DC_LOT2_NMDC_LOT2_KGSDC_LOT2_BAGSDC_LOT2_SLDC_LOT2_AMTDC_LOT3DC_LOT3_NMDC_LOT3_KGSDC_LOT3_BAGSDC_LOT3_SLDC_LOT3_AMTDC_LOT4DC_LOT4_NMDC_LOT4_KGSDC_LOT4_BAGSDC_LOT4_SLDC_LOT4_AMTDC_LOT5DC_LOT5_NMDC_LOT5_KGSDC_LOT5_BAGSDC_LOT5_SLDC_LOT5_AMTDC_LOT6DC_LOT6_NMDC_LOT6_KGSDC_LOT6_BAGSDC_LOT6_SLDC_LOT6_AMTDC_TOT_KGSDC_TOT_BAGSDC_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
1637222322919.png


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_112
141​
13
142​
6
and

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)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:50
Joined
May 7, 2009
Messages
19,169
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

New member
Local time
Today, 22:20
Joined
Nov 18, 2021
Messages
4
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>?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:50
Joined
Feb 19, 2002
Messages
42,971
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.
 

hbl1992

New member
Local time
Today, 22:20
Joined
Nov 18, 2021
Messages
4
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)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:50
Joined
Feb 19, 2002
Messages
42,971
What happens if there are more than 6 lots?

There are better ways to do this. I don't use mail merge for this reason. I've attached a sample database that uses OLE automation to export the data to word. Part of the example is to show how to handle "tables" within the document so this is how you would handle the "many-side" data. Take a look and see if you understand how the tables are built. The code includes some commented out lines. You can uncomment a different format, one at a time and that will show you the kind of options you have for formatting. There are lots more. I just picked a few choices.

The sample is simplistic since all the field-bookmark mappings are hard coded. The application that triggered me to build this sample does the mapping differently. It uses tables and is more complex but that means it is much more expansive. The app in production fills about 6,000 documents. All the documents are created by the users. We give them some training so they understand how to make the bookmarks and then they populate the tables of the application to tie a document to its bookmarks and a bookmark to the field t will be filled by.
 

Attachments

  • SampleWordAutomation_20210314.zip
    306.1 KB · Views: 327
  • SampleLettersOnly.zip
    33.7 KB · Views: 333

Users who are viewing this thread

Top Bottom