Combine Fields from Table/Query Into One 2

[AtlasAF]

Ok, here's what I currently have: Weekly a report is generated from the database pulling from tblLog, tblProjectData and tblActivityManager to create a Weekly Activity Report (WAR). tblProjectData contains the constants and tblLog contains the daily/weekly entries. Currently I have a append query that combines certain fields in each table and appends it to another. A word MailMerge is then generated from that table.

While this works fine as it is, if an idividual has more than one log in their project, within the dates specified, a sperate record is created in tblWAR. The table and query structure as follows:

tblWAR:
ProjectSummary
CommentSummary
ActivityManager
ProjectNumber

tblProjectData: (I'm leaving out the fields that aren't used)
ProjectNumber
Title
Description
AmID

tblActivityManager: (Again leaving out fields not used)
Rank/Grade
Last

tblLog:
ID (autonumber)
ProjectNumber
Comments
Date

qryWAR: (Query that combines data)

INSERT INTO tblWAR ( ProjectSummary, CommentsSummary, Name, ProjectNumber )
SELECT [Title] & ".  " & [Description] AS ProjectSummary, tblLog.Comments, [Rank/Grade] & " " & [Last] AS Name, tblProjectData.ProjectNumber
FROM (tblActivityManager INNER JOIN tblProjectData ON tblActivityManager.PriPMID = tblProjectData.PrimaryID) INNER JOIN tblLog ON tblProjectData.ProjectNumber = tblLog.ProjectNumber
WHERE (((tblActivityManager.Section) Like [Forms]![frmGenWAR]![cmbSection]+'*') AND ((tblLog.Date) Between [Forms]![frmGenWAR]![cmbBegin] And [Forms]![frmGenWAR]![cmbEnd]))
ORDER BY tblActivityManager.Section, tblActivityManager.Last, tblProjectData.ProjectNumber, tblLog.Date;

