Monthly Report - Access Developers - Computer Learning Zone


	Back to Access Developers Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Monthly Report

Daniel Golden

2 years ago

I have come to another point in my database where I need help. (Shocker??) I am needing to create a query which I can then use for a report. Ideally this report would be run once a month to show how many of each allegation type were entered for the month. For example: There are four categories in which incidents are entered into the database: "Unit/Center Reviews", "Unit/Center Investigations", "Parole/Probation Reviews", and "Parole/Probation Investigations". For each of these case types there are a whole list of various allegation types. The query and report I am needing to create would sort these allegations by type under each case type.

	Alex Hedley @Reply 2 years ago
Grouping Group Sales By Month

	Alex Hedley @Reply 2 years ago
Is all the data in one Table? Can you use `allegation type` as the value to `Group By`? Is there a Date Field in that Table?

	Daniel Golden OP @Reply 2 years ago
So the allegation type is what I want to group the information by in the report. The data is in 4 separate tables based off either unit/center reviews or investigations as well as parole/probation reviews or investigations.

	Kevin Robertson @Reply 2 years ago
If they are all allegation types, why are they stored in 4 separate Tables? Too Many Tables

Daniel Golden OP @Reply

2 years ago

@ Kevin: They are stored in 4 separate tables as the allegation types are used across 4 different types of investigations we conduct. I have a union query set up now which shows the allegation from each type of investigation but if there is more than one allegation of the same type on the same date it only shows one, rather than how many of that allegation type there is in total... how can I add count to this union query??

	Alex Hedley @Reply 2 years ago
Can you share a simplified SQL Query you are using. It could be dependent on which fields you are pulling in.

Daniel Golden OP @Reply

2 years ago

On the union query it is set up like this:

Details

SELECT Allegation, [Date Received]

FROM [Unit/Center Reviews]



UNION ALL



SELECT Allegation, [Date Received]

FROM [Unit/Center Investigations]



UNION ALL



SELECT Allegation, [Date Received]

FROM [Parole/Probation Reviews]



UNION ALL SELECT Allegation, [Date Received]

FROM [Parole/Probation Investigations]



ORDER BY [Date Received]

I need all of that information to stay but include a count for each allegation type...

	Daniel Golden OP @Reply 2 years ago
Sorry.... I also need the end user to be able to enter a start date for the report and have it gather all that info for the month.... for example: I want to open the report this query will be set to and have it ask me for a start date (say 12/01/2024) with which it will then use to look in all four case information tables and gather all those cases for just the month of December 2024 and so on...

	Alex Hedley @Reply 2 years ago
You can then create another Query and use that original one as the basis, add a Parameter Query from a Form and stick a button on that to open your Report. You can then apply groupings in the Report.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developers.

Next Unseen

New Feature: Comment Live View