Duplication By Design Help - Query How to avoid

**Jim Doherty** · Jan 22 '08, 01:24 AM

Originally posted by dbarmer

I have a unix database system that hold three tables, 254, 255, 256, and all are linked together by WO, CUST NO, LOC NO. This is for Unix Program Purposes Only. Duplication here is by design. So Up Front I have duplicated data due to a sequence work'd performed. For example. WO 000001 has duplicate WO, CUST, LOC etc... four times because it has four different Work Descriptions.

SO, When I transfer these tables to access and create a query and ask for specific info, or even one description only, IT still gives me four lines. The field Seq No, is how I know what description I want. I have tried so many relationships and IIf's that nothing seems to work without duplication.

I AM trying to get these four Work Desc fields in a column, instead of a row, thus I will have only one WO NO instead of four.

This is my current output, notice the data is all the same except work desc.

WO No Dispatch No Cust No Loc No Work Descr
000001 1219268 131720 0001 MATERIAL COST
000001 1219268 131720 0001 REN SA, NO MAINT DON
000001 1219268 131720 0001 MATERIAL COST/RSC PA
000001 1219268 131720 0001 REN CPE SERVICE CNTR

Any help will be so GREATFUL!

Derek!

This will grab your different descriptions over multiple rows and place them in same row per work order no

Code:

 Public Function Concat(strGrouping As String, _ 
strComment As String) As String
Static strLastGrouping As String
Static strComments As String
 
If strGrouping = strLastGrouping Then
If InStr(strComments, strComment) > 0 Then
Else
strComments = strComments & ", " & strComment
End If
Else
strLastGrouping = strGrouping
strComments = strComment
End If
Concat = strComments

sample calling sql statement for your use with this function

Code:

 
SELECT DISTINCT YourTableName.[WO No], concat([WO No],[Work Descr]) AS Description
FROM YourTableName;

Jim