What I would like is for each project you get one entry for ProjectSummary, CommentSummary and Name with all the Log entries for that project combined in the CommentSummary field. (I hope someone understood that, I can't think of any other way to put it...)

Thanks for the help in advance.

AtlasAF
USAF

 

[dhookom]

Are you hoping to combine a field from multiple child records into a single expression? If so read faq701-4233.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[AtlasAF]

ok, using the Concatenate function fromt he FAQ, I modified the query to the follow. I'm not recieving an error anywhere but the CommentsSummary field is blank. Am I doing something wrong???

INSERT INTO tblWAR ( ProjectSummary, CommentsSummary, Name, ProjectNumber )
SELECT [Title] & ".  " & [Description] AS ProjectSummary, Concatenate("SELECT Comments FROM tblLog WHERE Date Between " & [Forms]![frmGenWAR]![cmbBegin] & " And " & [Forms]![frmGenWAR]![cmbEnd]& " ORDER BY Date;") AS Comments, [Rank/Grade] & " " & [Last] AS Name, tblProjectData.ProjectNumber
FROM (tblActivityManager INNER JOIN tblProjectData ON tblActivityManager.PriPMID = tblProjectData.PrimaryID) INNER JOIN tblLog ON tblProjectData.ProjectNumber = tblLog.ProjectNumber
WHERE (((tblActivityManager.Section) Like [Forms]![frmGenWAR]![cmbSection]+'*'))
ORDER BY tblActivityManager.Section, tblActivityManager.Last, tblProjectData.ProjectNumber;

AtlasAF
USAF

 

[dhookom]

Try place "#" around your date expressions and []s around your date field name:

INSERT INTO tblWAR ( ProjectSummary, CommentsSummary, Name, ProjectNumber )
SELECT [Title] & ".  " & [Description] AS ProjectSummary, 
Concatenate("SELECT Comments FROM tblLog WHERE [Date] Between #" & [Forms]![frmGenWAR]![cmbBegin] & "# And #" & [Forms]![frmGenWAR]![cmbEnd]& "# ORDER BY [Date]") AS Comments, 
[Rank/Grade] & " " & [Last] AS Name, 
tblProjectData.ProjectNumber
FROM (tblActivityManager INNER JOIN tblProjectData ON tblActivityManager.PriPMID = tblProjectData.PrimaryID) INNER JOIN tblLog ON tblProjectData.ProjectNumber = tblLog.ProjectNumber
WHERE (((tblActivityManager.Section) Like [Forms]![frmGenWAR]![cmbSection]+'*'))
ORDER BY tblActivityManager.Section, tblActivityManager.Last, tblProjectData.ProjectNumber;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[AtlasAF]

That worked to get the fields populated. Now the problem is that it is combining all the entries into one project as well. Basically I'm getting all the inputs for the individual projects under each project. How would I get the Concat statement to combine on the comments with the same project number? After that, how would I combine those results with the main query results to get the three fields? BTW Thank you for your help Duane.

AtlasAF
USAF

 

[dhookom]

You can add more to the where clause inside the concatenate function call. This is a guess that assumes ProjectNumber is the correct filter and is numeric:

INSERT INTO tblWAR ( ProjectSummary, CommentsSummary, Name, ProjectNumber )
SELECT [Title] & ".  " & [Description] AS ProjectSummary, 
Concatenate("SELECT Comments FROM tblLog WHERE [Date] Between #" & [Forms]![frmGenWAR]![cmbBegin] & "# And #" & [Forms]![frmGenWAR]![cmbEnd]& "# AND ProjectNumber =" & [ProjectNumber] & " ORDER BY [Date]") AS Comments, 
[Rank/Grade] & " " & [Last] AS Name, 
tblProjectData.ProjectNumber
FROM (tblActivityManager INNER JOIN tblProjectData ON tblActivityManager.PriPMID = tblProjectData.PrimaryID) INNER JOIN tblLog ON tblProjectData.ProjectNumber = tblLog.ProjectNumber
WHERE (((tblActivityManager.Section) Like [Forms]![frmGenWAR]![cmbSection]+'*'))
ORDER BY tblActivityManager.Section, tblActivityManager.Last, tblProjectData.ProjectNumber;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[AtlasAF]

Ok, the following will populate the fields and the query doesn't error out. Now the problem is that the projects that have not comments are still showing up. So I'm getting one record for each project and the comments field is blank when there are no entries. I tried Is Not Null statement to exclude them, but that didn't work. I'm going to try using another query to filter out the empty records, but is there another way I could do it without making another query???

INSERT INTO tblWAR ( ProjectNumber, ProjectSummary, CommentsSummary, Name )
SELECT tblProjectData.ProjectNumber, [Title] & ".  " & [Description] AS ProjectSummary, Concatenate("SELECT Comments FROM tblLog WHERE [Date] Between #" & [Forms]![frmGenWAR]![cmbBegin] & "# And #" & [Forms]![frmGenWAR]![cmbEnd] & "# AND [tblLog].[ProjectNumber] Like " & [tblLog].[ProjectNumber] & " ORDER BY [Date]") AS Comments, [Rank/Grade] & " " & [Last] AS Name
FROM (tblActivityManager INNER JOIN tblProjectData ON tblActivityManager.PriPMID = tblProjectData.PrimaryID) INNER JOIN tblLog ON tblProjectData.ProjectNumber = tblLog.ProjectNumber
WHERE (((tblActivityManager.Section) Like [Forms]![frmGenWAR]![cmbSection]+'*'))
ORDER BY tblActivityManager.Section, tblActivityManager.Last, tblProjectData.ProjectNumber;

AtlasAF
USAF

 

[dhookom]

You could change your

FROM (tblActivityManager INNER JOIN tblProjectData ON tblActivityManager.PriPMID = tblProjectData.PrimaryID) INNER JOIN tblLog ON tblProjectData.ProjectNumber = tblLog.ProjectNumber

to a query that selects only projects with comments.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[AtlasAF]

Got it to work, that again for your help

AtlasAF
USAF

 

[AtlasAF]

Another problem arrised with these queries. The solution I am using is as follows:

First, I run an append query to find all the Log inputs that are between the supplied dates and formats them, adds the date to the begining of the entry, and appends them to a table.

Next I use a select query to combine the project summary, and other nessessary information, and run the concatenate function on the project entries.

Last is another append query that transfers the final product, records, from the select query to the table that the MailMerge is based off of.

The problem I'm having now is when I run the select query, all the entries are cut down to 255 character entries. When I look at the first append query, all the information is present. I had the Concatenate function display the results in a messagebox before appending them to the query and all the information is present. Can anyone think of a reason why the select query is cutting off the records? I've tried rebuilding the query from scratch and I've tried various other methods but the result is always the same with the select query. FYI, before I built the first append query, to add the date field to the entry, everything worked fine with just the select query and the final append query. Any help would be appreciated.


AtlasAF
USAF

 

[PHV]

Can anyone think of a reason why the select query is cutting off the records?
I think of several reasons:
1) use of the DISTINCT predicate
2) aggregate query
3) the return value of the concatenate function populates a non-memo field
4) ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AtlasAF]

