RE: Concatenate and Null Values

Tech-Archive recommends: Fix windows errors by optimizing your registry

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: MT Data Retrieval Question
    ... maybe the original query, i.e. IQueryable). ... method that you pass as the callback, and you hold onto that object. ... In the abort method, you check the flag. ...
    (microsoft.public.dotnet.languages.csharp)
  • 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)