RE: Concatenate and Null Values

Duane,

You are a life saver! Here is my Table Structure. I want to thank you
again for helping me out. Im learning alot going through all of
this.ShowFields "[Flag Audits]"
Flag Audits
==========================
Audit Date 8 8
Vendor 10 50
Audit number 4 4
Quality Auditor 10 50
Department 10 50
Department Name 10 50
Department Title 10 50
Flag Style 10 50
Item Number 10 50
Low Header 6 4
High Header 6 4
Header Stitch #1 6 4
Header Stitch #2 6 4
Header Stitch #3 6 4
Header Stitch #4 6 4
Header Stitch #5 6 4
Low Fly 6 4
High Fly 6 4
Fly Hem #1 6 4
Fly Hem #2 6 4
Fly Hem #3 6 4
Fly Hem #4 6 4
Fly Hem #5 6 4
Low Side 6 4
High Side 6 4
Side Hem Stitch #1 6 4
Side Hem Stitch #2 6 4
Side Hem Stitch #3 6 4
Side Hem Stitch #4 6 4
Side Hem Stitch #5 6 4
Low Grommet 6 4
High Grommet 6 4
Grommet 1 6 4
Grommet 2 6 4
Grommet 3 6 4
Grommet 4 6 4
Grommet 5 6 4
Grommet 6 6 4
Grommet 7 6 4
Grommet 8 6 4
Grommet 9 6 4
Grommet 10 6 4
Label Type 10 50
Label Type 1 10 50
Label Type 2 10 50
Label Type 3 10 50
Label Type 4 10 50
Label Type 5 10 50
Low Label 6 4
High Label 6 4
LabelPlace 1 6 4
LabelPlace 2 6 4
LabelPlace 3 6 4
LabelPlace 4 6 4
LabelPlace 5 6 4
Tail Spec 10 50
Tail 1 6 4
Tail 2 6 4
Tail 3 6 4
Tail 4 6 4
Tail 5 6 4
Low Fold Width 6 4
High Fold Width 6 4
Low Fold Length 6 4
High Fold Length 6 4
Fold Width 1 6 4
Fold Width 2 6 4
Fold Width 3 6 4
Fold Width 4 6 4
Fold Width 5 6 4
Fold Length 1 6 4
Fold Length 2 6 4
Fold Length 3 6 4
Fold Length 4 6 4
Fold Length 5 6 4
Low Width 6 4
High Width 6 4
Low Length 6 4
High Length 6 4
Flag Width #1 6 4
Flag Width #2 6 4
Flag Width #3 6 4
Flag Width #4 6 4
Flag Width #5 6 4
Flag Length #1 6 4
Flag Length #2 6 4
Flag Length #3 6 4
Flag Length #4 6 4
Flag Length #5 6 4
Low Header Balance 6 4
High Header Balance 6 4
Header Balance 1 6 4
Header Balance 2 6 4
Header Balance 3 6 4
Header Balance 4 6 4
Header Balance 5 6 4
Side Hem Width Low 6 4
Side Hem Width high 6 4
Side Hem Width 1 6 4
Side Hem Width 2 6 4
Side Hem Width 3 6 4
Side Hem Width 4 6 4
Side Hem Width 5 6 4
Low back 6 4
High Back 6 4
Back Tack #1 6 4
Back Tack #2 6 4
Back Tack #3 6 4
Back Tack #4 6 4
Back Tack #5 6 4
Low Red 6 4
High Red 6 4
Stiches per Inch 1 (Red) 6 4
Stiches per Inch 2 (Red) 6 4
Stiches per Inch 3 (Red) 6 4
Stiches per Inch 4 (Red) 6 4
Stiches per Inch 5 (Red) 6 4
Low White 6 4
High White 6 4
Stiches per Inch 1 (White) 6 4
Stiches per Inch 2 (White) 6 4
Stiches per Inch 3 (White) 6 4
Stiches per Inch 4 (White) 6 4
Stiches per Inch 5 (White) 6 4
Low Blue 6 4
High Blue 6 4
Stiches per Inch 1 (Blue) 6 4
Stiches per Inch 2 (Blue) 6 4
Stiches per Inch 3 (Blue) 6 4
Stiches per Inch 4 (Blue) 6 4
Stiches per Inch 5 (Blue) 6 4
Self Header Low 6 4
Self Header High 6 4
Self Header 1 6 4
Self Header 2 6 4
Self Header 3 6 4
Self Header 4 6 4
Self Header 5 6 4
Low Square 6 4
High Square 6 4
Square Tollerance 1 6 4
Square Tollerance 2 6 4
Square Tollerance 3 6 4
Square Tollerance 4 6 4
Square Tollerance 5 6 4
Low Diagonal 6 4
High Diagonal 6 4
Diagonal 1 6 4
Diagonal 2 6 4
Diagonal 3 6 4
Diagonal 4 6 4
Diagonal 5 6 4
Diagonal 6 6 4
Diagonal 7 6 4
Diagonal 8 6 4
Diagonal 9 6 4
Diagonal 10 6 4
Diagonal Difference 6 4
Diagonal Tolerance 1 6 4
Diagonal Tolerance 2 6 4
Diagonal Tolerance 3 6 4
Diagonal Tolerance 4 6 4
Diagonal Tolerance 5 6 4
Pass/Repair/Reject 1 10 50
Pass/Repair/Reject 2 10 50
Pass/Repair/Reject 3 10 50
Pass/Repair/Reject 4 10 50
Pass/Repair/Reject 5 10 50
Repair/Reject Comments 1 10 50
Repair/Reject Comments 2 10 50
Repair/Reject Comments 3 10 50
Repair/Reject Comments 4 10 50
Repair/Reject Comments 5 10 50
Audit Comments 1 10 255
Audit Comments 2 10 255
Audit Comments 3 10 255



