Create aggregated SQL report where the columns are based on a table list

user_3292358 0
Viewed 40

Not sure this is possible but I'm hoping someone can point me in the right direction.

I have a database that audit logs when a record changes stage. What I need to do is consolidate this back up into a report that gives me the earliest audit record for each stage by Record ID. i.e. I end up with a report that looks like this.

+----+---------+---------+---------+
| ID | Stage1  | Stage2  | Stage3  |
+----+---------+---------+---------+
|  1 | 1/10/20 | 1/10/20 | 3/10/20 |
|  2 | 1/10/20 | 2/10/20 | 4/10/20 |
|  3 | 2/10/20 | 2/10/20 | 3/10/20 |
+----+---------+---------+---------+

I can do this with multiple select queries but as my stages are stored in a table I was wondering if there is a way to build this dynamically. This would also minimise maintenance in the future.

My "Audit_Log" table has the following columns:

  • RecordID
  • Stage
  • Event_Date

My "Stages" are stored in a table called "Stages" column "Stage".

Any suggestions would be really appreciated. Thanks

ms-access-2013 sql
1 Answers

You can try to use operator TRANSFORM.

TRANSFORM <aggregate-function-expression>
  <select-statement>
PIVOT <expression>
[IN (<column-value-list>)]

where <aggregate-function-expression> is an expression created with one of the aggregate functions,

<select-statement> contains a GROUP BY clause, <column-value-list> is a list of required values expected to be returned by the PIVOT expression, enclosed in quotes and separated by commas. (You can use the IN clause to force the output sequence of the columns.)

If there's one event_date's value to RecordID and Stage:

TRANSFORM Max(Audit_Log.Event_Date)AS stage_date
SELECT Audit_Log.RecordID
FROM Audit_Log
GROUP BY Audit_Log.RecordID
PIVOT Audit_Log.Stage
user_139998580
Related