Hi everyone,
I am new to the forum and also the world of Access in general! I have been asked to create a query and have hit a problem I was hoping you could help me with.
In short I need to create a query with a fixed result layout, e.g. fixed column and row headers.
Column headers:
Type A
Type B
Type C
Row headers:
Source 1
Source 2
Source 3
Source 4
Source 5
Source 6
Source 7
Source 8
The query generates results if activity from a source was present in the previous month. I have attached an image of my current results.
Sources 1-4 had activity last month and have therefore generated results. However Sources 5-8 had no activity last month so do not display in the results.
I need the rows for Sources 5-8 to appear in the results with zero's even though no activity took place. Is this possible?
The code I have so far is:
TRANSFORM Nz(Count([Audit initiation date]),0) AS [New Audits]
SELECT DBAuditLog.Channel
FROM DBAuditLog
WHERE (((DBAuditLog.[Audit initiation date]) Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)) AND ((DBAuditLog.[Audit Occurance])="new"))
GROUP BY DBAuditLog.Channel
PIVOT DBAuditLog.[Entity Type] In ("Type A","Type B","Type C");
Would really appreciate any help
Thanks,
Stuart
I am new to the forum and also the world of Access in general! I have been asked to create a query and have hit a problem I was hoping you could help me with.
In short I need to create a query with a fixed result layout, e.g. fixed column and row headers.
Column headers:
Type A
Type B
Type C
Row headers:
Source 1
Source 2
Source 3
Source 4
Source 5
Source 6
Source 7
Source 8
The query generates results if activity from a source was present in the previous month. I have attached an image of my current results.
Sources 1-4 had activity last month and have therefore generated results. However Sources 5-8 had no activity last month so do not display in the results.
I need the rows for Sources 5-8 to appear in the results with zero's even though no activity took place. Is this possible?
The code I have so far is:
TRANSFORM Nz(Count([Audit initiation date]),0) AS [New Audits]
SELECT DBAuditLog.Channel
FROM DBAuditLog
WHERE (((DBAuditLog.[Audit initiation date]) Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)) AND ((DBAuditLog.[Audit Occurance])="new"))
GROUP BY DBAuditLog.Channel
PIVOT DBAuditLog.[Entity Type] In ("Type A","Type B","Type C");
Would really appreciate any help
Thanks,
Stuart