Complex Year-to-Date Report

[MHDataJockey]

Hello, first post so please be gentle.

I've created a database for tracking psychiatric client admissions and the many, many details that go along with that admission because our multi-million dollar medical records system can't provide the information needed to appropriately monitor/manage this psych unit which admits from 400-500 clients each month for up to 23-hour stays each.

I'm creating the database in Access 2007 but it will ultimately be used in Access 2000.

Up to this point staff have been required to keep a hard copy log book and, at the end of each month, go back through the 400-500 people and manually calculate the elements we need to report on.



Setting up the client entry portion of the database was relatively easy. It's extracting the information into a fiscal year-to-date report in a previously established manner (a format we'd like to keep) that I keep running into roadblocks with. I could use some assistance with how to approach the report.

Main table = "tblClientEntry"
There are multiple forms that feed info into this table for different purposes related to managing the client's stay on the unit. All of this info is crammed into each record episodically since a person can be on the unit one day, leave, and come back the very next day and start the process all over again (which would need to be a separate record).

There's basically a 5-page report that results from using the above table (months of the fiscal year across the top with a total of all months at the end and about 150 different categories that are all totals based on different elements of client information).

My hope was that I could use queries to calculate the totals and store them in temporary tables so that a report could simply pull the value from the temporary table. I'll give a few examples of the types of monthly/fiscal year totals that are required out of the 150:

----

Total "Law-Vol" admissions covering people voluntarily being admitted to the unit but were brought in by a law enforcement agency (based off of a table field called "Referred By" populated by a combo box when the entry is "Law Enforcement") and when a field called "Status" has an entry of "Vol", "Con-Vol" or "T-Con".

Total voluntary admissions based on the "Status" field mentioned above with entries that equal "Vol", "Con-Vol" or "T-Con" but any other entry than "Law Enforcement" on the "Referred By" field.

Total hospitalized adults based on DOB and a table field called "Disposition" when the entry is populated with "D/C to.." and the name of one of the 5 inpatient facilities we have at our disposal (populated on a form using a combo box).

Total clients admitted for the month with further breakdowns for admissions during the day shift and during the night shift based on "Date In" and "Time In" table fields.

----

To take just one of these things has been awkward enough when trying to get these monthly/fiscal year totals, but to do this for 150+ different data elements has me about ready to pass out.

I'm not exactly the most confident person when it comes to SQL, but if anyone out there can give some direction for where the heck to start it'd be much appreciated.

Sorry for the long post, and thanks in advance.

[ajetrumpet]

i do not have time to read all of your post, but is the gist of it basically that you need to be able query out a table that has a date field, based on "year to date" values??

because if that's the main issue, here are the formulas for getting that time frame from a date field:

in query form:

SELECT table.mydate
FROM table
WHERE (((table.mydate) BETWEEN 

"1/1/" & DatePart("yyyy",Date()) AND 

Date()));

[MHDataJockey]

i do not have time to read all of your post, but is the gist of it basically that you need to be able query out a table that has a date field, based on "year to date" values??

because if that's the main issue, here are the formulas for getting that time frame from a date field:

in query form:

SELECT table.mydate
FROM table
WHERE (((table.mydate) BETWEEN 
 
"1/1/" & DatePart("yyyy",Date()) AND 
 
Date()));

I gave a few examples of the types of information I have to pull from the table... I'm kinda going off of the advice that Access is designed to do a good chunk of the work for you, so if you find yourself looking at doing tons and tons of coding, you're probably doing something wrong.

Since I have 150ish different categories that need to be calculated each and every month, I know it won't be a quick task... but I'm not concerned about needing to repeat the same basic task 149 more times. Problem is I've tried a few different approaches with queries and can't seem to find one that takes me all the way home.

I'm thinking my logic with the temp tables is flawed, so if anyone out there has a better approach it'd be much appreciated.

Edit: A colleague mentioned my current setup would require having a separate query for each category and for each month, so 1800 queries seems just a tad excessive. :P