Filtering out duplicates.......

[pdbowling]

I've got a table

Dept_Info

ID...........Dept.........DeptGroup.....Station
IA702.....PC............Plant 1...........N1
MA702....PC............Plant 1...........S1
IP000.....PT.............Paint..............P1
IW000....BW...........BWS...............B1
UW000...BW...........BWS...............B2
etc...

and a query (called Costs) that returns

DeptGroup......month
Plant 1............value
Paint...............value
BWS................value
etc...

Now my task is to relate this data back to its original Dept

I run this query:

Select distinctRow(Costs.DeptGroup), [Dept_Info].[Dept], Costs.Value
FROM Costs left JOIN [Dept_Info] on Costs.DeptGroup = [Dept_Info].[DeptGroup];

I get

DeptGroup.....Dept....Cost
Plant 1...........PC.......Value
Plant 1...........PC.......Value repeated
Paint..............PT........Value
BWS...............BW......Value
BWS...............BW.......Value repeated
etc...

Can I get this to be

Plant 1......PC.......Value
Paint.........PT........Value
BWS..........BW.......Value
etc...

no repeats......??????

I'm pretty sure it has something to do with the Station column
having distinct values, but I don't need to know this in the result.

I've turned myself in circles on this one. If you want to know 'Why' I need to do this.. just ask...
Thanks everyone
PB

 

[pdbowling]

Aaarrgghh

I do apologize for wasting anyone's time with this.

DistinctRow did Exactly what it was supposed to...

I just changed it to Distinct and it only looked at the one column.

Sorry.
PB

 

[ColinEssex]

Blimey!!! suddenly this question of yours seems to be wherever I look!!!

Having a bad day?

Col

 

[Mile-O]

When you said you were going to post it elsewhere what made you post it to the exact same Access forum where your enquiry produced no valid answers?

Although the forum is split into sections such as General, Queries, etc. the same people browse through them all.

Just bewildered...