Hello,
Forgive me for jumping right in without a real introduction. I am at the end of my rope with regard to this problem I'm having, so I apologize if I sound desperate. I am working on a database as a side project for a volunteer position I have with my local Sheriff's department. The purpose of this database is to store records pertaining to cases and includes many fields, such the name of the examiner, the evidence receipt number, offense, date of report, etc. so that at the end of the year, they have ready access to important stats on the type of work they have done. I have only been working with Access for a few weeks now, but I have learned a lot in that time about creating forms, using macros (very little VBA knowledge), building queries and so on.
I want to be able to generate a report which uses around 40-50 calculated fields with the Count and Sum functions to aggregate data from selected fields for a specified date range. The control source for this report includes a number of different queries. So to test, I created a few text boxes which I turned into calculated fields to count number of cases, sum total items of evidence and total tasks performed for various charge categories (eg Homicide, Robbery, etc.). My problem is that when I have more than one query as a basis for the report's control, I get fields which contain various multiples of the actual data, and this multiple increases the larger the date range becomes. For example, for robbery cases, I have 1 record with 1 item of evidence and 2 tasks for a given period. However, when I expand the date range past a certain point, it multiplies the results by 2, 3, 4 and on. And it seems that results based on different queries are multiplied by different amounts (in other words not all results are multiplied by 2, some are multiplied by 4 or 6 for the same date range) I have no idea what's going on here and I can't find anything specific to this problem. Everything else about my database works perfectly so this is really frustrating me.
I hope this wasn't too long and if you need any clarification, I would be happy to provide it.