Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2004
    Posts
    361

    Unanswered: Major Report Vs Query Issue

    I have a query that spits out a 14 by 14 style matrix in a report. The query works but I had to make it a make table query so the report would accept it. I have the same situation with another query and report. My problems is I need to sum the numbers on both of those queries to fill another matrix like report. I tried to add all the functions from both queries but it says the query is to complex. Do you guys have any ideas on how I can do this without having to wait 10 minutes for both reports to query and run? Thanks!

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    GAh.... what kind of table structure are you running?

    What does your query look like?

    Sounds like you're making it hard on yourself...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Mar 2004
    Posts
    361
    I'm not really sure what you mean by table structure. Here is an example of what on of the row functions look like.

    CountInv: IIf([Unit Status]="4" And [o_maty] Between #8/1/2003# And #8/31/2003#,1,IIf([Unit Status]="4" And [o_maty] Between #9/1/2003# And #9/30/2003#,2,IIf([Unit Status]="4" And [o_maty] Between #10/1/2003# And #10/31/2003#,3,IIf([Unit Status]="4" And [o_maty] Between #11/1/2003# And #11/30/2003#,4,IIf([Unit Status]="4" And [o_maty] Between #12/1/2003# And #12/31/2003#,5,IIf([Unit Status]="4" And [o_maty] Between #1/1/2004# And #1/31/2004#,6,IIf([Unit Status]="4" And [o_maty] Between #2/1/2004# And #2/29/2004#,7,IIf([Unit Status]="4" And [o_maty] Between #3/1/2004# And #3/31/2004#,8,IIf([Unit Status]="4" And [o_maty] Between #4/1/2004# And #4/30/2004#,9,IIf([Unit Status]="4" And [o_maty] Between #5/1/2004# And #5/31/2004#,10,IIf([Unit Status]="4" And [Date_in_inv] Between #6/1/2004# And #6/30/2004#,11,IIf([Unit Status]="4" And [o_maty] Between #7/1/2004# And #7/30/2004#,12))))))))))))

    I have 14 rows per matrix. Maybe this will help?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    if you have to run 12 nested iif statements to return one value, it's a good bet that you have an issue with your schema.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Mar 2004
    Posts
    361
    I probably have a lot of issues. Could you tell me how to get at value into a report off of two different tables. Right now I have this function.


    =Sum(IIf([CountInv]=10,1,0)). The record source is the table I am calling. What I need to do is write that same function but get the value off of a different table. If I could sum[countInv] from 2 different tables, it would work. Could someone help me with the syntax.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    So you have the same field on 2 different tables?

    Why???

    You may be able to use a union query if your two tables are identicle. Of course, if both tables are identicle, then they should not be seperate tables.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Mar 2004
    Posts
    361
    The reason why there are two like columns in two tables is because I wanted to reuse most of the function I have writen. What I may do is just create a query with both tables in it. Do a count function in the query and a sum in the report.

  8. #8
    Join Date
    Mar 2004
    Posts
    361
    Actually this is what i really need. If I leave the record source blank and call the values from the table itself. Hopefully like this.

    =Sum(IIf([tbl Page 5 Summary ET]![CountInv]=10,1,0))
    =Sum(IIf([tbl Page 5 Summary EET]![CountInv]=10,1,0))

    I tried these but I get an error. I think its just a matter of syntax. Does anyone know how to write this type of function?

  9. #9
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    tjarvas

    Part of my data base runs a telemarketing sytem and it is built around 20 categories of prospect and a group of insurance agents. The data base creates a whole range of statistical data for each category. There is just no way that this can be pulled together on a query and then display in a form.

    So what I do is as follows.

    I have a form made (very rough looking as it is not seen) for each category and that form shows the results. Thus there are 20 such forms.

    I then have another form that is that is based on a table that that has only one record and heaps of blank fields.

    When we want the results a macro does the following:

    Opens the form base on one record.

    It then opens one of the 20 forms then a SetValue action to the appropriate text box on the form with one record and then it closes the 1 of 20 forms.

    It then goes though the process for the other 19 forms.

    Mike

  10. #10
    Join Date
    Mar 2004
    Posts
    361
    I just had an idea. Is it possible to write a function that sums the value from two different reports. Maybe when the user launches this report, the two other reports could be launched to get their values. I don't want to user to see those two reports though. I just need them to contain the number so I can write something like =sum[Report1][txtMvpAug] + [Report2][txtMvpAug]. Is that a possibility?

  11. #11
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    If you open and close Forms/reports with Echo No you won't see anything. You can also give such forms zero height and zero width and no border.

  12. #12
    Join Date
    Mar 2004
    Posts
    361
    Do you have the syntax of how to write a function that adds numbers off of two different reports?

  13. #13
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    No...and I don't need it because with a SetValue action the data is transferred from the box on one form to the other. However, for that to be done, at least on Access 95, the value being transferred has to be either:

    1) A query calculation. The reason is that the SetValue action will run before a text box with a calculation will have its answer and so the SetValue action transfers a null value. Thjis does not happen when the value being transferred isa query calculation.

    2) The SetValue action is placed on the timer, say a couple of seconds and then macro closes the form after doing the SetValue.

    Mike

  14. #14
    Join Date
    Mar 2004
    Posts
    361
    I'm sorry, I think that you misunderstood me. I took the idea from a previous post and decided that I would launch both reports and then add the values from those onto the new one. That is the function that I am looking for. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •