* HELP IN A CROSSTAB QUERY *

[kp1279]

I have created a crosstab query, and it works ok.

I have the following:

Targets: which is an area the reps need to address
Notes: Which are notes for the targets
Source: which are the reps ID numbers
asource1: which is the paper report the rep completes

The code for the query is: [blue]

TRANSFORM Count([rep logs].asource1) AS CountOfasource11
SELECT Targets.Targets, Targets.Notes, Count([rep logs].asource1) AS CountOfasource1
FROM Targets RIGHT JOIN [rep logs] ON Targets.Targets = [rep logs].Targets
GROUP BY Targets.Targets, Targets.Notes
PIVOT [rep logs].Source In (45732,45733,45734,45735,45736,45737,45738,45739,45740);

[/blue]
I have a list of 160 Target areas, but the above only brings me back those targets that have a value in for the source. I want to produce a report which shows those targets that have a value against a rep and those that do not have a value.

On the last line of the code I have put in a few reps source codes, but again I have about 100 of these, but what I would like is only those between a certain number range if that is possible, i.e 45730 - 45750. without having to put in each number as a column heading.

Another little tester, when the report is produced, and it shows me the target on the left column, and the notes in the next column, then the total number of recorded tries (asource1), then a break down of the selected rep fields with each rep showing the number of tries, so it looks something like

[red]

Targets | Notes | Countofasource1 | 45730 | 45731 |
games | test | 30 | 3 | 14 |


etc.
[/red]


After this I want to be able to show from this query how many sources have filed notes on their targets, but not just the amount.

From the above example this would be 2 sources, have filed notes, but I still want the number of notes to be displayed in the results, the score 2 will need to be in a separate column.

I know there is a lot in this query but nay help is apreciated.

 

[dhookom]

To get all the Targets, create another query based on your crosstab and Targets with a join that selects all records from Targets.

You should also be able to set a criteria in your query of
WHERE Source Between xxx And yyy
This won't provide column headings though. To get Column Headings you might need to use code to modify the SQL of the query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kp1279]

I have coded the sources so I get the column headings, and created a query to give me all the targets, and this works ok.

Thanks for your help on this matter.

In relation to the last, can you help with that.

I want to create the report where under the source against the targets it shows me the amount of reports submitted, so from the previous example about games, being the target, rep 45730 has submitted 3 forms, and 45731 submitted 14, which is how I want it displayed, but then against the target I want a new column which shows me the amount of sources who have sumitted reports again the taregt, so for this example there will be 2 sources against the target games.

Hope this makes sense?

 

[dhookom]

To get the "2" for the count, you may need to create either one or more totals queries that calculate the "2". Then add the final query to your existing crosstab.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kp1279]

How would I do this??

So that for each target, I only get those source fields that have a figure in, and not the figure itself.

This sounds very easy as most things do, but to get them is much more difficult.

?????????

 

[dhookom]

First query will get all unique targets and sources:
[qgrpTargetsSources]
SELECT Targets.Targets, [rep logs].Source
FROM Targets RIGHT JOIN [rep logs] ON Targets.Targets = [rep logs].Targets
GROUP BY Targets.Targets, [rep logs].Source;
The next query will count the Sources for each Target:
[qtotTargetsSourceCount]
SELECT Targets, Count(Source) as NumOfSources
FROM qgrpTargetsSources
GROUP BY Targets;

This should get "games" , 2




Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kp1279]

Thanks very much, it worked a treat.

KP