RE: Concatenate and Null Values

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

  • Re: how to link to a csv file with a date field in it?
    ... Regarding the scheduler it's in XP and above - if you aren't familiar with using the Windows Task Scheduler, here is a link to a fairly clean, simple, short tutorial. ... I think you have the right idea - link to the csv, maybe run an append query to process the contents into your main table, in that query you can fix things such as the date. ... s is the complete row of data as a string, flag is a boolean that is initialized to be False. ... The full code I snipped this from contains so much checking for quotes and # hashes and internal commas and internal single quotes replacing certain chars, that it's difficult for me to follow and I wrote it. ...
    (microsoft.public.access.externaldata)
  • Re: how to link to a csv file with a date field in it?
    ... I think you have the right idea - link to the csv, maybe run an append query to process the contents into your main table, in that query you can fix things such as the date. ... s is the complete row of data as a string, flag is a boolean that is initialized to be False. ... We can create a recordset object and set it to a specific table - we add the new row one field at a time, testing each field for type using a select case, add the # for dates or ' for text or nothing for numeric field types to the fldArrrayelement contents and the set rs.fields= the resulting expression. ... The full code I snipped this from contains so much checking for quotes and # hashes and internal commas and internal single quotes replacing certain chars, that it's difficult for me to follow and I wrote it. ...
    (microsoft.public.access.externaldata)
  • RE: Concatenate and Null Values
    ... "Duane Hookom" wrote: ... I went to the Design Screen of my Flag Audits Table but it ... I've been working with a Union Query all day but I can't get anything ...
    (microsoft.public.access.reports)
  • Re: unwanted duplicate loan account numbers
    ... I have rewritten your query a bit just to help me study it: ... inactive flag], ... you say there is a duplicated [loan acct #] in this query's results. ... Now, if you have such a duplicate in one column, the other columns may ...
    (microsoft.public.access.queries)
  • RE: Concatenate and Null Values
    ... I've been working with a Union Query all day but I can't get anything ... Flag Style with the whole Concatenated list of Repair reasons. ... "Duane Hookom" wrote: ...
    (microsoft.public.access.reports)