Results 1 to 13 of 13
  1. #1
    Neologic29 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2013
    Posts
    13

    Having issues with calculated fields in reports

    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.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Queries with joins can easily contain multiples of the same record depending on the Join. This is a bit confusing:
    The control source for this report includes a number of different queries.

  3. #3
    Neologic29 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2013
    Posts
    13
    What I meant by that part is that in the report, on the data tab I went to the control source line and used the ellipses button to bring up the query builder for that report. There, I pulled up each query and selected the fields I needed from each one. It was my understanding that this was possible. Now the fields are all the same across the various queries, but they do not contain duplicate records as they are based on different criteria for the Charge field (i.e. homicide query looks for records with 187 PC and 189 PC only whereas robbery looks for records with 211 PC only.) I hope that helps a little. I really appreciate any advice or direction.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Save the Query and it will become static. Then open it in design view and post a picture of the design and maybe the SQL view code as well. It is possible to have a query that includes several tables but they need to have a relationship. Do they?

  5. #5
    Neologic29 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2013
    Posts
    13
    Click image for larger version. 

Name:	AccessQueryDesign.jpg 
Views:	9 
Size:	80.5 KB 
ID:	13474

    Here is the Design View of the query for Robbery cases. The design of the other queries is identical except for the Criteria on the Charge field.

    Click image for larger version. 

Name:	AccessSQLview.JPG 
Views:	8 
Size:	78.5 KB 
ID:	13475

    Here is the SQL code for this query

    Now, I want to point out that when I run the queries directly, I have no issues with getting correct totals using the sum and count on each field. I have the totals built into the Datasheet view, not in the design view (Which I don't think makes a difference). I only start having problems when trying to use a calculated field in a report which calls on the related query and field. Hopefully this sheds some light. Thanks again for your help. The queries are based off of a single table with multiple fields. Maybe I overlooked something, but I couldn't think of a reason why there would need to be more than one main table. The main table has an id field as the primary key, a field for examiner, lab receipt number, charge, date, and 12 or so other fields dealing with the types of tasks performed by this section of the crime lab.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    If this is also the RecordSource for your Report then the Form needs to remain open, correct? You keep referring to the plural "queries" when this is only a single query, which is confusing.

  7. #7
    Neologic29 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2013
    Posts
    13
    That query is only one of many from which I need to pull field calculations, that was just an example. Unless there is a way to use one query that uses a parameter that the user enters, I don't see how else to get the figures I need into the report. I assumed that the record source for the report needed to include all but the last two queries you can see in the navigation panel in the first picture I posted. In order to be able to reference those queries using calculated controls (text boxes), I thought that the report needed all of those queries as a record source.

    Maybe I'm trying to reinvent the wheel here but it seems like what I want to do is possible, because they work individually, there is just something jamming up the calculations when the report is trying to pull from more than one query at a time.

    Click image for larger version. 

Name:	reportquerybuilder.jpg 
Views:	7 
Size:	79.0 KB 
ID:	13476

    Here is a look at the query builder which comes up when I use the ellipses button on the Record Source line of the report. You can see there are two queries which I'm using, but I was thinking I needed all of the relevant queries to my report included there.

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    I believe you have a misunderstanding of the RecordSource of a Report. It can *only* contain one query/table. I'm not sure what you are doing.

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Just so you know, having two queries/tables in a query without there being a relationship or join creates a Cartesian Product.

  10. #10
    Neologic29 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2013
    Posts
    13
    I was afraid there was something I was missing and it seems like that is it. So I must have misread when I was looking up how to set the record source for a report. If I join the queries, would that mean I could then use them in combination as the record source? (assuming they were joined properly, of course)

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    The simple answer to your question is YES.

  12. #12
    Neologic29 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2013
    Posts
    13
    Ok, thank you. I think my lack of understanding was causing the confusion and for that, I'm sorry. I will consider this solved because I at least know now that I can't do what I was trying to do and I know what I can do to fix it.

  13. #13
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Great! Start a new thread if you run into further issues with your new approach.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 11-20-2012, 11:14 PM
  2. Picture issues within Access reports
    By paulkimball in forum Access
    Replies: 0
    Last Post: 05-14-2012, 07:55 PM
  3. Calculated fields
    By FinChase in forum Queries
    Replies: 5
    Last Post: 02-02-2012, 06:12 PM
  4. Replies: 5
    Last Post: 01-18-2012, 03:46 PM
  5. Sum of calculated fields
    By nkuebelbeck in forum Forms
    Replies: 9
    Last Post: 03-12-2010, 01:32 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums