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 "Data" table . the table has 22 columns , but for report generation I am using only few columns as filtering criteria ( Visit Date, Gender ,Age Group, Group ,Visit Type and Diagnosis).
when you look at the report form you will notice its a matrix table composed of diagnosis columns and 12 other columns comprising the criteria for filtering, and also 2 date textboxes for start and end date and a command button to trigger the counting process. I want you help in :
1- report generation by counting the number of each diagnosis against each criteria and display the result in the specified textbox ( for example , the No of malaria cases who are " GPOC", :Male" ,"New Visit",">=5" . and finally calculate the total at the end (See report).
Unfortunately, your screenshot didn't survive the upload. We need to see the table, with some sample data in it, as well as the desire report layout.
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. OIf 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.
this is a sample of what I am working on right now
one thing , I need the report layout to be exactly like what is on this screenshot , of course with the rest of the diagnosis (22 diagnosis)
