Creating Recordset data per job 1

[Swi]

The actual the data is in an Access database and I want to create a recordset through ADO using VB6. How do I write a SQL statement to accomplish the following?

Here is the data in a CSV format:

JobLoc,AE,JobNum,Client,StatDt,DeptCode,ActCDAct,PieceIDTapeDPInfo,PieceSizeProg,SkedMadeYNR,QtyOrdNameCtProdCt,QtyRecQtyProd,RecDateProdDate,BalDue,ArtTpDate,BLOSODate,BLADtNPRTDt,FrmsdtdoneNPRTDt,VendorPressLaserJet,Comment,SR,OrgDropDate,1stSchedDropDt,ActualDropDt,History,OrgRevdCalendar
U,7AN,22011.01HD,Home Depot - Burbank,12/14/2005,DP,LSso,h-Postcard-cutsht,"4-up 12-1/2 x 18"" 9pt C1S",Y,20074,,,-20074,,12/14,12/14,,Color - Duplex,,7Mlnw,12/21,,,,
U,7AN,22011.01HD,Home Depot - Burbank,12/14/2005,DP,LSso,t-TAPES,12/12 DP,,20074,,,-20074,here 12/12,12/14,12/14,,,,7Mlnw,12/21,,,,
U,7AN,22010.02HD,Home Depot - Monroe,12/13/2005,DO,NPRT,h-Postcard-ekta,,Y,20086,,,-20086,,SOout 12/8,NPRT 12/8,appr'd 12/8,EKTA,changed to ekta 12/9,7Mlnw,12/8,,12/?,,
U,7AN,22010.02HD,Home Depot - Monroe,12/13/2005,DO,NPRT,t-TAPES,11/29 SR,,20086,,,-20086,here 11/29,SOout 12/8,NPRT 12/8,,,,7Mlnw,12/8,,12/?,,
U,7AN,22010.01HD,Home Depot - Bristol,12/13/2005,DO,NPRT,h-Postcard-ekta,,Y,20087,,,-20087,,SOout 12/5,NPRT 12/6,appr'd 12/6,EKTA,changed to ekta 12/9,7Mlnw,12/8,,12/?,,
U,7AN,22010.01HD,Home Depot - Bristol,12/13/2005,DO,NPRT,t-TAPES,11/29 HM,,20087,,,-20087,here 11/29,SOout 12/5,NPRT 12/6,,,,7Mlnw,12/8,,12/?,,
U,7MO,22006.01FM,Fannie Mae,12/13/2005,MS,DRdc,f-Brochure-cutsht Static copy,"2-up 12-1/2 x 18"" 80# satin text",Y,1055,1055,12/12/2005,0,,SOout 12/5,NPRT 12/5,"appr'd 12/5,done 12/12",Color - Duplex, Samples to Jason Jeremiah,7Mlnw,Ship 12/7,,Ship 12/?,,
U,7MO,22006.01FM,Fannie Mae,12/13/2005,MS,DRdc,m-ART 3 Kits,11/23 HM,,1055,,,-1055,art here 11/23,SOout 12/5,NPRT 12/5,,,,7Mlnw,Ship 12/7,,Ship 12/?,,
U,7MO,22005.02CN,Care Net,12/14/2005,MS,DRdc,a-OSE-ekta,,Y,3258,,,-3258,,SOout 12/9,PCut 12/12,done 12/12,EKTA,,7Mlnw,12/15,,,,
U,7MO,22005.02CN,Care Net,12/14/2005,DO,NPRT,h-Christmas Card static copy,"2-up 18"" 9pt C1S",Y,3258,,,-3258,,SOout 12/9,NPRT 12/12,appr'd 12/12,Color - Duplex,,7Mlnw,12/15,,,,
U,7MO,22005.02CN,Care Net,12/14/2005,DO,NPRT,t-TAPES 3 Kits,12/6 HM,,3258,,,-3258,here 12/7,SOout 12/9,NPRT 12/12,,,,7Mlnw,12/15,,,,
U,7MO,22005.01CN,Care Net,12/13/2005,DO,NPRT,h-Christmas Card static copy,"2-up 18"" 9pt C1S",Y,860,,,-860,,SOout 12/7,NPRT 12/12,appr'd 12/12,Color - Duplex,,7Mlnw,12/9,,Ship 12?,,
U,7MO,22005.01CN,Care Net,12/13/2005,DO,NPRT,t-TAPES 2 Kits,12/6 HM,,860,,,-860,here 12/6,SOout 12/7,NPRT 12/12,,,,7Mlnw,12/9,,Ship 12?,,

Here is the output I desire:

22011.01HD,Home Depot - Burbank
12/14/2005,DP,LSso,h-Postcard-cutsht,"4-up 12-1/2 x 18"" 9pt C1S",Y,20074,,,-20074,,12/14,12/14,,Color - Duplex,,7Mlnw,12/21,,,,
12/14/2005,DP,LSso,t-TAPES,12/12 DP,,20074,,,-20074,here 12/12,12/14,12/14,,,,7Mlnw,12/21,,,,
22010.02HD,Home Depot - Monroe
12/13/2005,DO,NPRT,h-Postcard-ekta,,Y,20086,,,-20086,,SOout 12/8,NPRT 12/8,appr'd 12/8,EKTA,changed to ekta 12/9,7Mlnw,12/8,,12/?,,
12/13/2005,DO,NPRT,t-TAPES,11/29 SR,,20086,,,-20086,here 11/29,SOout 12/8,NPRT 12/8,,,,7Mlnw,12/8,,12/?,,
22010.01HD,Home Depot - Bristol
12/13/2005,DO,NPRT,h-Postcard-ekta,,Y,20087,,,-20087,,SOout 12/5,NPRT 12/6,appr'd 12/6,EKTA,changed to ekta 12/9,7Mlnw,12/8,,12/?,,
12/13/2005,DO,NPRT,t-TAPES,11/29 HM,,20087,,,-20087,here 11/29,SOout 12/5,NPRT 12/6,,,,7Mlnw,12/8,,12/?,,
22006.01FM,Fannie Mae
12/13/2005,MS,DRdc,f-Brochure-cutsht Static copy,"2-up 12-1/2 x 18"" 80# satin text",Y,1055,1055,12/12/2005,0,,SOout 12/5,NPRT 12/5,"appr'd 12/5,done 12/12",Color - Duplex, Samples to Jason Jeremiah,7Mlnw,Ship 12/7,,Ship 12/?,,
12/13/2005,MS,DRdc,m-ART 3 Kits,11/23 HM,,1055,,,-1055,art here 11/23,SOout 12/5,NPRT 12/5,,,,7Mlnw,Ship 12/7,,Ship 12/?,,
22005.02CN,Care Net
12/14/2005,MS,DRdc,a-OSE-ekta,,Y,3258,,,-3258,,SOout 12/9,PCut 12/12,done 12/12,EKTA,,7Mlnw,12/15,,,,
12/14/2005,DO,NPRT,h-Christmas Card static copy,"2-up 18"" 9pt C1S",Y,3258,,,-3258,,SOout 12/9,NPRT 12/12,appr'd 12/12,Color - Duplex,,7Mlnw,12/15,,,,
12/14/2005,DO,NPRT,t-TAPES 3 Kits,12/6 HM,,3258,,,-3258,here 12/7,SOout 12/9,NPRT 12/12,,,,7Mlnw,12/15,,,,
22005.01CN,Care Net
12/13/2005,DO,NPRT,h-Christmas Card static copy,"2-up 18"" 9pt C1S",Y,860,,,-860,,SOout 12/7,NPRT 12/12,appr'd 12/12,Color - Duplex,,7Mlnw,12/9,,Ship 12?,,
12/13/2005,DO,NPRT,t-TAPES 2 Kits,12/6 HM,,860,,,-860,here 12/6,SOout 12/7,NPRT 12/12,,,,7Mlnw,12/9,,Ship 12?,,

Swi

 

[gmmastros]

This is the third forum (that I noticed) where you posted the same question.

I created a single query for you, but it requires that you have an indentity column (in Access, it's called an AutoIncrement column).

Here's what I did.

1. I copy/pasted your original data to a notepad window.
2. Saved it to C:\TekTipsData.csv
3. Opened Access and created a new database
4. Clicked File -> Import
5. I selected the file I saved it to
6. Chose Comma delimited
7. Said first row contains column headers
8. Allow Access to Add a primary key

Step 8 created a unique identifier for the records. This is crucial because my method for selecting the records depends on it. It created an AutoIncrement field named ID

I then fiddled with stuff to get the output correct (at least I think it is). Here's the query I ended up with.

Select TekTipsData.* 
From   TekTipsData 
       Inner Join (
            SELECT Min([ID]) AS MinId, 
                   TekTipsData.JobNum
            FROM   TekTipsData
            Group By JobNum
            ) As A On TekTipsData.Id = A.MinId

When I imported in to Access, I called the table TekTipsData. You'll need to replace this with your table name. You will also need to make sure you have a primary key (in my table it's named ID).

You will also notice that this is remarkably similar to the query I wrote for you in the VB forum. If this query works, I strongly urge you to post a reply to each thread you created in the other forums. Also, make sure you include a link to this thread so that when someone else is looking for a similar solution will be able to find this answer.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Swi]

Thanks. It worked.

Swi