Forum Discussion
Report Generation
Dear Experts , I am Kind new to access I Have been working on this project for few weeks now and I am stuck . as you will see from the attached file , I have a data entry form who enters data into...
these are the way the table and the report look like
Thank you.
From the presented data and the sample report, I would suggest a Crosstab query might get close to the format you want. Unfortunately, the data is presented as images, not in a usable format; working with the sample data is a challenge for someone trying to replicate it in an example for you.
However, the row values for the crosstab would be the Diagnosis field and the columns would be based on Age Group. The calculated values, I assume would be Counts of records in each row and column.
The bigger issue is that the task is significantly complicated by the fact that there are four levels of grouping on the Columns:
GPOC
AgeGroup
Gender
"N Visit" and "R Visit"
Not knowing the context beyond what we can see, and not having business rules to decide how those groupings must be defined, and not having sample data to work with is challenging.
I suspect, though, that given the apparent complexity of the report, this might be better handled by export to Excel where you can create Pivot Tables that might handle multiple levels. O
If you'd like to share an accdb with some sample data, along with a narrative of the context and rules for the report, someone could probably help you work out a strategy. Just make sure the sample data is obfuscated to avoid real personal data.
Thank you for your suggestions. i have tried to attach the file ,but it seems to be deleted for some reason however , I have managed to creat a combined query for all 22 diagnosis based on the 12 criteria , when I run it it shows exactly what I want , now when I try to creat a crosstab query out of that combined query , its gives me and error msg that the access database engine cant recognize [ Forms]![Report Generation]![StartDate] as a field name or expression.
(Report generation form is the form with dat controls), while its fully working as its in the combined query.
please find the attached screen shots and the combined query .
I will appreciate any help I get.
thanks
Combined Query:
SELECT Diagnosis, SUM(IIf([Group]="GPOC" AND [Gender]="Female" AND [Age Group]=5 AND [Visit Type]="New Visit" AND [Visit Date] BETWEEN [Forms]![Report Generation]![StartDate] AND [Forms]![Report Generation]![EndDate], 1, 0)) AS [GPOC, Female, =5, New Visit],
SUM(IIf([Group]="GPOC" AND [Gender]="Female" AND [Age Group]=5 AND [Visit Type]="Re-Visit" AND [Visit Date] BETWEEN [Forms]![Report Generation]![StartDate] AND [Forms]![Report Generation]![EndDate], 1, 0)) AS [GPOC, Female, =5, Re-Visit],
SUM(IIf([Group]="Non GPOC" AND [Gender]="Female" AND [Age Group]=4 AND [Visit Type]="New Visit" AND [Visit Date] BETWEEN [Forms]![Report Generation]![StartDate] AND [Forms]![Report Generation]![EndDate], 1, 0)) AS [Non GPOC, Female, =4, New Visit],
SUM(IIf([Group]="Non GPOC" AND [Gender]="Female" AND [Age Group]=4 AND [Visit Type]="Re-Visit" AND [Visit Date] BETWEEN [Forms]![Report Generation]![StartDate] AND [Forms]![Report Generation]![EndDate], 1, 0)) AS [Non GPOC, Female, =4, Re-Visit],
SUM(IIf([Group]="Non GPOC" AND [Gender]="Female" AND [Age Group]=5 AND [Visit Type]="New Visit" AND [Visit Date] BETWEEN [Forms]![Report Generation]![StartDate] AND [Forms]![Report Generation]![EndDate], 1, 0)) AS [Non GPOC, Female, =5, New Visit],
SUM(IIf([Group]="Non GPOC" AND [Gender]="Female" AND [Age Group]=5 AND [Visit Type]="Re-Visit" AND [Visit Date] BETWEEN [Forms]![Report Generation]![StartDate] AND [Forms]![Report Generation]![EndDate], 1, 0)) AS [Non GPOC, Female, =5, Re-Visit],
SUM(IIf([Group]="GPOC" AND [Gender]="Male" AND [Age Group]=5 AND [Visit Type]="New Visit" AND [Visit Date] BETWEEN [Forms]![Report Generation]![StartDate] AND [Forms]![Report Generation]![EndDate], 1, 0)) AS [GPOC, Male, =5, New Visit],
SUM(IIf([Group]="GPOC" AND [Gender]="Male" AND [Age Group]=5 AND [Visit Type]="Re-Visit" AND [Visit Date] BETWEEN [Forms]![Report Generation]![StartDate] AND [Forms]![Report Generation]![EndDate], 1, 0)) AS [GPOC, Male, =5, Re-Visit],
SUM(IIf([Group]="Non GPOC" AND [Gender]="Male" AND [Age Group]=4 AND [Visit Type]="New Visit" AND [Visit Date] BETWEEN [Forms]![Report Generation]![StartDate] AND [Forms]![Report Generation]![EndDate], 1, 0)) AS [Non GPOC, Male, =4, New Visit],
SUM(IIf([Group]="Non GPOC" AND [Gender]="Male" AND [Age Group]=4 AND [Visit Type]="Re-Visit" AND [Visit Date] BETWEEN [Forms]![Report Generation]![StartDate] AND [Forms]![Report Generation]![EndDate], 1, 0)) AS [Non GPOC, Male, =4, Re-Visit],
SUM(IIf([Group]="Non GPOC" AND [Gender]="Male" AND [Age Group]=5 AND [Visit Type]="New Visit" AND [Visit Date] BETWEEN [Forms]![Report Generation]![StartDate] AND [Forms]![Report Generation]![EndDate], 1, 0)) AS [Non GPOC, Male, =5, New Visit],
SUM(IIf([Group]="Non GPOC" AND [Gender]="Male" AND [Age Group]=5 AND [Visit Type]="Re-Visit" AND [Visit Date] BETWEEN [Forms]![Report Generation]![StartDate] AND [Forms]![Report Generation]![EndDate], 1, 0)) AS [Non GPOC, Male, =5, Re-Visit], SUM(
IIf([Group]="GPOC" AND [Gender]="Female" AND [Age Group]=5 AND [Visit Type]="New Visit" AND [Visit Date] BETWEEN [Forms]![Report Generation]![StartDate] AND [Forms]![Report Generation]![EndDate], 1, 0) +
IIf([Group]="GPOC" AND [Gender]="Female" AND [Age Group]=5 AND [Visit Type]="Re-Visit" AND [Visit Date] BETWEEN [Forms]![Report Generation]![StartDate] AND [Forms]![Report Generation]![EndDate], 1, 0) +
IIf([Group]="Non GPOC" AND [Gender]="Female" AND [Age Group]=4 AND [Visit Type]="New Visit" AND [Visit Date] BETWEEN [Forms]![Report Generation]![StartDate] AND [Forms]![Report Generation]![EndDate], 1, 0) +
IIf([Group]="Non GPOC" AND [Gender]="Female" AND [Age Group]=4 AND [Visit Type]="Re-Visit" AND [Visit Date] BETWEEN [Forms]![Report Generation]![StartDate] AND [Forms]![Report Generation]![EndDate], 1, 0) +
IIf([Group]="Non GPOC" AND [Gender]="Female" AND [Age Group]=5 AND [Visit Type]="New Visit" AND [Visit Date] BETWEEN [Forms]![Report Generation]![StartDate] AND [Forms]![Report Generation]![EndDate], 1, 0) +
IIf([Group]="Non GPOC" AND [Gender]="Female" AND [Age Group]=5 AND [Visit Type]="Re-Visit" AND [Visit Date] BETWEEN [Forms]![Report Generation]![StartDate] AND [Forms]![Report Generation]![EndDate], 1, 0) +
IIf([Group]="GPOC" AND [Gender]="Male" AND [Age Group]=5 AND [Visit Type]="New Visit" AND [Visit Date] BETWEEN [Forms]![Report Generation]![StartDate] AND [Forms]![Report Generation]![EndDate], 1, 0) +
IIf([Group]="GPOC" AND [Gender]="Male" AND [Age Group]=5 AND [Visit Type]="Re-Visit" AND [Visit Date] BETWEEN [Forms]![Report Generation]![StartDate] AND [Forms]![Report Generation]![EndDate], 1, 0) +
IIf([Group]="Non GPOC" AND [Gender]="Male" AND [Age Group]=4 AND [Visit Type]="New Visit" AND [Visit Date] BETWEEN [Forms]![Report Generation]![StartDate] AND [Forms]![Report Generation]![EndDate], 1, 0) +
IIf([Group]="Non GPOC" AND [Gender]="Male" AND [Age Group]=4 AND [Visit Type]="Re-Visit" AND [Visit Date] BETWEEN [Forms]![Report Generation]![StartDate] AND [Forms]![Report Generation]![EndDate], 1, 0) +
IIf([Group]="Non GPOC" AND [Gender]="Male" AND [Age Group]=5 AND [Visit Type]="New Visit" AND [Visit Date] BETWEEN [Forms]![Report Generation]![StartDate] AND [Forms]![Report Generation]![EndDate], 1, 0) +
IIf([Group]="Non GPOC" AND [Gender]="Male" AND [Age Group]=5 AND [Visit Type]="Re-Visit" AND [Visit Date] BETWEEN [Forms]![Report Generation]![StartDate] AND [Forms]![Report Generation]![EndDate], 1, 0)
) AS Total
FROM Data
WHERE Diagnosis IN ("Malaria", "URTI/LRTI", "Typhoid Fever", "AWD", "Dysentery", "Skin diseases", "Eye diseases", "STDs", "UTI", "Asthma", "PUD", "Arthritis", "Hypertension", "Diabetes", "Injuries & Accidents", "Burn", "CO Poisoning", "Others")
GROUP BY Diagnosis;attached is a screenshot of my table and the other screenshot is when I run the above code after I enter the date range in report generation form.
Actually, when I asked for an accdb with sample data, I was hoping to avoid screenshots.
I tried many times , but it seems it deleted every time . I don’t if you could provide an email to forward it to to you