I checked all that and none of it is causing the problem. After a little playing around, I think I found it, but it doesn't make any since to me. When I remove the Date from the log entries everything works perfectly, except that the date for each entry is missing. I've never heard of a date in a query causing it to cut off at 255. Can anyone explain why when the date field is added it cuts off? Is there a way around this to still have the date included?

I've attempted to move the date field to various locations in the entry but as long as it is present, somewhere, in the Concatenate function the field is limited. Any help would be appreciated.

AtlasAF
USAF

 

[dhookom]

This is a query forum so if you have trouble with a query, we are hoping to see some SQL views. Can you provide the SQL view of "run the select query"?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[AtlasAF]

Sorry, the select query is as follows:

SELECT DISTINCT tblProjectData.ProjectNumber, [Title] & ".  " & [Description] AS ProjectSummary, Concatenate("SELECT Comments FROM tblWarLog WHERE [tblWarLog].[ProjectNumber] Like " & [tblWarLog].[ProjectNumber]) AS Comments, [Rank/Grade] & " " & [Last] AS Name, [Rank/Grade] & " " & [First] & " " & [Last] AS FullName
FROM (tblActivityManager INNER JOIN tblProjectData ON tblActivityManager.PriPMID = tblProjectData.PrimaryID) INNER JOIN tblWarLog ON tblProjectData.ProjectNumber = tblWarLog.ProjectNumber
WHERE (((tblActivityManager.Section) Like [Forms]![frmGenWAR]![cmbSection]+'*'));

AtlasAF
USAF

 

[dhookom]

PH mentioned 1) use of the DISTINCT predicate which is clearly in your SQL. What happens if you take it out?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[AtlasAF]

Same thing, the only way the query seems to work is to remove the Date and Formatting in the following append query which populates tblWarLog:

INSERT INTO tblWarLog ( ProjectNumber, Comments, [Date] )
SELECT tblLog.ProjectNumber, "- (" & [Date] & ")  " & [tblLog].[Comments] AS Comments, tblLog.Date
FROM tblLog
WHERE (((tblLog.Date) Between [Forms]![frmGenWAR]![cmbBegin] And [Forms]![frmGenWAR]![cmbEnd]))
ORDER BY tblLog.Date;

If I change the query above to the following, everything works perfectly. But my supervisor is insisting on having the date included at the begining of each entry:

INSERT INTO tblWarLog ( ProjectNumber, Comments, [Date] )
SELECT tblLog.ProjectNumber, tblLog.Comments, tblLog.Date
FROM tblLog
WHERE (((tblLog.Date) Between [Forms]![frmGenWAR]![cmbBegin] And [Forms]![frmGenWAR]![cmbEnd]))
ORDER BY tblLog.Date;

AtlasAF
USAF

 

[AtlasAF]

I retract previous statement Duane, I didn't notice another query contained the Disctinct statement as well. When I remove all of the disctinct predicate from all the querys, the data is not restricted. The only problem is that using your Concatenate function creates multiple records after execution. For example: I made a table that contained two fields, ProjectNumber and Comments. That table contains all the entries needed for the final product. I created a Select query with using the Concatenate function to combine the fields:

SELECT ProjectNumber, Concatenate("SELECT Comments FROM tblWarLog WHERE [tblWarLog].[ProjectNumber] Like " & [tblWarLog].[ProjectNumber])

The concatenate function does it's thing and combines the fields together but, it creates duplicates depending on how many parent records that it started with. So if a project had two entries, my query displays two iditical entries. I can't seem to figure out how to get one entry per project without using a group by or Distinct query?

AtlasAF
USAF

 

[PHV]

And what about something like this ?
SELECT A.ProjectNumber, A.ProjectSummary, Concatenate("SELECT Comments FROM tblWarLog WHERE ProjectNumber Like " & [A].[ProjectNumber]) AS Comments, A.FullName
FROM (
SELECT DISTINCT tblProjectData.ProjectNumber, [Title] & ". " & [Description] AS ProjectSummary, [Rank/Grade] & " " & [Last] AS Name, [Rank/Grade] & " " & [First] & " " & [Last] AS FullName
FROM (tblActivityManager INNER JOIN tblProjectData ON tblActivityManager.PriPMID = tblProjectData.PrimaryID) INNER JOIN tblWarLog ON tblProjectData.ProjectNumber = tblWarLog.ProjectNumber
WHERE tblActivityManager.Section Like [Forms]![frmGenWAR]![cmbSection] & '*'
) AS A

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AtlasAF]

I think that worked perfectly. Now I'll just need to exam what your query did so I can understand it and duplicate it later. Thank you all for your help!

AtlasAF
USAF