show counts in a report of multiple years

[lisalisa]

I have 2 tables: Source and Deal. I want to show for each state for each
source the count of deals for 2006 and 2007. My query is correct but I can't
get the data to print on the report in this format:
2006 2007
state
source name 5 2
source name 1 2
totals by state 6 4

Query:
SELECT tbl_Source.[Source State], tbl_Source.[Source Name],
Year(tbl_Deal.[Date Received]) AS [Year], Count(tbl_Source.[Source Name]) AS
[CountOfSource Name]
FROM tbl_Source RIGHT JOIN tbl_Deal ON tbl_Source.[Source Id] =
tbl_Deal.[Source Id]
GROUP BY tbl_Source.[Source State], tbl_Source.[Source Name],
Year(tbl_Deal.[Date Received])
HAVING (((tbl_Source.[Source State]) Is Not Null) AND
((Year([tbl_Deal].[Date Received])) Like 2006 Or (Year([tbl_Deal].[Date
Received])) Like 2007));

I can print the report if the years are in the detail section, but I want
only 2 counts: 2006 and 2007 in the detailed section.

 

[KARL DEWEY]

I recommend a crosstab query. Use Running Sum in footer for the totals by
state.

 

[KARL DEWEY]

TRANSFORM Count(tbl_Source.[Source Name]) AS [CountOfSource Name]
SELECT tbl_Source.[Source State], tbl_Source.[Source Name]
FROM tbl_Deal LEFT JOIN tbl_Source ON tbl_Deal.[Source Id] =
tbl_Source.[Source Id]
GROUP BY tbl_Source.[Source State], tbl_Source.[Source Name]
PIVOT Year([Date Received]);

--
KARL DEWEY
Build a little - Test a little

I recommend a crosstab query. Use Running Sum in footer for the totals by
state.

--
KARL DEWEY
Build a little - Test a little

I have 2 tables: Source and Deal. I want to show for each state for each
source the count of deals for 2006 and 2007. My query is correct but I can't
get the data to print on the report in this format:
2006 2007
state
source name 5 2
source name 1 2
totals by state 6 4

Query:
SELECT tbl_Source.[Source State], tbl_Source.[Source Name],
Year(tbl_Deal.[Date Received]) AS [Year], Count(tbl_Source.[Source Name]) AS
[CountOfSource Name]
FROM tbl_Source RIGHT JOIN tbl_Deal ON tbl_Source.[Source Id] =
tbl_Deal.[Source Id]
GROUP BY tbl_Source.[Source State], tbl_Source.[Source Name],
Year(tbl_Deal.[Date Received])
HAVING (((tbl_Source.[Source State]) Is Not Null) AND
((Year([tbl_Deal].[Date Received])) Like 2006 Or (Year([tbl_Deal].[Date
Received])) Like 2007));

I can print the report if the years are in the detail section, but I want
only 2 counts: 2006 and 2007 in the detailed section.

 

[lisalisa]

Thanks so much it worked perferct!!!!!!!!!!!!

TRANSFORM Count(tbl_Source.[Source Name]) AS [CountOfSource Name]
SELECT tbl_Source.[Source State], tbl_Source.[Source Name]
FROM tbl_Deal LEFT JOIN tbl_Source ON tbl_Deal.[Source Id] =
tbl_Source.[Source Id]
GROUP BY tbl_Source.[Source State], tbl_Source.[Source Name]
PIVOT Year([Date Received]);

--
KARL DEWEY
Build a little - Test a little

I recommend a crosstab query. Use Running Sum in footer for the totals by
state.

--
KARL DEWEY
Build a little - Test a little

I have 2 tables: Source and Deal. I want to show for each state for each
source the count of deals for 2006 and 2007. My query is correct but I can't
get the data to print on the report in this format:
2006 2007
state
source name 5 2
source name 1 2
totals by state 6 4

Query:
SELECT tbl_Source.[Source State], tbl_Source.[Source Name],
Year(tbl_Deal.[Date Received]) AS [Year], Count(tbl_Source.[Source Name]) AS
[CountOfSource Name]
FROM tbl_Source RIGHT JOIN tbl_Deal ON tbl_Source.[Source Id] =
tbl_Deal.[Source Id]
GROUP BY tbl_Source.[Source State], tbl_Source.[Source Name],
Year(tbl_Deal.[Date Received])
HAVING (((tbl_Source.[Source State]) Is Not Null) AND
((Year([tbl_Deal].[Date Received])) Like 2006 Or (Year([tbl_Deal].[Date
Received])) Like 2007));

I can print the report if the years are in the detail section, but I want
only 2 counts: 2006 and 2007 in the detailed section.