Fixed query results layout, is this possible? (1 Viewer)

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

Hi Stuart - I'm not a guru when it comes to pivot statements, but my guess would be that you have your unexpected zeros because of this line:


The Nz() function is a "NullToZero" function, which takes your count of dates, and if it's null, replaces the null with a zero.

That said, you may want to consider putting the Nz() function around the field itself rather than the count function (or maybe not, I forget it's behavior in this scenario offhand):


See if that gives you different results, and which would be preferred.

Cheers,

Well it's difficult to say without more information but I would assume that if you return all of the results without criteria then you would see them?

Sent from my SM-G925F using Tapatalk

You need a table that lists all the row values you want to see. Then using a left join, join the list table with the crosstab. Select the row value from the list table and the column values from the crosstab.

Thanks for the responses everyone, appreciate the help I have been playing around with the suggestions but not had any success as of yet.

Pat Hartman said:

You need a table that lists all the row values you want to see. Then using a left join, join the list table with the crosstab. Select the row value from the list table and the column values from the crosstab.

Click to expand...

I have created a list table and connected it to the table within the crosstab with a left join. When I run the query it is still not showing all the channel types. Have I done this correctly?

TRANSFORM Nz(Count([channels]),0) AS [New Audits]
SELECT tblcha.Channels
FROM tblcha LEFT JOIN DBAuditLog ON tblcha.Channels = DBAuditLog.Channel
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 tblcha.Channels
PIVOT DBAuditLog.[Entity Type] In ("Type A","Type B","Type C");


Thanks,
Stuart

You can't do it withing the crosstab query because the crosstab specifically eliminates rows that don't have data. That is why I said to create a NEW query that joins the list table to the crosstab query. This lets the crosstab do its thing and then after the fact, you get it to add back the missing rows.

Pat Hartman said:

You can't do it withing the crosstab query because the crosstab specifically eliminates rows that don't have data. That is why I said to create a NEW query that joins the list table to the crosstab query. This lets the crosstab do its thing and then after the fact, you get it to add back the missing rows.

Click to expand...

Sorry I misunderstood, I have followed your instructions correctly this time and it is now working a treat

Thanks very much Pat much appreciated!

No problem. I should have used the word "new" the first time. But you learned a little about how the query engine works though and how you can impose your will on Access.