Problem with a crosstab query

[krut]

Hi,

I have two queries. The purpose of the first query is to get all the work orders from the work order table and show the money earned for each month from each work order. The second query (crosstab) takes the numbers from the first query and sums them up also by month. This way I get a total for each month for each sales person. There are 3 work types (CMP, OC, BAR).

My problem is that in the output of the second query I need to always show the saleperson and work type even if the saleperson has not sold anything in that period of timee and there is no work orders to show.

The output of the second query now is like this (Providing that CL hasn't sold any BAR worktypes and DK hasn't sold any OC in the period of time from January to April):

Salesperson | Worktype | TOTAL|Jan | Feb | Mar | Apr | ..........................
CL |CMP | $850 |$500| | |$330|
CL |OC |$850 | |$200| |$650|
DK |BAR |$440 |$440| | | |
DK |CMP |$330 | $200| | |$130|

What I need should look like this:

Salesperson | Worktype | TOTAL|Jan | Feb | Mar | Apr | ..........................
CL |BAR | | | | | |
CL |CMP | $850 |$500| | |$330|
CL |OC |$850 | |$200| |$650|
DK |BAR |$440 |$440| | | |
DK |CMP |$330 | $200| | |$130|
DK |OC | | | | | |

notice that the result that I need is that the query shows BAR for CL and OC for DK eventhough nothing was sold

any ideas how could that result be achieved?

bellow are the two queries I'm using

Thank you,

Alex

fisrt query:

SELECT Econtact.[Contact Number], Econtact.Company, Econtact.Status, [Work Order].[Quote Number], [Work Order].[Site Address Line 1],
[Work Order].[Site Postal Code], [Work Order].[Work Order Number], [Work Order].[WO Total],

[WO Version] & "/" & [WO Total] AS Expr1,
[Work Order].Salesperson, [Work Order].[Second Salesperson], [Work Order].Contractor, [Work Order].[Work Type],
[Net Billed Amount]+[Extra Billed Amount] AS Expr3,
[Work Order].[Scheduled Start],

IIf(Month([Scheduled Start])=5,[Net Billed Amount]+[Extra Billed Amount],0) AS Expr2,
IIf(Month([Scheduled Start])=6,[Net Billed Amount]+[Extra Billed Amount],0) AS Expr4,
IIf(Month([Scheduled Start])=7,[Net Billed Amount]+[Extra Billed Amount],0) AS Expr5,
IIf(Month([Scheduled Start])=8,[Net Billed Amount]+[Extra Billed Amount],0) AS Expr6,
IIf(Month([Scheduled Start])=9,[Net Billed Amount]+[Extra Billed Amount],0) AS Expr7,
IIf(Month([Scheduled Start])=10,[Net Billed Amount]+[Extra Billed Amount],0) AS Expr8,
IIf(Month([Scheduled Start])=11,[Net Billed Amount]+[Extra Billed Amount],0) AS Expr9,
IIf(Month([Scheduled Start])=12,[Net Billed Amount]+[Extra Billed Amount],0) AS Expr10,
IIf(Month([Scheduled Start])=1,[Net Billed Amount]+[Extra Billed Amount],0) AS Expr11,
IIf(Month([Scheduled Start])=2,[Net Billed Amount]+[Extra Billed Amount],0) AS Expr12,
IIf(Month([Scheduled Start])=3,[Net Billed Amount]+[Extra Billed Amount],0) AS Expr13,
IIf(Month([Scheduled Start])=4,[Net Billed Amount]+[Extra Billed Amount],0) AS Expr14, [Work Order].ContractorAssignedID
FROM Econtact INNER JOIN [Work Order] ON Econtact.[Contact Number] = [Work Order].[Contact Number]
WHERE ((([Net Billed Amount]+[Extra Billed Amount])>0) AND (([Work Order].[Scheduled Start]) Between [Forms]![Lists]![Beginning Date] And [Forms]![Lists]![Ending Date]))


second query:

TRANSFORM Sum([Qry - 3 Month Projection].Expr3) AS SumOfExpr3
SELECT [Qry - 3 Month Projection].Salesperson, [Qry - 3 Month Projection].[Work Type], Sum([Qry - 3 Month Projection].Expr3) AS [Total Of Expr3]
FROM [Qry - 3 Month Projection]
GROUP BY [Qry - 3 Month Projection].Salesperson, [Qry - 3 Month Projection].[Work Type]
PIVOT Format([Scheduled Start],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

 

[Keith Nichols]

I'm very green in this (and other) areas but I can offer this which may be relevant. Sorry in advance if it isn't.

In Pivot Tables, which I believe are functionally the same as cross tabs, only with more flexibility, you can right click row and column headings and select to show empty rows/columns. This toggle either shows all results or only those that contain a value. Something similar may work in cross tab queries.

Incidentally, showing or hiding the empty colums/rows does not form part of the query, it is just the formatting of the pivot table.

HTH