Create report in Access

[AmanKaur123]

Hi All



Please see attached the workbook . The Data tab stores data from the Access table and the Result tab show the report I want to create in Access. I have created the report which is shown in "result" tab using queries and Pivot tables and lookup statements but now sure how similar report can be created in Access?

I want the report to be divided into Department , Mandatory and Non mandatory questions and then total scoring for each question.
Score 1 represents "Yes", Score 2 represents "No" and Score 3 represents "N/A" and SCore 0 means unanswered questions.

Many Thanks

[CJ_London]

you would use queries in much the same way, crosstab instead of pivot and lookups, probably dlookup instead of vlookup

Note that reports in access have their own grouping and sorting options, so little point in sorting in your report recordsource query

I would expect you to have a table to contain 'AA New Business", BOI New business' etc to provide the headings down the side and left joined to your other data so it will still produce a null value.

If you are not aware, you can use the format property of a control to display text rather than a null for a number e.g. something like

#%;#%;"0%"

[AmanKaur123]

Thanks Ajax but can you please help me to do it? I haven't created reports before in Access and this report is going to be challenge for me. The query I have written is as below that will display data e.g data in "Data tab"

SELECT tblAuditStandard.AuditID, tblAuditStandardLibrary.StandardDocs, tblAuditStandard.Score, tblAuditStandard.Action, tblAuditStandardLibrary.StandardID, tblAuditStandardLibrary.StandardType, tblAuditStandard.Flag, tblAuditStandardLink.CallTypeID, tblAuditStandardLink.SubTypeID, tblAuditStandardLink.ChannelID, tblAuditStandardLink.DirectionID
FROM (tblAuditStandardLink INNER JOIN tblAuditStandardLibrary ON tblAuditStandardLink.StandardID = tblAuditStandardLibrary.StandardID) INNER JOIN tblAuditStandard ON tblAuditStandardLibrary.StandardID = tblAuditStandard.StandardID;

[CJ_London]

sorry I don't have the time at the moment, suggest make a start, get the data into the rows you require using a crosstab query. No idea wat bringing through all the id's is supposed to do

[AmanKaur123]

Please see attached the workbook. I have now written a cross table query and the result is showing in Data tab. How can this be displayed like "Result tab" in the report?I need to make few tweaks here . ANy help will be much appreciated. Thanks

[AmanKaur123]

Also can I have two fields set as column headers? I am not able to do that. I want Score and Standard type to be column headers.

[CJ_London]

with respect, I can only help if you provide an access db

I don't understand the second question. it may be that the data in your table is not stored in a way that access can work with. Or it may be you need 2 or more crosstab queries which you can join together

[AmanKaur123]

Ajax, I cant upload access database because of security restrictions at work. But I have created the Access report as attached. Is there any way I can display percentage of Yes,NO and NA questions in the Next columns? So basically some calculations in the report. %NA=NA/(NA+NO+Yes) . How this can be done?

Thanks

[CJ_London]

What are the 'Next columns'?

In principle yes - as you have said you would need to count the number of yes/no/na values plus the total then divide each into the total

But can't advise any more than that - you are effectively asking me to repair a bus but providing a car.

[AmanKaur123]

This is my crosstabl query:

TRANSFORM Count(qryCallAuditReport_New.AuditID) AS CountOfAuditID
SELECT qryCallAuditReport_New.[Call Type], qryCallAuditReport_New.SignOffType, Count(qryCallAuditReport_New.AuditID) AS [Total Of AuditID]
FROM qryCallAuditReport_New
GROUP BY qryCallAuditReport_New.[Call Type], qryCallAuditReport_New.SignOffType
PIVOT IIf([Score]=1,"Yes",IIf([score]=2,"No","NA"));

When I run it then for some of the records The value of "No" and "NA" and "Yes" columns is blank. Please see attached the "Data tab" in the attachment. So based on that the Report gives blanks for those records. Any way I can replace blanks with 0's ? Thanks

[CJ_London]

try for your value column

CountOfAuditID: Nz(Count(qryCallAuditReport_New.AuditID),0)

which put zeros in the blank fields