Report Based on CrossTab Query

[mcktigger]

Hi All

Hoping someone can help with this. I have a quotes table and want to create a report that shows the total number of quotes created, won and lost over a given period per sales person. I thought a cross tab query would work but having trouble with this.

The quotes created would be a count of quotes created based on DateCreated but the quotes won and lost would be based on StatusChangeDate between dd/mm/yyyy and dd/mm/yyyy.

I can get a query that shows total created and can get a query showing won and lost but can't combine this into 1 query as I get repeating rows for the won and lost columns. Also when a try to create my report from the Won and Lost crosstab query (which works fine as a query) no fields are available to be selected for the report.

Could someone point me in the right direction as I've now pulled the last few strands of hair out.

Thanks

 

[vbaInet]

Could someone point me in the right direction as I've now pulled the last few strands of hair out.

Let's hope the last few strands remain after this

I can get a query that shows total created and can get a query showing won and lost but can't combine this into 1 query as I get repeating rows for the won and lost columns.

Do you know about the DISTINCT clause? This is a property of a query. Have a look in the property sheet for Distinct Value.

Also when a try to create my report from the Won and Lost crosstab query (which works fine as a query) no fields are available to be selected for the report.

So you've realised that because your crosstab query is pulling from more than one source, the fields are not present. Also, one of the other reasons is because the column headings change based on the date and a report uses controls that need to be bound to an already existing field. You can display the crosstab query in a subreport control.

 

[mcktigger]

Hi

Thaks for the help, I'll give this a try.