Crosstab Query question!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following information in my Table

Borrower 1
DocName Years DateCompleted
W2 2004 12/05/05
W2 2003 12/06/05
W2 2002 12/06/05
1090 2004 12/05/05
1090 2003 12/05/05
1090 2002 12/05/05

Borrower 2
DocName Years DateCompleted
W2 2003 12/12/05
W2 2002 12/13/05
W2 2001 12/13/05
1090 2003 12/13/05
1090 2002 12/13/05
1090 2001 12/13/05

What I would like to do is to have the W2 or 1090 counted as 1 for each
borrower then my crosstab query would look like this
Week No. W2 1090 1040
50 1 1 0
51 1 1 0

How will I do this please help!
 
Use a totals query ahead of the crosstab query. Table named LoanDocs.

LoanDocsSum --
SELECT LoanDocs.Borrower, LoanDocs.DocName, Format([DateCompleted],"ww") AS
[Week of Year]
FROM LoanDocs
GROUP BY LoanDocs.Borrower, LoanDocs.DocName, Format([DateCompleted],"ww");


TRANSFORM Count(LoanDocsSum.Borrower) AS CountOfBorrower
SELECT LoanDocsSum.[Week of Year]
FROM LoanDocsSum
GROUP BY LoanDocsSum.[Week of Year]
PIVOT LoanDocsSum.DocName;
 
Thanks alot that worked....

KARL DEWEY said:
Use a totals query ahead of the crosstab query. Table named LoanDocs.

LoanDocsSum --
SELECT LoanDocs.Borrower, LoanDocs.DocName, Format([DateCompleted],"ww") AS
[Week of Year]
FROM LoanDocs
GROUP BY LoanDocs.Borrower, LoanDocs.DocName, Format([DateCompleted],"ww");


TRANSFORM Count(LoanDocsSum.Borrower) AS CountOfBorrower
SELECT LoanDocsSum.[Week of Year]
FROM LoanDocsSum
GROUP BY LoanDocsSum.[Week of Year]
PIVOT LoanDocsSum.DocName;


JOM said:
I have the following information in my Table

Borrower 1
DocName Years DateCompleted
W2 2004 12/05/05
W2 2003 12/06/05
W2 2002 12/06/05
1090 2004 12/05/05
1090 2003 12/05/05
1090 2002 12/05/05

Borrower 2
DocName Years DateCompleted
W2 2003 12/12/05
W2 2002 12/13/05
W2 2001 12/13/05
1090 2003 12/13/05
1090 2002 12/13/05
1090 2001 12/13/05

What I would like to do is to have the W2 or 1090 counted as 1 for each
borrower then my crosstab query would look like this
Week No. W2 1090 1040
50 1 1 0
51 1 1 0

How will I do this please help!
Click to expand...
Click to expand...
 
Back
Top