"Duane Hookom" wrote:

Typing it all in would work for me ;-) If you don't want to type, you can
open a new, blank module and paste this code:
Sub ShowFields(pstrTable As String)
Dim fld As DAO.Field
Dim tbl As DAO.TableDef
Dim db As DAO.Database
Set db = CurrentDb
Set tbl = db.TableDefs(pstrTable)
Debug.Print tbl.Name
Debug.Print "=========================="

For Each fld In tbl.Fields
Debug.Print fld.Name, fld.Type, fld.Size
Next
End Sub
Make sure you have a reference to the Microsoft DAO library. Then view the
immediate/debug window (press Ctrl+G) and type

ShowFields "Your Table Name"

--
Duane Hookom
Microsoft Access MVP


"Hoopster" wrote:

Duane,

I'm sorry sir but I can't figure out how to get you my current table
structure. I went to the Design Screen of my Flag Audits Table but it
wouldn't let me Copy and Paste. Do you want me to try and type it all in?

"Duane Hookom" wrote:

Reply back with your current table structure.

--
Duane Hookom
Microsoft Access MVP


"Hoopster" wrote:

Duane,

I've been working with a Union Query all day but I can't get anything
more out of it than before. The Union Query looks like this:

SELECT [Flag Style],[conRepair1] FROM [Flag Audits Small Query] ORDER BY
[Flag Style]

UNION SELECT [Flag Style],[conRepair1] FROM [Flag Audits Small Query]

Or at least this is what I have now. I think I've tried about every
combination I could think of. For some reason I can't get it to show only one
Flag Style with the whole Concatenated list of Repair reasons.
I tried to allow the Wizard to normalize my structure but nothing
happened. I have looked at this before but not sure what I could do
different. My mistake was to design the form so that the Auditor could do 5
Audits on the same Form but this was how they did it in the past when they
used Excel. This means 5 Audits per Record.

"Duane Hookom" wrote:

I think I'm sorry I asked :-) Have you considered normalizing this and
working with the normalized structure? I would at least create a union query
that normalizes this. Working with the union query would be much less
complicated than working with your current structure.

--
Duane Hookom
Microsoft Access MVP


"Hoopster" wrote:

Sure thing Duane. Thanks for getting back to me. Here is the complete SQL
View of the my Select Query (Flag Audits Small Query):

