Crosstab Query question!

J

JOM

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!
 
K

KARL DEWEY

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;
 
J

JOM

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I get the DMax(Date) 1
Calculating interest due on late loan payments 1
2 dimensional date sort 2
Fill information on Form 1
Calculations on my report! 0
Returning a Date with Multiple Criteria Lookup Values 0
How can I specify two criterias in a column based in another colum 2
Help with If Sum statement - i think.... 4

Top