Loop and Append Text to a Memo Field

[padlocked17]

Good Morning,

I need to figure out how to take a list of awards that are to be presented to an individual and take the description of each award they are being presented and copy that or append it to a memo field for that individual. How would I create a query that would basically loop through all of the awards the individual is to get and then append that data to the end of a memo field?

Thanks,

 

[pbaldy]

You could tweak this to do what you want:

http://www.mvps.org/access/modules/mdl0004.htm

 

[padlocked17]

I took a look, and decided that I want to try and keep as much of this project query based if at all possible. I'm trying to begin creating these databases so that another user down the line would have an easier time tweaking and editing the databases I create.

I've got the following query which I'm using a combination of IIF, and Concatenate in the query design to accomplish that adding of text to an existing memo field.

The issue I'm running into is that I want the query results to be cumulative. That is, I want for the first record that matches and is Concatenated to be updated and then the next record that matches inlclude the concatenation from the previous action.

Does that make any sense?

SELECT tblMembersAwards.AwardsID, tblAwards.Name, tblMembersAwards.MemberID, tblAwards.AwardCategoryID, IIf([AwardCatID]=1,[tblMembers].[DGAwardNonComp] & Chr(13) & Chr(10) & [tblAwards].[Description],"No") AS DGAward, IIf([AwardCatID]=2,[tblMembers].[AcadTrngAccomp] & Chr(13) & Chr(10) & [tblAwards].[Description],"No") AS AcadTrng, IIf([AwardCatID]=3,[tblMembers].[ProQualities] & Chr(13) & Chr(10) & [tblAwards].[Description],"No") AS ProQual, IIf([AwardCatID]=4,[tblMembers].[OtherComm] & Chr(13) & Chr(10) & [tblAwards].[Description],"No") AS OtherComm
FROM tblAwardCategory INNER JOIN ((tblMembersAwards INNER JOIN tblMembers ON tblMembersAwards.MemberID = tblMembers.MemberID) INNER JOIN tblAwards ON tblMembersAwards.AwardsID = tblAwards.AwardsID) ON tblAwardCategory.AwardCatID = tblAwards.AwardCategoryID
WHERE (((tblMembersAwards.MemberID)=[ASK]));

 

[dkinley]

IMO, I don't think you are going to be able to do that in a query alone. The queries job is to return data based on a set of criteria - you are only allowed to manipulate data that is in the same record. In order to manipulate the data from record-to-record, you will need to store one record's information to apply it to the next record's information - in a variable for a final write or perform multiple writes as the data is collected.

Pbaldy's link is a great starting place to show how you can accomplish this goal. Does it need to be so complex? No. However, the demo module is for all data types so you can have reuse if needed in several areas of the application.

If there is a way to do this using only the QBE, I think that it will be so complex as the next user would probably think "Why didn't he use something simple like DAO?" If you are unfamiliar with DAO concepts then this link: http://www.functionx.com/vbaccess/ will give you a great start.

Again, in simplistic terms - 15 lines of code verses (possibly) 15 queries with data manipulation going on between them.

-dK

 

[pbaldy]

I also don't think there's a pure SQL way of doing this (certainly not an easy one). Here's another method if you want options:

http://support.microsoft.com/kb/210163/en-us

 

[padlocked17]

Cool. Thank you both. I'll see about putting some brain bytes into the links you've both provided and see if I can't figure this out.

It's been about 6 months since I've designed a DB and I just can't help but keep beating my head against a wall on this. It's as if I know I need to bust into the VBA, but just can't remember where to start.

Thanks!

 

[dkinley]

It's as if I know I need to bust into the VBA, but just can't remember where to start.

Hehehe ... I understand that feeling. That link I provided should get you pretty acquainted with VBA and DAO. It has walk-thru's that will show you how Access works thru the QBE and then reproduce the same result using DAO/ADO/SQL. That way you can use the ole 'stare-n-compare' until it gels. Notice I used gels instead of hardens because it is never concrete with me.

Good luck!

-dK

 

[padlocked17]

Alright, thank you Paul and dk, I've got the code mostly figured out. Now I just need to filter the query defined in the DAO recordset by the MemberID field. I've got the query which the form is based on including the MemberID in the Recordsource and tried the following code but it's popping an error.

Dim db As Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("qryConcanTest")
qdf.Parameters("MemberID") = Me!MemberID
'Set rs = db.OpenRecordset("qryConcanTest", dbOpenDynaset)
Set rs = qdf.OpenRecordset(dbOpenDynaset)

I've tried setting up the Criteria in the Query but that doesn't work either. I just need to define or pass a "WHERE" statement that gets the MemberID from the Form.

Thanks!

 

[padlocked17]

Turns out the following worked. Only took a little tweaking and a little more digging.

Dim dbs As DAO.Database
Dim qDef As DAO.QueryDef
Dim rs As DAO.Recordset
Set dbs = CurrentDb
Set qDef = dbs.QueryDefs("qryConcanTest")
qDef.Parameters("Forms!frmAddMember!MemberID") = Forms!frmAddMember!MemberID
Set rs = qDef.OpenRecordset

 

[neileg]

Mmm...

Generally, combining more than one piece of data in a field is a bad move. Sooner or later you will want the individual pieces. I'd keep the awards as separate related records. You can concatenate them in a report or list them in a subform.

 

[dkinley]

Thanks for posting your final solution, Russ.

Neileg, I understand what you are saying but Russ is throwing it into a memo field. To expand for Russ' information, you will not be able to segregrate people in the database based on a awards. For instance, you won't be able to make a list of people with award x.

That is, unless your awards are in a seperate table with a foreign key for the person. In this sense, concantenation seems normal for reports or such but you could do this on the fly instead of storing the data in a memo field (since it is already stored in the child table).

-dK

 

[neileg]

Yeah, dK, that's what I was getting at.