SELECT [Flag Audits].[Audit number], [Flag Audits].[Audit Date], [Flag
Audits].[Flag Style], [Flag Audits].[Item Number], [Flag
Audits].[Pass/Repair/Reject 1], [Flag Audits].[Repair/Reject Comments 1],
IIf([Pass/Repair/Reject 1]="Repair",[Repair/Reject Comments 1],"") AS
Repair1, Concatenate("SELECT IIF([Pass/Repair/Reject 1]
='Repair',[Repair/Reject Comments 1],Null) & (IIF([Pass/Repair/Reject 2]
='Repair',[Repair/Reject Comments 2],Null) + ', ') & (IIF([Pass/Repair/Reject
3] ='Repair',[Repair/Reject Comments 3],Null) + ', ') &
(IIF([Pass/Repair/Reject 4] ='Repair',[Repair/Reject Comments 4],Null) + ',
') & (IIF([Pass/Repair/Reject 5] ='Repair',[Repair/Reject Comments 5],Null) +
', ') FROM [Flag Audits] WHERE ( [Pass/Repair/Reject 1] = 'Repair' OR
[Pass/Repair/Reject 2] = 'Repair' OR [Pass/Repair/Reject 3] = 'Repair' OR
[Pass/Repair/Reject 4] = 'Repair' OR [Pass/Repair/Reject 5] = 'Repair' ) AND
[Audit Number] = " & [Audit Number]) AS conRepair1, IIf([Pass/Repair/Reject
1]="Reject",[Repair/Reject Comments 1],"") AS Reject1, Concatenate("SELECT
IIF([Pass/Repair/Reject 1] ='Reject',[Repair/Reject Comments 1],Null) &
(IIF([Pass/Repair/Reject 2] ='Reject',[Repair/Reject Comments 2],Null) + ',
') & (IIF([Pass/Repair/Reject 3] ='Reject',[Repair/Reject Comments 3],Null) +
', ') & (IIF([Pass/Repair/Reject 4] ='Reject',[Repair/Reject Comments
4],Null) + ', ') & (IIF([Pass/Repair/Reject 5] ='Reject',[Repair/Reject
Comments 5],Null) + ', ') FROM [Flag Audits] WHERE ( [Pass/Repair/Reject 1] =
'Reject' OR [Pass/Repair/Reject 2] = 'Reject' OR [Pass/Repair/Reject 3] =
'Reject' OR [Pass/Repair/Reject 4] = 'Reject' OR [Pass/Repair/Reject 5] =
'Reject' ) AND [Audit Number] = " & [Audit Number]) AS conReject1, [Flag
Audits].[Pass/Repair/Reject 2], [Flag Audits].[Repair/Reject Comments 2],
IIf([Pass/Repair/Reject 2]="Repair",[Repair/Reject Comments 2],"") AS
Repair2, IIf([Pass/Repair/Reject 2]="Reject",[Repair/Reject Comments 2],"")
AS Reject2, [Flag Audits].[Pass/Repair/Reject 3], [Flag
Audits].[Repair/Reject Comments 3], IIf([Pass/Repair/Reject
3]="Repair",[Repair/Reject Comments 3],"") AS Repair3,
IIf([Pass/Repair/Reject 3]="Reject",[Repair/Reject Comments 3],"") AS
Reject3, [Flag Audits].[Pass/Repair/Reject 4], [Flag Audits].[Repair/Reject
Comments 4], IIf([Pass/Repair/Reject 4]="Repair",[Repair/Reject Comments
4],"") AS Repair4, IIf([Pass/Repair/Reject 4]="Reject",[Repair/Reject
Comments 4],"") AS Reject4, [Flag Audits].[Pass/Repair/Reject 5], [Flag
Audits].[Repair/Reject Comments 5], IIf([Pass/Repair/Reject
5]="Repair",[Repair/Reject Comments 5],"") AS Repair5,
IIf([Pass/Repair/Reject 5]="Reject",[Repair/Reject Comments 5],"") AS Reject5
FROM [Flag Audits]
WHERE ((([Flag Audits].[Audit Date]) Between [forms]![DateRange]![StartDate]
And [forms]![DateRange]![EndDate]))
ORDER BY [Flag Audits].[Flag Style];


"Duane Hookom" wrote:

Could you share your Concatenate(....) or better yet the entire SQL view of
your query?
--
Duane Hookom
Microsoft Access MVP


"Hoopster" wrote:

I have been able to use the sample Concatenate code supplied by Duane Hookom
in a Module and a Select Query using some Code supplied with the help of John
Spencer to Concatenate many Records into a single Column named conRepair1. My
data in my Query looks like this:
Flag Style conRepair1
2 1/2 X 4 Poly Cotton
3 X 5 Nylon U.S. for CVS
3 X 5 Nylon U.S. for CVS 6 stripe to long,
3 X 5 Nylon U.S. for CVS
3 X 5 Nylon U.S. for CVS
3 X 5 Nylon U.S. for CVS
3 X 5 Nylon U.S. Sub Assembly
3 X 5 Nylon U.S. Sub Assembly grommet to low,
3 X 5 Nylon U.S. Sub Assembly field join & 7 stripe uneven,
3 X 5 Nylon U.S. Sub Assembly
3 X 5 Nylon U.S. Sub Assembly grommet to close
3 X 5 Nylon U.S. Sub Assembly
3 X 5 Nylon U.S. Sub Assembly field join -7 stripe,
3 X 5 Nylon U.S. Sub Assembly
3 X 5 Poly Cotton
3 X 5 Poly Cotton
3 X 5 Reliance U.S. Sub Assembly
3 X 5 Reliance U.S. Sub Assembly
3 X 5 Tough - Tex Sub Assembly raw edge on fh, raw edge on fh,
30" X 48" Poly Cotton Banner
30" X 48" Poly Cotton Banner
30" X 48" Poly Cotton Banner
30" X 48" Poly Cotton Banner
30" X 48" Poly Cotton Banner
30" X 48" Poly Cotton Banner
30" X 48" Poly Cotton Banner
30" X 48" Poly Cotton Banner
30" X 48" Poly Cotton Banner label not attached,
30" X 48" Poly Cotton Banner
4 X 6 Super Tough Brand U.S. fly hem, field join - 7 - stripe,

As you can see, not all Records for a certain Flag Style has a
corrisponding conRepair1 Record. When I try to Concantenate the conRepair1
Comments on a Report for each Flag Style, not all Comments are displayed. Can
someone help me to understand what I am missing / doing wrong. Is it because
of the Null Values in the conRepair1 Column? Should I try to Group By Flag
Style in my Query?

.

Relevant Pages
Loading