Individual as well as overall averages problem

[ptaylor-west]

I posted this previously in reports but it is more liely a query problem.

I have created 2 queries from the same table.

The first allows me to run information by entering a Company Name "Like[Enter Company Name]" The results displays all the information for that particular Company Name and all the Salesmen in that company.... fine so far.

The second is simply the same information grouped by average of the same information as used in the first query but without Company Name or Salesmen or Salesman fields, so it gives me just one line of the overall averages... still fine.

I am then attempting to produce a report from the two queries, so that I have information by a selected Company Name and their Salesmen, which I also want to summarise for the average of the Salesmen overall... this is no problem on its own, however before I even get that far if I try to add the second query I get an error message sayinh that I am trying to access information from the same table/query and will not run.

My objective for the second query is so that it gives a constant average for all Company Names so that the selected Company Name can be compared to others.

At that point HELP!

 

[GabrielR]

You will have to create 2 different reports, one for the company info and the other for the Average info.

In the Report footer of the first report insert the 2nd report. so you will always see the individual company info and the average as only one report...

 

[ptaylor-west]

A sort of a sub-report, similar to a sub-form... I didn't know you could do that.

Thanks for the advice.

 

[GabrielR]

Yes ptaylor-west, you got it, play with it and let us know if you run into questions...

 

[GabrielR]

See Attached Sample. Open the first report in design view, there are really 2 reports in there based on 2 different queries on the same table.

At the end where you see the report footer, there is a bar... that is the 2nd report embebed in the 1st.

 

[ptaylor-west]

I see the horizontal scrolling bar in design view but it doesn't display the summary in normal view, it also doesn't appear in the footer in the print out (which I thought it would).

 

[GabrielR]

I see the horizontal scrolling bar in design view but it doesn't display the summary in normal view, it also doesn't appear in the footer in the print out (which I thought it would).

Yes it does appear, actually is the last page of the report. Page 12 in the first time out of field report is another report (Summary) based on a different query.

Go in design view and expand the bar, there you will see the report.

 

[ptaylor-west]

Aha! all sorted and it works fine, many thanks for your help it is greatly appreciated.