creating a report with all weeknumbers

[dmi]

I have the following report I want to create:

I want to display a list of prospects, sales and cancellations on a report.

I have the following database schema:

tblprospects:

proid
prospect (value 1= registered, value 0 = not registered.)
prospectdate

tblsale:

proid
sale (value 1= sold, value 0 = not sold.)
saledate

tblcancel:
proid
cancel (value 1= cancelled, value 0 = not cancelled.)
canceldate

I want to be able to create a report displayed like below:

Year Week Prospect Sale Cancel

2005 5 0 0 1
2005 4 0 3 0
2005 3 2 3 0
2005 2 3 4 1

And so on...

It should only count the prospect, sale and cancel field for every week and year. But the problem I have is that the dates in the different date fields are not always on the same week.

I thought of creating a new table where I put all weeknumbers and years inside, and run a check for the three (prospect, sale and cancel) fields how many there are. But I was hoping I could save me that trouble, because then I have to add this for every year.

Does anyone of you have any ideas on how to attack this problem?

[devinemke]

how bout something like:

SELECT YEAR(prospectdate) AS year, WEEK(prospectdate) AS week, COUNT(*) FROM tblprospects WHERE prospect = 1 GROUP BY year, week ORDER BY year, week

repeat for the other two tables.

[dmi]

Thanks for your reply.
The query is not correct, because I want to know them all:

Year Week prospect sale trial

2005 3 1 1 0
2005 2 2 0 1
2005 1 1 0 0

Values in the query you gave, would only output the three first columns. The problem is that I do not know how to connect them all:

The below data:

Year Week prospect

2005 3 1
2005 2 2
2005 1 1

Year Week sale

2005 3 1

Year Week cancel

2005 2 1

Should become:

Year Week prospect sale trial

2005 3 1 1 0
2005 2 2 0 1
2005 1 1 0 0