Unanswered: Help with Reporting Average Age: Access 2007
I have been googling for days and cannot seem to figure this out, (i am also a vba/sql novice) so any thoughts or suggestions is very appreciated!
What the database is used for:
-data for a research study
Object I am working on:
-cannot get the average age to report correctly using a dynamic query in an SQL statement.
Old method of reporting:
-Reporting of data is broken down by year (i.e. in year 1, there were this many clients, in year 2 this many, etc.) the old VBA code to make the report included static dates. (I.e. if the client was in the study from 9/1/2009 until 8/31/2010 include them in this report).
New method of reporting:
-Since the study will continue for seveal years, and the report objets are quite large and take up a lot of memory, I wanted to make the code dynamic, so that we only need one reporting object
- I created a Form where database users can select the Year they would like to run the report, and the start dates and end dates of that year are then automatically copied into two unbound controls on that form.
-Then those dates in the unbound controls become the parameters for which the data is pullled and reported on.
-This works great for all the data reported on except for calculating the average AGE.
-The average age code is a little different because it pulls data from a query, which also filters data based on the specific reporting year dates.
Here is what part of the code looks like:
'calculate average age
'first query ages of all women who meet criteria (not rescreened case, etc.)
sql = "SELECT Round((Avg([qryAllAges]![Age])),0) AS AvgAge FROM qryAllAges"
-This and the rest of the code work perfectly when the query criteria for dates in the qryAllAges are static: i.e. >= #9/1/2009# and <= #8/31/2010#
-BUt i changed the criteria so the date criteria is based on what is in those 2 unbound controls:
>=[Forms]![frmannualReports]![txtStartDate2] And <=[forms]![frmAnnualreports]![txtEndDate2]
-The query by itself works fine. When I double click on it, it will only pull the ages of clients between the dates in the unbound controls-
-However, the reporting of the numbers get all wacky
-For instance, there are only 8 clients in the database, but the number of clients reported on becomes 20, and the average age is definately not accurate...
-also the age data is not the only data to get messed up, any data reported underneath it (i.e. race, gender, etc,) become all wacky as well.
-If it helps I can attach screen shots of the two reports (original and current), but since I have already written a novel, i thought i would spear everyone!
-Also in my googling I found something that says SQL doesn't "like" using queries whose criteria is based on controls in a form, so this might be the probelm?
-should I be using a completely different method?
Thanks for any help in advance! And sorry for the long winded explanation!!
Are the ages calculated? What information is stored? The birth Date or the Actual age of the participant?
If you have any doubts about the query working with the unbound form fields. One thing you could do to test it is to create a table with the start and end dates. What I typically do for reports is create a parameter table where I store start and end dates, uesernames, etc.
The table would have only one record with two fields (StartDate, EndDate). Then in the query you can add a reference to the table and filter for Between StartDate And EndDate.
If you get the same results then its probably not because of the reference to the form. And if it fixes it, then you can write soem code to take the StartDate and EndDate from the form and save it to the table.
To answer your question: The "qryAllAges" is pulling stored Age data (birthday is entered as well, and there is validation code in place to make sure that the age, birthdate and visit date all coordinate) from a table called tblscreenings.
The reason for the query is to pull only values that are not null, don't equal -99 (as that implies missing data) and where there is actually a client ID (and not just a random empty record)...Basically to get only "good" data before teh ages are averaged.
Here is what the query code looks like (in case it helps):
FROM tblClients LEFT JOIN tblScreenings ON tblClients.UID = tblScreenings.ClientUID
GROUP BY tblScreenings.Age, tblClients.ClientID, tblScreenings.FormDate
HAVING (((tblScreenings.Age)<>99) AND ((tblClients.ClientID) Is Not Null) AND ((tblScreenings.FormDate)>=[Forms]![frmAnnualReports]![txtStartDate2] And (tblScreenings.FormDate)<=[forms]![frmannualreports]![txtEndDate2]) AND ((Mid([tblClients].[ClientID],13,1))=''));
I tried what you said, I made a table with two columns, start date and end date, and referenced them in the date criteria of the query, and unfortunately I got the same results.
Since the query itself seems to be working alone, I thought that maybe if I could make the query avg the ages BEFORE the sql statement, and just use that avg value so I don't have to use the date criteria in the sql statement....
So, in the totals row, in the "Age" field I selected the Avg from the dropdown.
-However, it won't average all of the clients ages...i think it's grouping the data by the client ID, so if a client had two ages for example, it would average those two ages, but I need it to average ALL ages for ALL clients..Any ideas of how to do that?? Or any other thoughts??