RE: Concatenate and Null Values
- From: Hoopster <Hoopster@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 10 Jan 2007 12:32:00 -0800
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?
- Follow-Ups:
- RE: Concatenate and Null Values
- From: Duane Hookom
- RE: Concatenate and Null Values
- References:
- RE: Concatenate and Null Values
- From: Hoopster
- RE: Concatenate and Null Values
- From: Duane Hookom
- RE: Concatenate and Null Values
- Prev by Date: Re: "Hiding" field if another field is referenced in another table.
- Next by Date: RE: Concatenate and Null Values
- Previous by thread: RE: Concatenate and Null Values
- Next by thread: RE: Concatenate and Null Values
- Index(es):
Relevant Pages
|
|