Access Report using cross tab queries 1

[debbiecarvery]

I would like to have:

rep Deposits balance qual confir sale contract
John 100.00 50.00 1 3 2 1
Mark 100.00 0.00 5 2 1 2

I have tried using a crosstab query and I am able to get the information I need on a regular query and a crosstab query. When I try to use info from both of these queries I am getting an error. Anyone with any ideas how to do this differently or how I can make this work ?

Thanks
Debbie

 

[debbiecarvery]

OOps ! Not sure where the rest of my message went.Here is my original post.

I am using Access 2000

I need a report that calculates numbers for salesmen and telemarketers statics.

I would like to see, number of sales, cancellations, renewels etc. All these fields work fine and I am able to calculate them in a query and add them to a report.

I have a field where the person who enters the data entes a choice in the field : confirmed, sale or contract. I would need these items as headings added to the headings I already have.

e.g
Say I had :

rep Deposits balance qual
John 100.00 50.00 1
Mark 100.00 0.00 5



I would like to have:

rep Deposits balance qual confir sale contract
John 100.00 50.00 1 3 2 1
Mark 100.00 0.00 5 2 1 2

I have tried using a crosstab query and I am able to get the information I need on a regular query and a crosstab query. When I try to use info from both of these queries I am getting an error. Anyone with any ideas how to do this differently or how I can make this work ?

Thanks
Debbie

 

[danvlas]

Not sure it will work, but it's worth a try:

Select SQ.Deposits, SQ.Balance, SQ.qual, CT.* From SQ Inner Join CT On SQ.rep = CT.rep;

SQ=Select query name
CT=crosstab query name

If rep is not primary key in the source table, you should consider replacing it with the primary key in both queries, add the rep field in the crosstab only and change the join to:

SQ.PkField = CT.PKField

Good luck


Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[MichaelRed]

To assure that all info is present in the final query, the Join should MUST include ALL of the piviot fields' possible entries (from BOTH source tables). There are a few ways to accomplish this.

First, simplest but has some reliability issues, is to use the headings property in the crosstab query. This can FORCE the columns to exist, exen if the underlying table/query (recordsource) does not include them. The reliability issue is that this is a bit 'obscure', so it is not obvious and when the data changes, it may not get updated, thus resulting in a filtering of the data (excluding the new entry).

A (IMO) better approach is to extract the UNIQUE set of column headings from the union of the data set and a simple list type of table and use this to generate an additional table which is used as the (Left) outter join of the remaining recordset sources of the crosstab query.

But, (finally) to the issue originally posted, I did not note the actual error condition in your post, so am not able to reply specifically to that. If you could post a more detailed explination of the problem more (specific help) might be available.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[debbiecarvery]

This is the error I am getting from the REport Wizard, when I try to chose fields from my 2 queries. One is a regular query the other is a cross tab based on the original query.

You have chosen fields from record sources which the wizard can’t connect. You may have chosen fields from a table and from a query based on that table. If so, try choosing fields from only the table or only the query

Thanks
Debbie

 

[danvlas]

Sometimes, 'normal humans' are better than the wizards.
Send me a mdb file containing just the queries and necessary table(s) and I'll see what I can do.
Befor sending, delete all records from the table and zip the file. An additional txt file with what you need exactly would be great.

Have a nice weekend (I will for sure)

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[debbiecarvery]

I was able to add info from my original query to my crosstab query. All is well "well kinda". I just added some new questions about this report on Oct 3.Thanks for your response.

Thanks
Debbie