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!
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 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.
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.
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.
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?
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.
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.