Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Get One Record for each Job using SQL 4

Status
Not open for further replies.
Swi

Swi

Programmer
Joined
Feb 4, 2002
Messages
1,978
Location
US
I have a table that has multiple entries for each job. I want to only pull one record for each job (It doesn't matter which one entry). I thought about using SELECT DISTINCT but it will not work because I want to pull all data from the table to show in a datareport and some of the other field data within each entry is different. Any ideas?

Swi
 
Sort by date Sort by votes
That sounds a little inconsistent. Seems like you are asking for ONE record but at the same time you want data from TWO or more records.

It might be possible with a self join. Perhaps if you posted some sample data to show exactly what you need...

 
Upvote 0 Downvote
You can probably accomplish this with a subquery.

Since this is more of a SQL question, I recommend you post in the SQL Programming forum forum183.

When posting, I recommend you show some sample data and an expected result set.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
SWI use a GroupBy instead...

E.G.

JobsTable
ID
JobType
Field2
Field3

SELECT JobType, ID, Field2, Field3 FROM JobsTable
GROUP BY JobType

Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".
 
Upvote 0 Downvote
This is what I tried:

RPTconn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\XXXXXXXX.mdb"

RPTrs.Open "SELECT JobNum, Client, AE, BalDue, ArtTpDate, FrmsdtdoneNPRTDt, " & _
"BLOSODt, BLADtNPRTDt, OrgDropDate, ActualDropDt, StatDt, Comment " & _
"FROM Master GROUP BY JobNum", RPTconn

I get the following error:

You tried to execute a query that does not include the specified expression 'Client' as part of an aggregate function.

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?,,

The output would be:

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,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.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,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,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.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?,,

Swi
 
Upvote 0 Downvote
Does this query work?

Code: 
Select [Master].*
From   [Master]
       Inner Join (
         Select Min(JobNum) As JobNum,
               Client
         From   [Master]
         ) A On [Master].JobNum = A.JobNum


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
This may give you what you want:
[tt]
SELECT JobNum, Min(Client), Min(AE), Min(BalDue),
Min(ArtTpDate), Min(FrmsdtdoneNPRTDt),
Min(BLOSODt), Min(BLADtNPRTDt), Min(OrgDropDate),
Min(ActualDropDt), Min(StatDt), Min(Comment)
FROM Master
GROUP BY JobNum
[/tt]
Change Min( ) to Max( ) where appropriate.
 
Upvote 0 Downvote
I missed a group by.

Code: 
Select [Master].*
From   [Master]
       Inner Join (
         Select Min(JobNum) As JobNum,
               Client
         From   [Master]
         [red]Group By Client[/red]
         ) A On [Master].JobNum = A.JobNum

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
It returned more records than were in the database and there are still duplicates.

Swi
 
Upvote 0 Downvote
Thanks to all.

Swi
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

2009luca
  • Question Question
PRINT title in center on hight page for each page
Replies
4
Views
440
mintjulep
M
sal21
  • Locked
  • Question Question
NOT FOR ME, get value from table web
Replies
1
Views
450
sal21
sal21
sal21
  • Locked
  • Question Question
get value from html page
Replies
7
Views
781
strongm
strongm
sal21
  • Locked
  • Question Question
merge .txt in one named big.txt
Replies
10
Views
2K
sal21
sal21
sal21
  • Locked
  • Question Question
API REST get zip cod efrom street name and city
Replies
1
Views
416
strongm
strongm

Part and Inventory Search

Sponsor

Back
Top