By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,773 Members | 2,307 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,773 IT Pros & Developers. It's quick & easy.

Duplication By Design Help - Query How to avoid

P: 1
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!
Jan 8 '08 #1
Share this Question
Share on Google+
1 Reply


Jim Doherty
Expert 100+
P: 897
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


Expand|Select|Wrap|Line Numbers
  1.  Public Function Concat(strGrouping As String, _ 
  2. strComment As String) As String
  3. Static strLastGrouping As String
  4. Static strComments As String
  5.  
  6. If strGrouping = strLastGrouping Then
  7. If InStr(strComments, strComment) > 0 Then
  8. Else
  9. strComments = strComments & ", " & strComment
  10. End If
  11. Else
  12. strLastGrouping = strGrouping
  13. strComments = strComment
  14. End If
  15. Concat = strComments
  16.  
sample calling sql statement for your use with this function

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT DISTINCT YourTableName.[WO No], concat([WO No],[Work Descr]) AS Description
  3. FROM YourTableName;

Jim
Jan 22 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.