Set joined criteria in Cross Tab Query

[thesproing]

hi guys!

Im sure this is really easy to solve, but im kinda new to Access and its really bugging me!!

Basically, i have a cross tab Query where the column Heading is a date, ie October, November etc.

However, i only want the table to show three dates, corresponding with three fields in another table ie the other table will show October, november and December, and the Cross Tab will only show those columns.

this is the SQL as it stands:


TRANSFORM Sum(TABLE A.[Total Revenue £]) AS [SumOfTotal Revenue £]

SELECT TABLE A.[Company ID], TABLE A.[Company Name], TABLE A.[Agency Flag]

FROM [MONTH] LEFT JOIN TABLE A ON [DISCOUNT PERIOD].Market = TABLE A.Market

WHERE (((TABLE A.Market)="UK"))

GROUP BY TABLE A.Market, TABLE A.[Company ID], TABLE A.[Company Name], TABLE A.[Agency Flag]

PIVOT TABLE.[Mth Yr];

Where [Mth Yr] is what has to match the months specified in the "MONTH Table".

Any ideas?

Thanks!

Oliver

 

[xenou]

I'm not really up to snuff on cross tab SQL (i usually do them in design view), but I would guess the answer to your question is that you should inner join rather than left join. That would include only matches in the result set.