1

Access - We have a table with a memo field [DESCRIPTION_OF_REQUEST].

Another table to record any date changes (for deliverables) that occur to a particular request. One to many relationship.

From a reporting point of view we are required to export (as Excel) the requests on a monthly basis and would like to have all the changes to any deliverable dates concatenated to the comments field.

[tblRequests]


RegProjID    DateOfRequest  DESCRIPTION_OF_REQUEST
----------   -------------  ----------------------
116          06 Oct 2015   "Stability of broken ampoule."

and

[tblDateChanges]

RegProjChangeID  RegProjID  DateOfDateChange    UserName   ReasonForChange
---------------  ---------  ----------------    --------   ---------------
355              116        19 Jan 2016         dskelly "Duplicate Request from Simon Wong CPP Request already in process Original Request # 13661"

1549            116         21 Mar 2016         sdoyle  This request looks like an entry error - it is logged as Trandate 200mg tablets, but refers to a broken ampoule. We received an enquiry from Francisco Gomez for information on a broken Trandate ampoule which has been logged correctly as Req ID 18540.

The output must have the two reasons for the change in the request concatenated into the DESCRIPTION_OF_REQUEST

Can we build a query that will concatenate all the ReasonForChange into one record prior to export or should this be done in Excel by exporting two queries?

As usual a simple database has grown into a valuable reporting tool and the powers that be want it to be everything to everyone.

Thank you for any suggestions you may have.

SierraOscar
  • 17,507
  • 6
  • 40
  • 68
Gary
  • 13
  • 2
  • Excel may be the easiest way, you could use some VBA to stick everything together within Access and use that within the query - it will have a fairly large impact on the speed of the query if it's a big table. – Darren Bartrup-Cook May 13 '16 at 11:31
  • 1
    Check concatenate eg http://stackoverflow.com/a/93863/2548 – Fionnuala May 13 '16 at 12:33

1 Answers1

0

If you are okay with adding a new field to your primary table it would be pretty simple using VBA, you could do something like this:

Function updateReasonForChange()
Dim db AS DAO.Database
Dim Req AS DAO.Recordset
Dim Chg AS DAO.Recordset

Set db = currentDb()
Set Req = db.OpenRecordset("tblRequests")
Set Chg = db.OpenRecordset(SQL here to select only changes that have not previously
been added to tblRequests, use a date range would be my preference but you could also
create a yes/no field in the date changes table to mark it as processed)

Req.MoveFirst
Chg.MoveFirst

Do While Not Chg.EOF 'run though all date change records selected
    Req.Seek "=" Chg![RegProjID] 'This assumes you have RegProjID indexed

          If Req.NoMatch
               'do nothing if no match is found, you could add some error handling here
               'but if you have a parent-child relationship you should have no issue
          Else
              Req.Edit
              Req![DESCRIPTION_OF_REQUEST] = Req![DESCRIPTION_OF_REQUEST] & " " & Chg![ReasonForChange]
              Req.Update
          End if
Loop

Req.Close
Chg.Close
db.Close

Set Req = Nothing
Set Chg = Nothing
Set db = Nothing

End Function

EDIT: Your field type for [DESCRIPTION_OF_REQUEST] will matter here, if it is set to max of 255 characters your may have errors due to many reasons for change being added to the same record, you may have to change that.

All this, and I would recommend a simple output select query joined on the primary key, bring in the [DateOfDateChange] and [ReasonForChange] and display the result as a pivot table vs a simple spread sheet, it will serve your purposes much better I would think.

pegicity
  • 359
  • 1
  • 3
  • 12